Date & Time
Dynamic monthly calendar grid formula (SEQUENCE + WEEKDAY)
Spill a complete 6-week x 7-day monthly calendar grid for any Year and Month (Sunday-start or Monday-start) with one SEQUENCE formula.
Anchor on the Sunday (or Monday) on or before the 1st of the month
Any calendar month fits inside a 6-row by 7-column (42-day) grid. First build the 1st of the month with f = DATE(Year, Month, 1).
For a Sunday-to-Saturday calendar, WEEKDAY(f, 1) returns 1..7, so f - WEEKDAY(f, 1) + 1 steps back to the exact Sunday that opens Row 1! For an Monday-to-Sunday calendar, use f - WEEKDAY(f, 2) + 1. Passing that start serial into SEQUENCE(6, 7, StartDate, 1) spills all 42 dates automatically.
| Calendar Style (May 2026, May 1 = Fri) | Start Formula | Top-Left Cell (Row 1, Col 1) |
|---|---|---|
| Sunday–Saturday (US) | f - WEEKDAY(f, 1) + 1 | 2026-04-26 (Sun, Apr 26) |
| Monday–Sunday (Monday-start) | f - WEEKDAY(f, 2) + 1 | 2026-04-27 (Mon, Apr 27) |
6x7 Sunday-start and Monday-start calendar formulas
Format the 6x7 spilled grid with custom date format "d" (and dim cells where MONTH(cell) <> SelectedMonth via conditional formatting).
=LET(f, DATE(A2, B2, 1), SEQUENCE(6, 7, f - WEEKDAY(f, 1) + 1, 1))Full Monday-start calendar grid
Switches WEEKDAY return_type from 1 (Sunday=1) to 2 (Monday=1).
=LET(f, DATE(A2, B2, 1), SEQUENCE(6, 7, f - WEEKDAY(f, 2) + 1, 1))Inputs and interpretation
Use integer years in the supported date range and months 1 through 12. Enter the formula in an empty 6-by-7 area outside the input cells. Results are numeric date serials; apply a date format to display dates. A Monday-start calendar does not itself calculate ISO week numbers.
Try the example
Use the original values or change them to test your understanding.