Tip Printed from ExcelTip.com

---

Converting a Julian Date to a Calendar Date


Problem:

Listed in column A are Julian dates in "YYJJJ" format.
"YY" represents a year between 1920 and 2020, and "JJJ" represents the serial number of the day within the year.
We want to convert each date in column A to its corresponding calendar date.

Solution:

Use the LEFT, IF, and MOD functions as shown in the following formula and format the results as dates:
=("1/1/"&(IF(LEFT(A2,2)*1<20,2000,1900)+LEFT(A2,2)))+MOD(A2,1000)-1