# How to make a monthly calendar with a formula in Excel

Start the grid on the Sunday before the 1st and blank out other months: `=LET(s,B1-WEEKDAY(B1)+1,d,SEQUENCE(6,7,s),IF(MONTH(d)=MONTH(B1),d,""))`, formatted as d.

**Excel:** `=LET(s,B1-WEEKDAY(B1)+1,d,SEQUENCE(6,7,s),IF(MONTH(d)=MONTH(B1),d,""))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(IF(MONTH(SEQUENCE(6,7,B1-WEEKDAY(B1)+1))=MONTH(B1),SEQUENCE(6,7,B1-WEEKDAY(B1)+1),""))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Month | 2026-10-01 |  |  |  |  |  |
| Sun | Mon | Tue | Wed | Thu | Fri | Sat |

Formula in A3: `=LET(s,B1-WEEKDAY(B1)+1,d,SEQUENCE(6,7,s),IF(MONTH(d)=MONTH(B1),d,""))`

Result (spills 6 × 7): , , , , 1, 2, 3; 4, 5, 6, 7, 8, 9, 10; 11, 12, 13, 14, 15, 16, 17; 18, 19, 20, 21, 22, 23, 24; 25, 26, 27, 28, 29, 30, 31; , , , , , , 

## How it works

`WEEKDAY(B1)` is 5 for a Thursday, so `B1-WEEKDAY(B1)+1` steps back to the Sunday before the 1st. SEQUENCE fills a 6 by 7 grid of consecutive dates from there, which covers any month. IF keeps the dates in the same month as `B1` and blanks the rest, and the d format shows only the day number.

LET names the start date and the grid so each is calculated once. The Google Sheets version spells them out and wraps the formula in ARRAYFORMULA.

## Other ways

- `=SEQUENCE(6,7,B1-WEEKDAY(B1)+1)`: Without the IF, the days of the previous and next month fill the gaps, like a wall calendar. (Shows Sep 27 to 30 and Nov 1 to 7 as well.)
- `=LET(first,EOMONTH(B1,-1)+1,d,SEQUENCE(6,7,first-WEEKDAY(first)+1),IF(MONTH(d)=MONTH(first),d,""))`: Works when B1 holds any date in the month: `EOMONTH(B1,-1)+1` finds the 1st first.

## Common mistakes

- `B1` must be the 1st of the month. If it might be any date, use `B1-DAY(B1)+1` in place of B1.
- Without a d (or dd) number format the grid shows serial numbers. A full date format shows 10/1/2026 in every cell.
- For a Monday-first calendar, change the start to `B1-WEEKDAY(B1,2)+1` and relabel the headers.

## Questions

**How do I switch months?**

Change `B1`, or point it at a dropdown of first-of-month dates. Next month is `=EDATE(B1,1)`.

**How do I highlight today?**

Add conditional formatting to A3:G8 with the formula `=A3=TODAY()`.

**How do I make it show the current month?**

Put `=EOMONTH(TODAY(),-1)+1` in B1, the 1st of this month.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/make-a-monthly-calendar-with-a-formula