Date & Time
Generate & Filter Gregorian Leap Years Accurately Across Century Boundaries
Build a leap-year list that avoids the naive +4 century trap on 1900 and 2100 using exact 4/100/400 modular rules, MONTH(DATE(y,2,29))=2, and FILTER.
Why SEQUENCE(..., 4) fails across century years 1900 and 2100
Stepping by +4 years with SEQUENCE(10, 1, 2092, 4) produces 2092, 2096, 2100, 2104—wrongly labeling 2100 as a leap year! Under the Gregorian calendar, century years divisible by 100 are NOT leap years unless also divisible by 400 (so 2000 and 2400 are leap years, while 1900 and 2100 have 365 days).
Note also Excel's historic Lotus 1-2-3 compatibility quirk on year 1900 itself: testing OR(MOD(A2, 400) = 0, AND(MOD(A2, 4) = 0, MOD(A2, 100) <> 0)) implements the astronomical Gregorian rule cleanly even for 1900, and FILTER extracts only verified leap years.
| Candidate Year | Divisible by 4 | Divisible by 100 | Divisible by 400 | True Gregorian Leap Year? |
|---|---|---|---|---|
| 1900 | Yes | Yes | No | FALSE (365 days) |
| 2000 | Yes | Yes | Yes | TRUE (366 days) |
| 2024 | Yes | No | No | TRUE (366 days) |
| 2026 | No | No | No | FALSE (365 days) |
| 2100 | Yes | Yes | No | FALSE (365 days) |
Exact Gregorian 4 / 100 / 400 Leap-Year Test
Evaluates TRUE strictly for true Gregorian leap years—correctly rejecting both 1900 and 2100 while accepting 2000 and 2028.
=OR(MOD(A2, 400) = 0, AND(MOD(A2, 4) = 0, MOD(A2, 100) <> 0))Extract Verified Leap-Year List with FILTER
Filters candidate years A2:A7 by the boolean helper column B2:B7 so century non-leap years are excluded automatically.
=FILTER(A2:A7, B2:B7 = TRUE)Try the example
Use the original values or change them to test your understanding.