Converting Julian Dates to ##/##/## Format

[Kevin]

Hey all:

Need help with converting Julian Dates to Regular Date Format. I did see a
thread with similar question but I am pretty sure my data is in the real
Julian format. For example, I have the year and ordinal day for the year
(1-365 or 1-366). There are two colums right now with each but I can convert
to 90001 for January 1, 1990 before running something to get the regular
date. My dates range from Jan 1, 1970 to Dec 31, 2004. Any help?

 

[John Vinson]

Hey all:

Need help with converting Julian Dates to Regular Date Format. I did see a
thread with similar question but I am pretty sure my data is in the real
Julian format.

WHICH "real Julian format"? There are many: Astronomical Julian Date
(midnight today was 2453548.5) and Modified Astronomical Julian Date
(midnight today was 53548.5) (see
http://en.wikipedia.org/wiki/Julian_day); yyyyddd (today is 2005178);
Military Julian Date (today is 5178), and others.

For example, I have the year and ordinal day for the year
(1-365 or 1-366). There are two colums right now with each but I can convert
to 90001 for January 1, 1990 before running something to get the regular
date. My dates range from Jan 1, 1970 to Dec 31, 2004. Any help?

So you're intentionally building in a Y2K problem using only a two
digit year. That'll be fun in 2070, but I guess we'll both be out of
the picture by then... <g>

If you have a two-digit JYear field and an integer JDay field, try

Datefield: DateSerial([JYear]+Iif([JYear] > 70,1900,2000), 1, [JDay])


John W. Vinson[MVP]

 

[Kevin]

Thanks for the quick response. I do have a four year field as well as the two
final digits of the year. I will try your solution and let you know how it
works out.

Thanks for the clarification on "real"

Hey all:

Need help with converting Julian Dates to Regular Date Format. I did see a
thread with similar question but I am pretty sure my data is in the real
Julian format.

WHICH "real Julian format"? There are many: Astronomical Julian Date
(midnight today was 2453548.5) and Modified Astronomical Julian Date
(midnight today was 53548.5) (see
http://en.wikipedia.org/wiki/Julian_day); yyyyddd (today is 2005178);
Military Julian Date (today is 5178), and others.

For example, I have the year and ordinal day for the year
(1-365 or 1-366). There are two colums right now with each but I can convert
to 90001 for January 1, 1990 before running something to get the regular
date. My dates range from Jan 1, 1970 to Dec 31, 2004. Any help?

So you're intentionally building in a Y2K problem using only a two
digit year. That'll be fun in 2070, but I guess we'll both be out of
the picture by then... <g>

If you have a two-digit JYear field and an integer JDay field, try

Datefield: DateSerial([JYear]+Iif([JYear] > 70,1900,2000), 1, [JDay])


John W. Vinson[MVP]

 

[Kevin]

Worked like a charm, Thanks a lot

Hey all:

Need help with converting Julian Dates to Regular Date Format. I did see a
thread with similar question but I am pretty sure my data is in the real
Julian format.

WHICH "real Julian format"? There are many: Astronomical Julian Date
(midnight today was 2453548.5) and Modified Astronomical Julian Date
(midnight today was 53548.5) (see
http://en.wikipedia.org/wiki/Julian_day); yyyyddd (today is 2005178);
Military Julian Date (today is 5178), and others.

For example, I have the year and ordinal day for the year
(1-365 or 1-366). There are two colums right now with each but I can convert
to 90001 for January 1, 1990 before running something to get the regular
date. My dates range from Jan 1, 1970 to Dec 31, 2004. Any help?

So you're intentionally building in a Y2K problem using only a two
digit year. That'll be fun in 2070, but I guess we'll both be out of
the picture by then... <g>

If you have a two-digit JYear field and an integer JDay field, try

Datefield: DateSerial([JYear]+Iif([JYear] > 70,1900,2000), 1, [JDay])


John W. Vinson[MVP]