Julian Date Conversion

[K]

To convert a Julian date to a Normal Date in Access. Cut and paste into your
query. Change [Your Field Name] to the Julina date field name you have.

NormalDate: CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([Your Field
Name]/1000))))+[Your Field Name]-Int([Your Field
Name]/1000)*1000))-1,"mm-dd-yy"))

If you find this usefull please rate it. Thanks

 

[Douglas J. Steele]

Sorry, but that's not really a particular good way to do it.

For one thing, you're formatting the date as mm-dd-yy (why not 4 digits,
btw?), and then using CDate. The CDate function respects regional settings.
Someone who uses dd-mm-yyyy as their Short Date format will find that should
the Format function return 06-03-07 for 03 June, 2007, the CDate function
will translate that to 06 March, 2007.

All you need is DateSerial(YearPortion, 0, DayPortion)

I'm not sure why you're using 1900+Int([Your Field Name]/1000) to calculate
the year portion: how will you get 2007 from that, for instance?

The day portion is [Your Field Name] Mod 1000

 

[K]

Okay so if the [Your Field name] = IE_DATE how would you write the
expression. I am doing this cacluation in a quey?

Sorry, but that's not really a particular good way to do it.

For one thing, you're formatting the date as mm-dd-yy (why not 4 digits,
btw?), and then using CDate. The CDate function respects regional settings.
Someone who uses dd-mm-yyyy as their Short Date format will find that should
the Format function return 06-03-07 for 03 June, 2007, the CDate function
will translate that to 06 March, 2007.

All you need is DateSerial(YearPortion, 0, DayPortion)

I'm not sure why you're using 1900+Int([Your Field Name]/1000) to calculate
the year portion: how will you get 2007 from that, for instance?

The day portion is [Your Field Name] Mod 1000

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

To convert a Julian date to a Normal Date in Access. Cut and paste into
your
query. Change [Your Field Name] to the Julina date field name you have.

NormalDate: CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([Your
Field
Name]/1000))))+[Your Field Name]-Int([Your Field
Name]/1000)*1000))-1,"mm-dd-yy"))

If you find this usefull please rate it. Thanks

 

[Douglas J. Steele]

What's contained in IE_DATE: A 2 digit year or a 4 digit year? If a 2 digit
year, how do you know that 40032 is 1 Feb, 1940 or 1 Feb, 2040?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Okay so if the [Your Field name] = IE_DATE how would you write the
expression. I am doing this cacluation in a quey?

Sorry, but that's not really a particular good way to do it.

For one thing, you're formatting the date as mm-dd-yy (why not 4 digits,
btw?), and then using CDate. The CDate function respects regional
settings.
Someone who uses dd-mm-yyyy as their Short Date format will find that
should
the Format function return 06-03-07 for 03 June, 2007, the CDate function
will translate that to 06 March, 2007.

All you need is DateSerial(YearPortion, 0, DayPortion)

I'm not sure why you're using 1900+Int([Your Field Name]/1000) to
calculate
the year portion: how will you get 2007 from that, for instance?

The day portion is [Your Field Name] Mod 1000

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

To convert a Julian date to a Normal Date in Access. Cut and paste into
your
query. Change [Your Field Name] to the Julina date field name you have.

NormalDate: CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([Your
Field
Name]/1000))))+[Your Field Name]-Int([Your Field
Name]/1000)*1000))-1,"mm-dd-yy"))

If you find this usefull please rate it. Thanks

 

[K]

I should have stated that. I am using a 4 digit year for example 07125,year
then days.(May 5, 2007)

What's contained in IE_DATE: A 2 digit year or a 4 digit year? If a 2 digit
year, how do you know that 40032 is 1 Feb, 1940 or 1 Feb, 2040?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Okay so if the [Your Field name] = IE_DATE how would you write the
expression. I am doing this cacluation in a quey?

Sorry, but that's not really a particular good way to do it.

For one thing, you're formatting the date as mm-dd-yy (why not 4 digits,
btw?), and then using CDate. The CDate function respects regional
settings.
Someone who uses dd-mm-yyyy as their Short Date format will find that
should
the Format function return 06-03-07 for 03 June, 2007, the CDate function
will translate that to 06 March, 2007.

All you need is DateSerial(YearPortion, 0, DayPortion)

I'm not sure why you're using 1900+Int([Your Field Name]/1000) to
calculate
the year portion: how will you get 2007 from that, for instance?

The day portion is [Your Field Name] Mod 1000

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


