How to Convert a Julian Date in Excel

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
An Excel formula can convert Julian dates to typical calendar format.
Image Credit: AndreyPopov/iStock/Getty Images

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.

Advertisement

Step 1

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.

Video of the Day

Step 2

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.

Advertisement

Advertisement

Step 3

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.

Video of the Day

Advertisement

Advertisement

references