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.

Businesswoman Marking In Calendar
An Excel formula can convert Julian dates to typical calendar format.
credit: AndreyPopov/iStock/Getty Images

Step

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.

Step

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.

Step

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.