Date & Time
Get Calendar Date for the Nth Day of the Year (Leap-Aware DATE Overflow)
Convert an ordinal day-of-year integer (1–366) and 4-digit year into an exact calendar date—automatically adjusting for February 29 in leap years—using DATE(year, 1, n).
How DATE(year, 1, n) handles month lengths and leap years automatically
Passing month = 1 and day = N directly into DATE(Year, 1, N) instructs the spreadsheet calendar engine to start at January 1 and advance N - 1 days across exact month boundaries. Day 60 automatically resolves to March 1 in a 365-day year (2026) and February 29 in a leap year (2028).
To guard scheduling inputs so N = 366 cannot silently spill into January 1 of the next year during non-leap years, check N <= (DATE(Year, 12, 31) - DATE(Year, 1, 0)) before converting.
| Year | Ordinal Day (N) | Total Days in Year | DATE(Year, 1, N) ISO Output |
|---|---|---|---|
| 2026 (Common) | 60 | 365 | 2026-03-01 |
| 2028 (Leap) | 60 | 366 | 2028-02-29 |
| 2026 (Common) | 256 (Programmer's Day) | 365 | 2026-09-13 |
| 2028 (Leap) | 256 (Programmer's Day) | 366 | 2028-09-12 |
Convert Year & Nth Day into Calendar Date
Returns the exact date serial for day B2 of year A2; wrap in TEXT(..., "yyyy-mm-dd") when displaying an ISO string.
=DATE(A2, 1, B2)Year-Bound Guard Preventing Day-366 Overflow in Common Years
Verifies 1 <= N <= 365 (or 366 in leap years) so out-of-range ordinal days flag an explicit message instead of rolling into next January.
=IF(AND(B2 >= 1, B2 <= DATE(A2, 12, 31) - DATE(A2, 1, 0)), TEXT(DATE(A2, 1, B2), "yyyy-mm-dd"), "Invalid Day")Try the example
Use the original values or change them to test your understanding.