| Microsoft Year 2000 Readiness Disclosure
and Resource Center |
|
|
|
| Microsoft Access: How to Convert Between
Julian Days and Dates |
|
Summary
This
article contains steps to create sample procedures for converting
between a Julian day and a valid Microsoft Access date. You can
change the Julian day format used in the examples into a format
tailored to your specific needs. Examples of conversion methods
follow each sample procedure.
This
article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using
the programming tools provided with Microsoft Access. For more
information about Visual Basic for Applications, please refer to
your version of the "Building Applications with Microsoft Access"
manual.
More Information
There
are many definitions of Julian days and Julian dates. The formal
definition of a Julian Date is not used in this article because of
the complex historical adoption of the Gregorian (modern) calendar.
Instead, an informal Julian day format is used and is defined as the
ordinal day of a year.
The
informal Julian day is commonly used by government agencies and
contractors. The informal Julian day format used in this article is
the ordinal day of a year (for example, Julian day 032 represents
February 1st, or the 32nd day of the year). Variations on informal
Julian day formats include using a preceding two-digit year (for
example 96032 for 2/1/96) and separating the year with a dash (for
example 96-032). Another, less popular, Julian day format uses a one
digit year (for example 6-032). These additional formats do not
uniquely identify the century or decade. You should carefully
consider the consequences when using these formats; for example, the
Julian day 00061 can be interpreted as 3/1/2000 or 3/2/1900.
The
first sample function, CJulian2Date() , converts a
Julian day to a date:
1. Create a module and type the following line in
the Declarations section if it is not already there:
2. Type the following procedure:
'
*************************************** '
'
' FUNCTION:
CJulian2Date() '
'
' PURPOSE: Convert a Julian day to a
date. The function '
' works with dates based on the
Gregorian (modern) '
' calendar. '
'
' ARGUMENTS:
JulDay: The ordinal day of a year. Between 1 '
' and 365 for
all years, or between 1 and 366 for leap '
' years. YYYY: A
three or four digit integer for a year '
' that is within the
range of valid Microsoft Access dates. '
' If YYYY is
omitted, then YYYY is assumed to be the year '
' of the
current system date. '
'
' RETURNS: A date for a valid
Microsoft Access year and '
' Julian day, ' or a Null value
for an invalid Julian Day. '
'
*************************************** '
Function
CJulian2Date (JulDay As Integer, Optional YYYY) If
IsMissing(YYYY) Then YYYY = Year(Date) If Not IsNumeric(YYYY) Or
YYYY \ 1 <> YYYY Or YYYY < 100 Or YYYY _ > 9999 Then
Exit Function If JulDay > 0 And JulDay < 366 Or JulDay =
366 And _ YYYY Mod 4 = 0 And YYYY Mod 100 <> 0 Or YYYY Mod
400 = 0 Then _ CJulian2Date = DateSerial(YYYY, 1, JulDay) End
Function
3. To test this function, type the following line
in the Debug window and then press ENTER:
Note that your system's
date format for 2/1/96 is returned. If your system's date is in
1996 then CJulian2Date(32) will also return 2/1/96.
4. To convert a Julian date format like 96032 to a
Microsoft Access date, type the following line in the Debug
window, and then press ENTER:
?CJulian2Date(96032 Mod 1000, 1900 + 96032 \ 1000)
Note that your system's date format for 2/1/96 is returned.
Replace 1900 with 2000 if you are using this Julian date format
for dates between 1/1/2000 and 12/31/2999.
5. To convert a Julian date format like 96-032 to a
Microsoft Access date, type the following line in the Debug
window, and then press ENTER:
?CJulian2Date(Right("96-032", 3), 1900 + Left("96-032",
2)) Note that your system's
date format for 2/1/96 is returned. Replace 1900 with 2000 if you
are using this Julian date format for dates between 1/1/2000 and
12/31/2999. The second sample function,
CDate2Julian() , converts a date to a
Julian day:
1. Create a module and type the following line in
the Declarations section if it is not already there:
2. Type the following procedure:
' *************************************** '
' FUNCTION:
CDate2Julian() '
'
' PURPOSE: Convert a date to a Julian
day. The function '
' works with dates based on the Gregorian
(modern) '
' calendar. '
'
' ARGUMENTS: ' MyDate: A
valid Microsoft Access date. '
'
' RETURNS: A three digit
Julian day as a string. '
'
*************************************** '
Function
CDate2Julian(MyDate As Date) As String CDate2Julian =
Format(MyDate - DateSerial(Year(MyDate) - 1, 12, _ 31), "000")
End Function
3. To test this function, type the following line
in the Debug window, and then press ENTER:
?CDate2Julian(#3/1/1996#) Note that Julian day 061
is returned.
4. To convert a valid Microsoft Access date to a
Julian date in a format like 96061 type the following line in the
Debug window, and then press ENTER:
?Year(#3/1/1996#) Mod 100 & CDate2Julian(#3/1/1996#)
Note that 96061 is returned.
5. To convert a valid Microsoft Access date to a
Julian date in a format like 96-061 type the following line in the
Debug window, and then press ENTER:
?Year(#3/1/1996#) Mod 100 & "-" &
CDate2Julian(#3/1/1996#) Note that 96-061 is
returned. Last reviewed: August 29,
1997
Article ID: Q162745 The information in this
article applies to:
Microsoft Access versions
7.0, 97
For information about
converting Julian days to dates in Microsoft Access 1.x and 2.0,
please see the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q92816
TITLE : ACC:
Converting Julian Dates with Access Basic Code
|
|
|
