How to Convert a Julian Date in Excel
Julian dates use a five-digit code; the first two digits represent the year, and the last three digits refer to the day. Excel 2013 doesn't directly recognize this format, so you'll need to convert Julian dates to normal calendar dates to perform calculations. Use a formula to extract the pertinent data from the Julian date and then reformat it to the expected date format.
Highlight the cell in which you'll enter the Julian date, click the "Number Format" drop-down menu on the Home tab's Number group and then select "Text," so Excel recognizes the data entered as text.
Enter the complete five-digit Julian date, including any leading zeros such as "00001" for the date Jan. 1, 2000. Because you formatted the cell as text, Excel keeps any leading zeros; if you used the General or Number format, Excel removes leading zeros, so the data is no longer in Julian format.
Enter the formula "=Date(If(0+(Left(A1,2))<30,2000,1900)+Left(A1,2),1,Right(A1,3))" without quotes in an empty cell. Change the "A1" reference to reflect the cell containing the Julian date.
Tips & Warnings
- Some databases use a seven-digit variation of the original Julian format in which the first four digits represent the year. To convert a seven-digit Julian date in cell A1, use the formula "=Date(Left(A1,4),1,Right(A1,3))."
- If you have a columnar list of Julian dates, copy the formula down the formula column to automatically convert each date.
- To remove the original Julian dates, you must first strip the formula from the converted values. Highlight the cells that contain the converted dates, press "Ctrl-C," click the "Paste" drop-down menu in the Home tab's Clipboard group and then select the "Paste Values" option to paste the values over the formulas. You can then delete the Julian dates without affecting the converted values.
- Because Excel internally increments dates starting at Jan 1, 1900 and does not allow negative date numbers, it will not correctly convert Julian dates earlier than this starting value without using extensive Microsoft Visual Basic code.