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.

The following tutorials explain how to perform other common tasks in Excel: