Insert Julian dates

A Julian date is sometimes 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, 2007 is represented as 2007001 and December 31, 2007 is represented as 2007365. Note that this format is not based on the Julian calendar.

There is also a Julian date commonly used in astronomy, which is a serial date system starting on January 1, 4713 B.C.E.

There are several ways to insert Julian dates.

What do you want to do?

Enter today as a Julian date

Convert a date to a Julian date

Convert a date to a Julian date used in astronomy


Enter today as a Julian date

To do this task, use the TEXT, TODAY, and DATEVALUE functions.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
3
A B
Formula Description (Result)
=TEXT(TODAY() ,"yy")&TEXT((TODAY() -DATEVALUE("1/1/"&TEXT(TODAY(),"yy"))+1),"000") Current day in Julian format, with a two-digit year (Varies)
=TEXT(TODAY() ,"yyyy")&TEXT((TODAY() -DATEVALUE("1/1/"&TEXT(TODAY(),"yy"))+1),"000") Current day in Julian format, with a four-digit year (Varies)

 Notes 

  • The current date used is taken from the computer's system clock.
  • In the formulas above, 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 that you want.

Function details

DATEVALUE

TEXT

TODAY

Top of Page Top of Page


Convert a date to a Julian date

To do this task, use the TEXT and DATEVALUE functions.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
A
Date
6/23/2007
Formula Description (Result)
=TEXT(A2,"yy")&TEXT((A2-DATEVALUE("1/1/"&TEXT(A2,"yy"))+1),"000") Date above in Julian format, with a two-digit year (07174)
=TEXT(A2,"yyyy")&TEXT((A2-DATEVALUE("1/1/"&TEXT(A2,"yy"))+1),"000") Date above in Julian format, with a four-digit year (2007174)

 Note    In the formula above, 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 that you want.

Function details

DATEVALUE

TEXT

Top of Page Top of Page


Convert a date to a Julian date used in astronomy

This formula only works for dates after 3/1/1901 and in workbooks that use the 1900 date system.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
 
1
2
A
Date
6/23/2007
Formula Description (Result)
=A2+2415018.50 First date above in Julian date format used in astronomy (2454274.50)

 Note    To view the date as a number, select the cell and click Cells on the Format menu. Click the Number tab, and then click Number in the Category box.

Top of Page Top of Page

 
 
Applies to:
Excel 2003