To convert a Julian date to a Normal Date in Access. Cut and paste into
your
query. Change [Your Field Name] to the Julina date field name you have.

NormalDate: CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([Your
Field
Name]/1000))))+[Your Field Name]-Int([Your Field
Name]/1000)*1000))-1,"mm-dd-yy"))

If you find this usefull please rate it. Thanks

 

[K]

Also, It will never be a year less than the year 2000.

What's contained in IE_DATE: A 2 digit year or a 4 digit year? If a 2 digit
year, how do you know that 40032 is 1 Feb, 1940 or 1 Feb, 2040?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Okay so if the [Your Field name] = IE_DATE how would you write the
expression. I am doing this cacluation in a quey?

Sorry, but that's not really a particular good way to do it.

For one thing, you're formatting the date as mm-dd-yy (why not 4 digits,
btw?), and then using CDate. The CDate function respects regional
settings.
Someone who uses dd-mm-yyyy as their Short Date format will find that
should
the Format function return 06-03-07 for 03 June, 2007, the CDate function
will translate that to 06 March, 2007.

All you need is DateSerial(YearPortion, 0, DayPortion)

I'm not sure why you're using 1900+Int([Your Field Name]/1000) to
calculate
the year portion: how will you get 2007 from that, for instance?

The day portion is [Your Field Name] Mod 1000

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


To convert a Julian date to a Normal Date in Access. Cut and paste into
your
query. Change [Your Field Name] to the Julina date field name you have.

NormalDate: CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([Your
Field
Name]/1000))))+[Your Field Name]-Int([Your Field
Name]/1000)*1000))-1,"mm-dd-yy"))

If you find this usefull please rate it. Thanks

 

[Douglas J. Steele]

You have an odd way of counting! <g> (that looks like a 2 digit year to
me...)

Since your other post indicated your dates will always be greater than or
equal to 2000, use

DateSerial(2000 + ([IE_DATE]\1000), 0, [IE_DATE] Mod 1000)

Note that that's \ in the first calculation, not /.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I should have stated that. I am using a 4 digit year for example 07125,year
then days.(May 5, 2007)

What's contained in IE_DATE: A 2 digit year or a 4 digit year? If a 2
digit
year, how do you know that 40032 is 1 Feb, 1940 or 1 Feb, 2040?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Okay so if the [Your Field name] = IE_DATE how would you write the
expression. I am doing this cacluation in a quey?

:

Sorry, but that's not really a particular good way to do it.

For one thing, you're formatting the date as mm-dd-yy (why not 4
digits,
btw?), and then using CDate. The CDate function respects regional
settings.
Someone who uses dd-mm-yyyy as their Short Date format will find that
should
the Format function return 06-03-07 for 03 June, 2007, the CDate
function
will translate that to 06 March, 2007.

All you need is DateSerial(YearPortion, 0, DayPortion)

I'm not sure why you're using 1900+Int([Your Field Name]/1000) to
calculate
the year portion: how will you get 2007 from that, for instance?

The day portion is [Your Field Name] Mod 1000

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


To convert a Julian date to a Normal Date in Access. Cut and paste
into
your
query. Change [Your Field Name] to the Julina date field name you
have.

NormalDate:
CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([Your
Field
Name]/1000))))+[Your Field Name]-Int([Your Field
Name]/1000)*1000))-1,"mm-dd-yy"))

If you find this usefull please rate it. Thanks

 

[K]

Yes sorry about that. Its a Monday. You are correct a 2 didgit year date. I
tried your SQL in my query and it returns the wrong date. It is off by one
month and sometimes a day as well.??

Also, It will never be a year less than the year 2000.

What's contained in IE_DATE: A 2 digit year or a 4 digit year? If a 2 digit
year, how do you know that 40032 is 1 Feb, 1940 or 1 Feb, 2040?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Okay so if the [Your Field name] = IE_DATE how would you write the
expression. I am doing this cacluation in a quey?

:

Sorry, but that's not really a particular good way to do it.

For one thing, you're formatting the date as mm-dd-yy (why not 4 digits,
btw?), and then using CDate. The CDate function respects regional
settings.
Someone who uses dd-mm-yyyy as their Short Date format will find that
should
the Format function return 06-03-07 for 03 June, 2007, the CDate function
will translate that to 06 March, 2007.

All you need is DateSerial(YearPortion, 0, DayPortion)

I'm not sure why you're using 1900+Int([Your Field Name]/1000) to
calculate
the year portion: how will you get 2007 from that, for instance?

The day portion is [Your Field Name] Mod 1000

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


