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

Julian Dates

Latitude And Longitude 

Overtime Hours And Timesheets

Time Zones In VBA

VBA Procedures For Dates And Times

Week Numbers

Worksheet Functions For Dates And Times

See the Dates And Times Topic Index For Information