The phrase "Julian date" is used to refer to a date format that is a combination of the current year, and the number of days since the beginning of the year. For example, January 1, 2003 is represented as 2003001 and December 31, 2003 is represented as 2003365. The "Julian date" format is described in this topic. Note that this format is not based on the Julian calendar, nor is it a true Julian date.

Notes

• The DATEVALUE function converts a text date to a serial number.
• The TEXT function converts to text in a specified number format.
• The TODAY function provides a serial number for today's date.
• In the formulas below, the current date used is taken from the computer's system clock and the year begins on January 1 (1/1). To convert the formulas to use a different starting date, edit the portion "1/1/" to the date you want.

Enter today as a Julian date

To insert the current day in Julian format, with a two-digit year, enter the following formula:

=TEXT(TODAY(),"yy")&TEXT((TODAY()-DATEVALUE("1/1/"&TEXT(TODAY(),"yy"))+1),"000")

To insert the current day in Julian format, with a four-digit year, enter the following formula:

=TEXT(TODAY(),"yyyy")&TEXT((TODAY()-DATEVALUE("1/1/"&TEXT(TODAY(),"yy"))+1),"000")

Convert a date to a Julian date

To convert the date in cell A1 to Julian format, with a two-digit year, enter the following formula:

=TEXT(A1,"yy")&TEXT((A1-DATEVALUE("1/1/"&TEXT(A1,"yy"))+1),"000")

To convert the date in cell A1 to Julian format, with a four-digit year, enter the following formula:

=TEXT(A1,"yyyy")&TEXT((A1-DATEVALUE("1/1/"&TEXT(A1,"yy"))+1),"000")

Convert a date to a Julian date, as used in astronomy

Note  This formula only works for dates after 3/1/1901, in workbooks using the 1900 date system.

To convert the date in cell A1 to the Julian date format used in astronomy, enter the following formula:

=A1+2415018.50

Select the cell and click Cells on the Format menu. Click the Number tab, and then click Number in the Category box.

More information

For more information about the worksheet functions in this formula, type DATEVALUE, TODAY, or TEXT function in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.