| |
Many applications (especially mainframe systems) store
dates in the Julian format, which is a 5 digit number, consisting of a 2 digit
year and a 3 digit day-of-year number. For example, 24-August-1999
is stored as 99236, since 24-August is the 236th day of the year.
Excel doesn't support Julian dates directly, but you can use them with only a
few fairly simple formulas.
The first section of this page describes the Excel
worksheet formulas for working with Julian dates. The second
section describes the VBA procedures.
Converting A Standard Date To A Julian Date
The formula below will convert a standard Excel date in A1
to a Julian Date.
=RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000")
This formula takes the 2 right-most characters of the YEAR
of the date in A1, and
then appends the number of days between the date in A1
and the 0th day of that year. The TEXT
function formats the day-of-year number as three digits, with leading zeros if
necessary.
Converting A Julian Date To A Standard Date
The formula below will convert a Julian date to a standard
Excel date.
=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))
If the year digits of the Julian date are less than 30
(i.e., 00 to 29), the date is assumed to be a 2000 century year. If the
year digits of the Julian date are greater than or equal to 30 (i.e., 30 to 99),
the date is assumed to be a 1900 century year. This formula works by
taking advantage of the fact that the DATE
function can handle days beyond the "normal" days in a month.
For example, DATE
correctly computes 100-Jan-1999 to be 10-April-1999.
These Julian dates must have the leading zero or
zeros for years between 2000 and 2009. For example the 123rd day of
2000 must be entered as 00123. Format the cell as TEXT before entering the
data, or enter an apostrophe before the Julian date -- e.g., '00123. This
will prevent Excel from treating the Julian date as a number and suppressing the
leading zeros.
Differences Between Two Julian Dates
Now that we know how to convert a Julian date to a
standard date, it is simple to compute the number of days between two Julian
dates. For example, suppose we have two Julian dates, in A1 and A2.
The formula below will subtract the two dates.
=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))
-
DATE(IF(0+(LEFT(A2,2))<30,2000,1900)+LEFT(A2,2),1,RIGHT(A2,3))
Be sure to format the cell containing this formula as
General.
Add A Number Of Days To A Julian Date
You can add some number of days to a Julian date.
Suppose we have a Julian date in A1,
and a number of days in A2.
The formula below will add the number of days in A2
to the Julian date in A1,
and return the date as a standard Excel date.
=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3)+A2)
This formula works just like the formula we used to
convert a Julian date to a standard date, but adds A2
to the days. This works because the DATE
function will correctly compute a date like 400-Jan-1999 to be
4-Feb-2000. For example, if A1
contains 99001 (1-Jan-1999), and A2
contains 400, the formula will return 5-Feb-2000.
If you want your result to be a Julian date, then use the
following formula, which is just our first formula to convert standard dates to
Julian dates, with the formula above inserted into it wherever we need the
date.
=RIGHT(YEAR(DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+
LEFT(A1,2),1,RIGHT(A1,3)+A2)),2)& TEXT(DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3)+A2)-
DATE(YEAR(DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+
LEFT(A1,2),1,RIGHT(A1,3)+A2)),1,0),"000")
For example, if A1
contains 99001 (1-Jan-1999), and A2
contains 400, the formula will return 00036, the 36th day of the year
2000.
VBA Procedures For Working With
Julian Dates
The functions below will convert a Julian date to a
standard Excel date, and convert a standard Excel date to a Julian
date.
The JDateToDate
function will accept a five-character Julian date and return the standard Excel
date.
Function JDateToDate(JDate As String) As Long
Dim TheYear As Integer
Dim TheDay As Integer
Dim TheDate As Long
TheYear = CInt(Left(JDate, 2))
If TheYear < 30 Then
TheYear = TheYear + 2000
Else
TheYear = TheYear + 1900
End If
TheDay = CInt(Right(JDate, 3))
TheDate = DateSerial(TheYear, 1, TheDay)
JDateToDate = TheDate
End Function
The DateToJDate
function will accept a standard Excel date and return a five-character string
representing the Julian date.
Function DateToJDate(TheDate As Long) As String
Dim TheYear As Integer
Dim TheDays As Integer
Dim JDate As String
TheYear = Year(TheDate)
TheDays = DateDiff("d", DateSerial(TheYear, 1, 0), TheDate)
JDate = Right(Format(TheYear, "0000"), 2) & Format(TheDays, "000")
DateToJDate = JDate
End Function
|
|