Date & Time Formulas
Convert Year & Day-of-Year Number (1–366) to Calendar Date
Convert ordinal day-of-year numbers (e.g., Year = 2026, Day = 135 -> 2026-05-15) into ISO calendar dates via DATE(Year, 1, Day_Number) while catching invalid Day 366 in non-leap years.
Turn NOAA weather logs, satellite TLEs, and manufacturing lot codes into real dates!
Meteorological stations, NASA satellite ephemerides, and food-packaging Julian lot codes ("2026-135") store dates as a 4-digit Year paired with an ordinal Day of Year (1 = January 1 through 365 or 366 = December 31). Because DATE(year, month, day) automatically rolls excess days forward across exact month lengths (28/29-day February, 30-day April, 31-day May), passing month = 1 and day = Day_Number into DATE(B2, 1, B3) converts any ordinal day directly into its exact calendar serial date!
Watch out for one silent rollover trap: in non-leap year 2026, DATE(2026, 1, 366) silently overflows into 2027-01-01 instead of raising an error! Checking YEAR(DATE(B2, 1, B3)) = B2 enforces exact Gregorian leap-year rules automatically—accepting Day 366 in leap year 2024 (2024-12-31) while rejecting Day 366 in 2026!
| Year (B2) & Ordinal Day (B3) | YEAR(DATE(B2, 1, B3)) Check | ISO Calendar Date Result | Leap-Year Behavior |
|---|---|---|---|
| Year 2026, Day 135 | 2026 (= B2 Valid) | "2026-05-15" | Non-Leap Year (Feb = 28d) |
| Leap Year 2024, Day 366 | 2024 (= B2 Valid) | "2024-12-31" | Valid Leap Day 366 |
| Non-Leap Year 2026, Day 366 | 2027 (<> B2 Overflow!) | "Invalid Day of Year" | Catches Non-Leap Overflow |
Validated ISO Calendar Date From Year & Day Number (1–365/366)
Verifies B3 >= 1 and YEAR(DATE(B2, 1, B3)) = B2 before formatting as ISO "yyyy-mm-dd".
=IF(OR(B2 < 1900, B3 < 1, YEAR(DATE(B2, 1, B3)) <> B2), "Invalid Day of Year", TEXT(DATE(B2, 1, B3), "yyyy-mm-dd"))Extract Month Number (1–12) From Year & Ordinal Day Number
Returns the integer calendar month (e.g., 5 for May) for monthly aggregation.
=IF(OR(B2 < 1900, B3 < 1, YEAR(DATE(B2, 1, B3)) <> B2), "Invalid Day of Year", MONTH(DATE(B2, 1, B3)))Try the example
Use the original values or change them to test your understanding.