Julian Dates
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 dayofyear number. For example, 24August1999 is stored as 99236, since 24August 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(A1DATE(YEAR(A1),1,0),"000") This formula takes the 2 rightmost 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 dayofyear 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 100Jan1999 to be 10April1999. 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))
 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 400Jan1999 to be 4Feb2000. For example, if A1 contains 99001 (1Jan1999), and A2 contains 400, the formula will return 5Feb2000. 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)+ For example, if A1 contains 99001 (1Jan1999), 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 fivecharacter Julian date and return the standard Excel date. Function JDateToDate(JDate As String) As Long The DateToJDate function will accept a standard Excel date and return a fivecharacter string representing the Julian date. Function DateToJDate(TheDate As Long) As String 



Other Date
And Time Related Procedures are described on the following pages. 

Holidays 

