How to create a calendar in Excel

The idea: find the Sunday before the 1st, then count 42 days

Every month fits in a grid of 6 rows by 7 days. The grid starts on the Sunday on or before the 1st of the month. DATE(B1,B2,1) is the 1st, here Sunday, November 1, 2026. WEEKDAY returns 1 for Sunday through 7 for Saturday, so subtracting WEEKDAY and adding 1 always lands on that Sunday.

SEQUENCE(6,7,start) then counts 42 days in a block, row by row: 6 rows, 7 columns, starting at that date and stepping by 1. The raw result is a grid of dates. They show as numbers like 46327 until you format them; a custom format of d (Ctrl+1 > Custom) shows just the day.

Why 6 rows when most months need 5? Some months spread over six weeks. August 2026 starts on a Saturday and has 31 days, so the 30th and 31st land in row 6. February 2026 starts on a Sunday and fills exactly 4 rows. A fixed 6x7 grid keeps your layout the same every month.

Show only this month's days

A plain grid also shows the last days of the previous month and the first days of the next one, which is confusing on a wall calendar. LET names the 1st of the month s and the grid d, so you can test each date: if MONTH(d) is the month in B2, show DAY(d), otherwise show nothing.

For November 2026 the grid fills 1 to 28 in the first four rows, 29 and 30 start row 5, and row 6 is blank. Change B2 to 8 and the same formula shows August, with the 1st on Saturday and the 30th and 31st in row 6.

The result is day numbers, not dates. That's perfect for printing. If you also want to mark events or shade weekends, keep the dates and hide the other months with conditional formatting instead (see the last section).

Start the week on Monday

In much of Europe and in many work calendars the week starts on Monday. WEEKDAY(s,2) numbers the days Monday 1 to Sunday 7, so the same subtraction finds the Monday on or before the 1st. For November 2026 that is Monday, October 26.

November 1 is a Sunday, so it now sits in the last column of row 1, and November 30 is alone at the start of row 6. Months that start on a Sunday are exactly the ones that need six rows in a Monday calendar.

Add the day names and a title

Put the day headers in the row above the grid with one formula. =TEXT(SEQUENCE(1,7,s-WEEKDAY(s)+1),"ddd") takes the first seven dates of the grid and formats them as short day names, so they always match the grid. For the Sunday version that gives Sun, Mon, Tue, Wed, Thu, Fri, Sat; with WEEKDAY(s,2) it gives Mon to Sun. Use "dddd" for full names.

For the title, =TEXT(DATE(B1,B2,1),"mmmm yyyy") shows November 2026. To flip through months, add a Next button the low-tech way: =DATE(B1,B2+1,1) rolls past December on its own, because DATE(2026,13,1) is January 2027.

Make B2 a drop-down (Data > Data Validation > Allow: Whole number, between 1 and 12) so nobody types 13 into it.

Mark events and shade weekends

Keep a small events list next to the calendar: dates in column D, what's happening in E. COUNTIF can count how many events fall on each grid date in one go, and the formula below adds a dot to busy days. The 3rd, 11th and 26th get a marker; the 11th has two events. The Dec 2 rent reminder doesn't show, because it's outside November.

To see what's on a day, =TEXTJOIN(", ",TRUE,FILTER(E2:E6,D2:D6=DATE(2026,11,11))) lists "Team offsite, Book club".

For colors, spill the plain date grid from the first section, format it as d, and select the grid and add conditional formatting rules written for its top-left cell. If the grid starts in G3: =MONTH(G3)<>$B$2 with gray text fades the other months, =WEEKDAY(G3,2)>5 shades weekends, =COUNTIF($D$2:$D$6,G3)>0 highlights event days and =G3=TODAY() marks today.