Dates, Time & Schedules
List All Dates in a Month Dynamically with SEQUENCE & EOMONTH
Generate a self-resizing vertical calendar column of every date in any 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 |
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.