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
I have a question…Your Formula works fine for converting a 5-digit Julian date to a calendar date. My question is how you modify to use a 4 digit one? 1st digit is the number of the year, and the next 3 digits are the day of the year – meaning that “3177” would be June 26th, 2023, and “5005” would be January 5th, 2025.
Where I work, we use 16-digit transaction codes and the digits 5-8 represent the Julian date. Extracting the Julian from the transaction number is excel is easy, I just need a formula to convert those 4 digits to a calendar date.
Thank you for the formula. However, I am looking to convert a 7 digit Julian Date – 2018166. Hope you have a formula for this request. TIA!!
Hi GePa…To convert a 7-digit Julian date like **2018166** (which represents the 166th day of the year 2018) to a calendar date in Excel, you can use a formula that extracts the year and day parts and then converts them to a standard date.
Here’s how:
1. **Extract the year**:
The first four digits (2018 in your example) represent the year. You can use the formula:
`=LEFT(A1,4)`
(assuming the Julian date is in cell `A1`).
2. **Extract the day of the year**:
The remaining digits (166 in your example) represent the day of the year. You can use the formula:
`=RIGHT(A1,3)`
3. **Combine into a calendar date**:
To combine these parts and convert them into a proper date, use this formula:
`=DATE(LEFT(A1,4),1,RIGHT(A1,3))`
This formula takes the year from the first four digits, sets January 1st as the starting point, and adds the day of the year.
For example:
– If `A1` contains `2018166`, the formula `=DATE(LEFT(A1,4),1,RIGHT(A1,3))` will return **June 15, 2018**, which is the 166th day of 2018.
Yes, the Example 1 works for years in the 1900s, but the 2000s are 100 over. Here’s the 2K compliant version.
B column has the Julian dates.
C column has this statement resulting in a short date … 2/5/2024
=IF((LEN(B2)>5),(DATE((LEFT(B2,3) + 1900),1,RIGHT(B2,3))),(DATE((LEFT(B2,2) + 1900),1,RIGHT(B2,3))))
Hi Mike…Your formula for converting Julian dates to calendar dates in Excel does work for distinguishing between 3-digit and 5-digit Julian dates in the 1900s. However, it needs a slight adjustment for **21st-century dates** (2000s) to ensure it handles them correctly. Here’s a **2K-compliant version** that works seamlessly:
—
### Corrected Formula for Julian Dates in the 1900s and 2000s:
If `B2` contains the Julian date, you can use the following formula in column `C`:
“`excel
=IF(LEN(B2)>5,
DATE(LEFT(B2,4), 1, RIGHT(B2,3)),
DATE(IF(LEFT(B2,2)+1900<2000, LEFT(B2,2)+2000, LEFT(B2,2)+1900), 1, RIGHT(B2,3)))
```
---
### Explanation:
1. **`LEN(B2)`**: Determines whether the Julian date is in the 5-digit format (YYYYDDD) or shorter format (YYDDD).
- 5 digits: Use `LEFT(B2,4)` for the year (e.g., `2024` in `2024020`).
- Shorter format (e.g., `024020`):
- `LEFT(B2,2)` extracts the 2-digit year.
- Adds either `1900` or `2000` depending on whether it's for the 20th or 21st century.
2. **`DATE(year, 1, RIGHT(B2,3))`**: Constructs the calendar date by taking the year, the first month (January), and the day of the year (`DDD` from Julian format).
---
### Example:
| Julian Date (B) | Calendar Date (C) |
|------------------|-------------------|
| 2024020 | 1/20/2024 |
| 99001 | 1/1/1999 |
| 02036 | 2/5/2002 |
---
### Why This Works:
- It correctly distinguishes between years in the **1900s** and **2000s** based on the Julian date's format and length.
- It avoids the issue where shorter Julian dates (e.g., `99001` for 1999) would incorrectly map to a year in the 2000s due to the formula logic.
Let me know if you'd like further clarification or help with specific Julian date scenarios!