To convert a Julian date to a Normal Date in Access. Cut and paste into
your
query. Change [Your Field Name] to the Julina date field name you have.

NormalDate: CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([Your
Field
Name]/1000))))+[Your Field Name]-Int([Your Field
Name]/1000)*1000))-1,"mm-dd-yy"))

If you find this usefull please rate it. Thanks

 

[John W. Vinson]

I should have stated that. I am using a 4 digit year for example 07125,year
then days.(May 5, 2007)

07 is two digits, not four...

That said...

DateSerial(2000 + Left([IE_Date], 2), 1, Mid([IE_Date], 3))

will give you the date/time value. To convert a date/time back to this format,
use

Format([datefield], "yyy")

John W. Vinson [MVP]

 

[K]

It works. Thanks John !

Yes sorry about that. Its a Monday. You are correct a 2 didgit year date. I
tried your SQL in my query and it returns the wrong date. It is off by one
month and sometimes a day as well.??

Also, It will never be a year less than the year 2000.

What's contained in IE_DATE: A 2 digit year or a 4 digit year? If a 2 digit
year, how do you know that 40032 is 1 Feb, 1940 or 1 Feb, 2040?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Okay so if the [Your Field name] = IE_DATE how would you write the
expression. I am doing this cacluation in a quey?

:

Sorry, but that's not really a particular good way to do it.

For one thing, you're formatting the date as mm-dd-yy (why not 4 digits,
btw?), and then using CDate. The CDate function respects regional
settings.
Someone who uses dd-mm-yyyy as their Short Date format will find that
should
the Format function return 06-03-07 for 03 June, 2007, the CDate function
will translate that to 06 March, 2007.

All you need is DateSerial(YearPortion, 0, DayPortion)

I'm not sure why you're using 1900+Int([Your Field Name]/1000) to
calculate
the year portion: how will you get 2007 from that, for instance?

The day portion is [Your Field Name] Mod 1000

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


To convert a Julian date to a Normal Date in Access. Cut and paste into
your
query. Change [Your Field Name] to the Julina date field name you have.

NormalDate: CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([Your
Field
Name]/1000))))+[Your Field Name]-Int([Your Field
Name]/1000)*1000))-1,"mm-dd-yy"))

If you find this usefull please rate it. Thanks

 

[Douglas J. Steele]

Sorry: typo.

DateSerial(2000 + ([IE_DATE]\1000), 1, [IE_DATE] Mod 1000)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Yes sorry about that. Its a Monday. You are correct a 2 didgit year date.
I
tried your SQL in my query and it returns the wrong date. It is off by one
month and sometimes a day as well.??

Also, It will never be a year less than the year 2000.

What's contained in IE_DATE: A 2 digit year or a 4 digit year? If a 2
digit
year, how do you know that 40032 is 1 Feb, 1940 or 1 Feb, 2040?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Okay so if the [Your Field name] = IE_DATE how would you write the
expression. I am doing this cacluation in a quey?

:

Sorry, but that's not really a particular good way to do it.

For one thing, you're formatting the date as mm-dd-yy (why not 4
digits,
btw?), and then using CDate. The CDate function respects regional
settings.
Someone who uses dd-mm-yyyy as their Short Date format will find
that
should
the Format function return 06-03-07 for 03 June, 2007, the CDate
function
will translate that to 06 March, 2007.

All you need is DateSerial(YearPortion, 0, DayPortion)

I'm not sure why you're using 1900+Int([Your Field Name]/1000) to
calculate
the year portion: how will you get 2007 from that, for instance?

The day portion is [Your Field Name] Mod 1000

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


To convert a Julian date to a Normal Date in Access. Cut and paste
into
your
query. Change [Your Field Name] to the Julina date field name you
have.

NormalDate:
CDate(Format(((DateValue("01/01/"+LTrim(Str(1900+Int([Your
Field
Name]/1000))))+[Your Field Name]-Int([Your Field
Name]/1000)*1000))-1,"mm-dd-yy"))

If you find this usefull please rate it. Thanks

 

[Don Brovero]

Very useful and easy to use.

Thanks!

Don

 

[Dirk Goldgar]

(re-posting as my original reply hasn't appeared)

Very useful and easy to use.

Thanks!

If this is a question, you'd better explain a bit more. The term "Julian
date" means different things to different people. Are you looking for a
date-format-conversion function? There have been various functions posted
to convert to "Julian" format. Here's an expression that displays the
current date in yyyy.ddd format, if that helps:

=Year(Date()) & "." & Format(Format(Date(),"y"),"000")