# How to convert a Julian date (YYDDD) to a regular date in Excel

Split the year and the day number with INT and MOD, then let DATE count the days: `=DATE(2000+INT(A2/1000),1,MOD(A2,1000))`.

**Excel:** `=DATE(2000+INT(A2/1000),1,MOD(A2,1000))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Julian (YYDDD) | Date |
| 26001 |  |
| 26064 |  |
| 25365 |  |
| 28060 |  |
| 27185 |  |

Formula in B2, filled to B6: `=DATE(2000+INT(A2/1000),1,MOD(A2,1000))`

Result: 1/1/2026; 3/5/2026; 12/31/2025; 2/29/2028; 7/4/2027

## How it works

`INT(A2/1000)` is the two-digit year and `MOD(A2,1000)` the day of the year. DATE accepts a day number larger than the month has and rolls it forward, so day 64 of January 2026 is March 5.

## Other ways

- `=DATE(2000+LEFT(A2,2),1,RIGHT(A2,3))`: The text version. It breaks for years 2000 to 2009, where 06001 is stored as 6001.

## Common mistakes

- Years before 2000 need 1900 instead. With a mix, use a cutoff: `DATE(IF(INT(A2/1000)>50,1900,2000)+INT(A2/1000),1,MOD(A2,1000))`.
- Julian dates stored as text with a leading zero work with LEFT and RIGHT but not with INT and MOD until converted: use `--A2`.

## Questions

**How do I convert a JD Edwards CYYDDD date?**

`=DATE(1900+INT(A2/1000),1,MOD(A2,1000))`: 126064 becomes March 5, 2026.

**How do I convert YYYYDDD?**

`=DATE(INT(A2/1000),1,MOD(A2,1000))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/convert-a-julian-date-to-a-date