Date & Time
Convert date to Julian day of year (1–366 and YYYYDDD) and back
Calculate the ordinal day of the year with Date - DATE(YEAR(Date), 1, 0), format 7-digit YYYYDDD codes, and parse them back.
How day 0 of January gives December 31 of the prior year
DATE(YEAR(A2), 1, 0) steps one day before January 1, returning December 31 of the previous year. Subtracting that anchor from A2 yields 1 for January 1, 74 for March 15 in a common year (31 + 28 + 15), and 366 for December 31 in a leap year.
Warehouse and manufacturing lot codes often combine the 4-digit year with a 3-digit zero-padded day of year (YYYYDDD), such as "2026074".
Here Julian means an ordinal day-of-year code, not the astronomical Julian Day Number. Use date-only inputs and validate that a decoded day lies between 1 and the year’s length; DATE otherwise rolls an invalid code into another year.
| CalDate (A) | Day of Year | 7-Digit YYYYDDD | Parsed Back |
|---|---|---|---|
| 2026-03-15 | 74 | 2026074 | 2026-03-15 |
| 2024-12-31 | 366 | 2024366 | 2024-12-31 |
Day of year and 7-digit Julian string
RIGHT("000" & (A2 - DATE(YEAR(A2), 1, 0)), 3) pads days 1–99 with leading zeros so every code stays 7 characters wide.
=A2 - DATE(YEAR(A2), 1, 0)Convert YYYYDDD Julian code back to a calendar date
DATE accepts day numbers greater than 31 and rolls across months automatically, so DATE(LEFT(B2, 4), 1, RIGHT(B2, 3)) reconstructs March 15, 2026 directly.
=TEXT(DATE(VALUE(LEFT(B2, 4)), 1, VALUE(RIGHT(B2, 3))), "yyyy-mm-dd")Try the example
Use the original values or change them to test your understanding.