Dynamic calendar in Excel: one date, a SEQUENCE grid and WEEKDAY

Why a calendar is just 42 consecutive dates

Every month fits in six rows of seven days. A 31-day month that starts on a Saturday or a Sunday needs all six, as August 2026 does: the 1st is in row 1 and the 31st, a Monday, opens row 6. Shorter or better-placed months leave the last row in the next month. So instead of placing each day by hand, you only need the first cell of the grid. Every other cell is that date plus a number of days: one to the right adds 1, one row down adds 7.

Step 1 and 2: the 1st of the month and the Monday before it

=DATE(YEAR(B1),MONTH(B1),1) turns any date into the 1st of its month. =EOMONTH(B1,-1)+1 does the same: the last day of the previous month plus one.

Then step back to Monday. WEEKDAY(date,2) numbers the days Monday = 1 to Sunday = 7, so subtracting it and adding 1 always lands on the Monday of the same week: =B2-WEEKDAY(B2,2)+1. For Saturday 1 August, WEEKDAY returns 6, and 6 − 1 = 5 days back is Monday 27 July. If the 1st is itself a Monday the formula returns the 1st, which is what you want.

Step 3: fill the grid with SEQUENCE

In Excel 365 and Excel 2021, one formula fills all 42 cells: =SEQUENCE(6,7,B3) spills 6 rows and 7 columns counting up by 1 from the Monday in B3. Put Mon to Sun headers above it and give the grid the custom number format d (Ctrl+1, Custom) so each cell shows only the day number while still holding a real date.

In older versions, put one formula in the top-left cell and copy it over the grid: =$B$3+(ROW()-6)*7+COLUMN()-1, where 6 is the row of the first week. The worked example uses this copy-down form, so every cell has its own formula you can inspect.

Worked example: August 2026 from one date

B1 is the only input. Column B holds real dates as serial numbers, column C shows them as text. Rows 6 to 11 are the calendar: each cell adds the right number of days to B3 and hides days outside August.

Worked example: August 2026 from one date

	A	B	C	D	E	F	G
1	Any date	14 Aug 2026		Title	August 2026
2	1st of month	46,235	Sat 1 Aug	Days	31
3	Grid start (Mon)	46,230	Mon 27 Jul	Weeks	6
4
5	Mon	Tue	Wed	Thu	Fri	Sat	Sun
6						1	2
7	3	4	5	6	7	8	9
8	10	11	12	13	14	15	16
9	17	18	19	20	21	22	23
10	24	25	26	27	28	29	30
11	31
12
13	Last cell of the grid, B3 + 41				Sun 6 Sep
14	Sunday-first start, B2 − WEEKDAY(B2) + 1				Sun 26 Jul
15	Working days in the month (no holidays)				21
16	ISO week of the 1st				31

In Excel 365, =SEQUENCE(6,7,B3) in A6 with the number format d replaces the 42 copied formulas. B2 and B3 show serial numbers here (46,235 is 1 Aug 2026), which is how Excel stores every date.

Hide the days of the previous and next month

A printed calendar usually leaves the neighbouring months blank. Test the month of each date against the month of the 1st: =LET(d,SEQUENCE(6,7,B3),IF(MONTH(d)=MONTH(B2),d,"")) in Excel 365, or the IF version in the grid below. The cell for 27 July then shows nothing, and the 31 August cell still shows 31. A softer alternative is a conditional formatting rule =MONTH(A6)<>MONTH($B$2) that greys those days out instead of hiding them. The conditional formatting formula examples show how the relative and absolute references in such rules work, and the same idea colours weekends with =WEEKDAY(A6,2)>5.

Titles, working days and week numbers

A month title above the grid is =TEXT(B2,"mmmm yyyy"), here August 2026. =DAY(EOMONTH(B1,0)) gives the days in the month (31). =NETWORKDAYS(B2,EOMONTH(B2,0)) counts the working days: 21 in August 2026, before any holiday list, which you add as a third argument as shown in the NETWORKDAYS and WORKDAY guide. =ISOWEEKNUM(B2) returns the ISO week of the 1st, week 31, and the same function in a column next to the grid labels each row with its week number.

A week that starts on Sunday

For a US-style calendar with Sunday first, use plain WEEKDAY(B2), which numbers Sunday = 1, and put Sun to Sat headers above the grid: =B2-WEEKDAY(B2)+1 returns Sun 26 Jul for August 2026. Mixing the two is the classic bug: Sunday-first maths under Monday-first headers shifts every date one column to the left of its weekday.

Common mistakes

• Starting the grid on the 1st. If A6 is the 1st itself, the dates no longer match the Mon to Sun headers. Start at the Monday in B3.
• Using WEEKDAY without the second argument. Plain WEEKDAY counts Sunday as 1, so =B2-WEEKDAY(B2)+1 lands on a Sunday. Use WEEKDAY(B2,2) for a Monday-first calendar.
• Only five rows. Months like August 2026 need six weeks. With five rows the 31st disappears.
• Typing day numbers instead of dates. A grid of plain 1, 2, 3 cannot be coloured by weekday, matched against a holiday list or redrawn for the next month. Keep real dates and change only the number format.
• Forgetting the $ signs in the copy-down formula. Without $B$3 every copied cell refers to a different start date and the grid falls apart.
• Comparing months without the year. To blank other months, MONTH is enough inside one grid, but a planner that spans December and January should compare EOMONTH(d,0)=EOMONTH(B2,0).