A Julian date is a date that is represented by a single number (e.g. 22164) while a calendar date is a date that is represented in months, days and years (e.g. 6/13/2022).
You can use the following formulas in Excel to convert between Julian dates and calendar dates:
Formula 1: Convert Julian Date to Calendar Date
=DATE(IF(0+(LEFT(A2))<30,2000,1900)+LEFT(A2,2),1,RIGHT(A2,3))
Formula 2: Convert Calendar Date to Julian Date
=TEXT(A2,"yy")&TEXT((A2-DATEVALUE("1/1/"&TEXT(A2,"yy"))+1),"000")
Each formula assumes that the date you’d like to convert is located in cell A2.
The following examples show how to use each formula in practice.
Example 1: Convert Julian Date to Calendar Date
We can type the following formula into cell B2 to convert the Julian date in cell A2 into a calendar date:
=DATE(IF(0+(LEFT(A2))<30,2000,1900)+LEFT(A2,2),1,RIGHT(A2,3))
We can then click and drag this formula down to each remaining cell in column B:
Column B displays the calendar date that corresponds to each Julian date in column A.
For example
- The Julian date of 22164 is equivalent to a calendar date of 6/13/2022.
- The Julian date of 85124 is equivalent to a calendar date of 5/4/1985.
- The Julian date of 85194 is equivalent to a calendar date of 7/13/1985.
And so on.
Example 2: Convert Calendar Date to Julian Date
We can type the following formula into cell B2 to convert the calendar date in cell A2 into a Julian date:
=TEXT(A2,"yy")&TEXT((A2-DATEVALUE("1/1/"&TEXT(A2,"yy"))+1),"000")
We can then click and drag this formula down to each remaining cell in column B:
Column B displays the Julian date that corresponds to each calendar date in column A.
For example
- The calendar date of 6/13/2022 is equivalent to a Julian date of 22164.
- The calendar date of 5/4/1985 is equivalent to a Julian date of 85124.
- The calendar date of 7/13/1985 is equivalent to a Julian date of 85194.
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Calculate the Number of Months Between Dates in Excel
How to Convert Date to Month and Year Format in Excel
How to Calculate Average by Month in Excel
