Dates, Time & Schedules
List All Dates in a Month Dynamically with SEQUENCE & EOMONTH
Generate a self-resizing vertical calendar column of every date in a modern Year + Month (automatically adjusting for 28-, 29-, 30-, or 31-day months) using SEQUENCE(DAY(EOMONTH(DATE(Y, M, 1), 0)), 1, DATE(Y, M, 1), 1).
Stop deleting #VALUE! or spillover May-1 rows at the bottom of February & April
Dragging =A2 + 1 down 31 fixed rows on a monthly attendance or hotel occupancy template creates an annoying end-of-month bug: whenever a user switches the month dropdown to February (28 or 29 days) or April/June/September/November (30 days), the bottom 1 to 3 rows spill silently into the NEXT month (March 1–3 or May 1).
Combining DATE(Year, Month, 1), EOMONTH(..., 0), and SEQUENCE eliminates manual row adjustments forever. First, DATE(B2, B3, 1) builds the 1st of the target month; next, DAY(EOMONTH(DATE(B2, B3, 1), 0)) extracts the exact day count of that month (28 in Feb 2026, 29 in leap Feb 2024, 30 in April, 31 in May); finally, SEQUENCE(days, 1, DATE(B2, B3, 1), 1) spills the exact date serials from Day 1 through the last day of the month.
| Selected Year & Month | First Day Serial Formula | DAY(EOMONTH(..., 0)) Rows | Last Spilled Date |
|---|---|---|---|
| 2024, Month 2 (Leap Feb) | DATE(2024, 2, 1) | 29 days (Leap Year) | 2024-02-29 |
| 2026, Month 2 (Common Feb) | DATE(2026, 2, 1) | 28 days | 2026-02-28 |
| 2026, Month 4 (April) | DATE(2026, 4, 1) | 30 days | 2026-04-30 |
| 2026, Month 12 (December) | DATE(2026, 12, 1) | 31 days | 2026-12-31 |
Spill the full list of dates
Enter this formula in a clear cell and format the spill range as dates. Use a whole-number year in the spreadsheet date system and a whole-number month from 1 to 12. A blocked spill range prevents the array from expanding. This example targets 1901–9999. Excel deliberately preserves a historical 1900 leap-year error; 1900 is outside this example.
=SEQUENCE(DAY(EOMONTH(DATE(B2, B3, 1), 0)), 1, DATE(B2, B3, 1), 1)Exact Number of Calendar Days Spilled for Target Year & Month
Validates Year >= 1900 and 1 <= Month <= 12, then counts the dynamic SEQUENCE array (matching DAY(EOMONTH(...))).
=IF(OR(B2 < 1900, B3 < 1, B3 > 12), "Invalid Year/Month", COUNTA(SEQUENCE(DAY(EOMONTH(DATE(B2, B3, 1), 0)), 1, DATE(B2, B3, 1), 1)))Verify Final Date of the Spilled Monthly Sequence (ISO YYYY-MM-DD)
Indexes the last element of the dynamic SEQUENCE spill and formats it as YYYY-MM-DD.
=IF(OR(B2 < 1900, B3 < 1, B3 > 12), "Invalid Year/Month", TEXT(MAX(SEQUENCE(DAY(EOMONTH(DATE(B2, B3, 1), 0)), 1, DATE(B2, B3, 1), 1)), "yyyy-mm-dd"))Try the example
Use the original values or change them to test your understanding.