Insert Julian dates

Microsoft Office Excel 2003

previous page next page

Use the TEXT, TODAY, and DATEVALUE functions to do this task.

Worksheet example

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

ShowHow?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. 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 you want.

Function details

DATEVALUE

TEXT

TODAY

ShowConvert a date to a "Julian date"

Use the TEXT and DATEVALUE functions to do this task.

Worksheet example

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

Show How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. 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 you want.

Function details

DATEVALUE

TEXT

ShowConvert a date to a Julian date used in astronomy

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

Worksheet example

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

ShowHow?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

    Selecting an example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the worksheet, select cell A1, and press CTRL+V.
  5. 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.

previous page start next page