Access Calendar to Julian Conversions

[Dave]

I have a database with dates in the MM/DD/YYYY format and
I need to convert them to julian dates. Is there a built
in function in Access that will do this?

 

[Douglas J. Steele]

Depends what you mean by "Julian Date". If you'll looking to represent the
date as the day number in the year, (yyyy.ddd, so that today is 2004.177),
try:

Format(MyDate, "yyyy") & "." & Format(Format(MyDate, "y"), "000")

 

[Dave Steele]

No, this is a straight number - not sure what the
reference day and year is but it looks like it might be 1
Jan 1950 or so (the number I see are like 105191 for a
date in Nov or Dec of last year).

David L. Steele

 

[Dave Steele]

I stand corrected. The Julian date I have to convert to
is composed of six digets ... the first is the century
(21st so 1) the second is the year and the last three the
day of the year. So 103191 would be the 191st day of
2003.

Dave

 

[Douglas J. Steele]

How many centuries do you need to be concerned with? If only 20th and 21st,
the following will work:

=IIf(Year(MyDate) > 2000, 1, 0) & Right$(Format(MyDate, "yyyy"), 2) &
Format(Format(MyDate, "y"), "000")

If more, take a look at using the Switch function to determine it.

 

[Dave Steele]

Thank you - that should work.

Dave

-----Original Message-----
How many centuries do you need to be concerned with? If only 20th and 21st,
the following will work:

=IIf(Year(MyDate) > 2000, 1, 0) & Right$(Format (MyDate, "yyyy"), 2) &
Format(Format(MyDate, "y"), "000")

If more, take a look at using the Switch function to determine it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)






.

 

[Steven Parsons [MSFT]]

Hi Dave -

This is Steven from Microsoft Access Technical Support replying to your
newsgroup post.

The following document will show you how to convert from Access dates to
Julian dates, or from Julian dates to Access dates:

ACC2000: How to Convert Julian Days to Dates in Access and Back
http://support.microsoft.com/default.aspx?scid=KB;EN-US;209922

Please let me know if this solves your problem or if you would like further
assistance. I look forward to hearing from you.

Sincerely,
Steven Parsons [MSFT]
Microsoft Access Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! (http://www.microsoft.com/security)