Table of Contents
Do you need to convert between Julian dates and calendar dates? Working with dates can be tricky in Excel, but with this tutorial, you can learn how to quickly and easily convert between these two formats. In this article, we’ll show you step-by-step how to use Excel to convert between Julian dates and calendar dates. So, let’s get started!
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.