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

Join the two-digit year and the three-digit day of the year: `=TEXT(A2,"yy")&TEXT(A2-DATE(YEAR(A2),1,0),"000")`.

**Excel:** `=TEXT(A2,"yy")&TEXT(A2-DATE(YEAR(A2),1,0),"000")` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Date | Julian (YYDDD) |
| 2026-01-01 |  |
| 2026-03-05 |  |
| 2026-12-31 |  |
| 2028-12-31 |  |
| 2027-07-04 |  |

Formula in B2, filled to B6: `=TEXT(A2,"yy")&TEXT(A2-DATE(YEAR(A2),1,0),"000")`

Result: 26001; 26064; 26365; 28366; 27185

## How it works

`TEXT(A2,"yy")` gives the two-digit year, and A2 minus December 31 of the previous year gives the day of the year. `TEXT(…,"000")` pads that to three digits, so March 5, 2026 becomes 26064.

## Other ways

- `=RIGHT(YEAR(A2),2)&TEXT(A2-DATE(YEAR(A2),1,0),"000")`: The same, taking the last two digits of the year.
- `=YEAR(A2)&TEXT(A2-DATE(YEAR(A2),1,0),"000")`: Seven-digit YYYYDDD, which avoids any doubt about the century. (Four-digit year.)

## Common mistakes

- The result is text. Converting it to a number drops the leading zero for 2000 to 2009 (05001 becomes 5001).
- Julian date here means the year-and-day format used on food labels and in manufacturing, not the astronomical Julian Day Number.

## Questions

**How do I convert it back to a date?**

`=DATE(2000+INT(B2/1000),1,MOD(B2,1000))`.

**What about the JD Edwards CYYDDD format?**

It adds a century digit: `=(YEAR(A2)-1900)*1000+A2-DATE(YEAR(A2),1,0)` gives 126064 for March 5, 2026.

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-date-to-a-julian-date