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