Dates & Time
Reject Invalid Dates & Impossible Leap Days (Round-Trip DATE Year/Month/Day Audit)
Catch impossible imported date strings like "2026-02-29" or "2026-13-05" that DATE(y, m, d) would otherwise roll silently into the next month.
Why DATE(2026, 2, 29) silently rolls forward instead of raising an error
Spreadsheet DATE(year, month, day) arithmetic is intentionally permissive: passing month = 13 rolls into January of year + 1, and passing day = 29 in non-leap February 2026 silently produces March 1, 2026. When validating CSV imports or manual ISO "YYYY-MM-DD" strings, simply checking ISNUMBER(DATE(yr, mo, dy)) lets impossible calendar dates corrupt aging buckets.
Reconstructing dt = DATE(yr, mo, dy) and asserting YEAR(dt) = yr, MONTH(dt) = mo, and DAY(dt) = dy forms a strict round-trip identity that passes "2024-02-29" (leap year) while rejecting "2026-02-29" (non-leap rollover).
| Raw Input String | DATE(yr, mo, dy) Serial | Round-Trip Y/M/D Match? | Verdict |
|---|---|---|---|
| "2026-10-31" | 2026-10-31 | TRUE | Valid calendar date |
| "2024-02-29" | 2024-02-29 | TRUE | Valid leap-year date |
| "2026-02-29" | Rolls to 2026-03-01 | FALSE (Month 3 <> 2) | Rejected non-leap Feb 29 |
| "2026-13-05" | Rolls to 2027-01-05 | FALSE (Year 2027 <> 2026) | Rejected month > 12 |
Dual Serial-Bound & ISO Text Round-Trip Validation Formula
Validate parsed numeric date serials against your valid business-date window [2020-01-01, 2035-12-31], and validate unparsed text strings by asserting that DATE(yr, mo, dy) preserves all three year, month, and day components unchanged.
=IF(ISNUMBER(A2), AND(A2 >= DATE(2020, 1, 1), A2 <= DATE(2035, 12, 31)), LET(raw_s, TRIM(A2), yr, VALUE(LEFT(raw_s, 4)), mo, VALUE(MID(raw_s, 6, 2)), dy, VALUE(RIGHT(raw_s, 2)), dt, DATE(yr, mo, dy), IFERROR(AND(LEN(raw_s) = 10, MID(raw_s, 5, 1) = "-", MID(raw_s, 8, 1) = "-", YEAR(dt) = yr, MONTH(dt) = mo, DAY(dt) = dy, dt >= DATE(2020, 1, 1), dt <= DATE(2035, 12, 31)), FALSE)))Count Rejected Date Entries Across an Import Batch
Count FALSE flags in the validation column before promoting imported rows into reporting schedules.
=COUNTIF(B2:B6, FALSE)Try the example
Use the original values or change them to test your understanding.