How to make a monthly calendar with a formula in Excel
Excel formula
=LET(s,B1-WEEKDAY(B1)+1,d,SEQUENCE(6,7,s),IF(MONTH(d)=MONTH(B1),d,""))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.
- Result on the example
- a 6-row list that spills from A3 in
A3 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in A3. Change any input and the result updates, or edit the formula to experiment.
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.
=LET(s,B1-WEEKDAY(B1)+1,d,SEQUENCE(6,7,s),IF(MONTH(d)=MONTH(B1),d,""))Result{6 rows × 7 cols} "", "", "", "", …
s46292A name defined in LET, or a named range.
B1Oct 2026The value in B1.
WEEKDAY(B1)5WEEKDAY: Day of the week as a number; Sunday = 1 by default.- serial_number
B1Oct 2026
- serial_number
B1-WEEKDAY(B1)46291The - operator subtracts.
B1-WEEKDAY(B1)+146292The + operator adds.
d{6 rows × 7 cols} 46292, 46293, 46294, 46295, …A name defined in LET, or a named range.
SEQUENCE(6,7,s){6 rows × 7 cols} 46292, 46293, 46294, 46295, …SEQUENCE: A grid of sequential numbers. Spills.- rows
6 - columns
7 - start
s46292
- rows
MONTH(d){6 rows × 7 cols} 9, 9, 9, 9, …MONTH: Month of a date, 1–12.- serial_number
d{6 rows × 7 cols} 46292, 46293, 46294, 46295, …
- serial_number
MONTH(B1)10MONTH: Month of a date, 1–12.- serial_number
B1Oct 2026
- serial_number
MONTH(d)=MONTH(B1){6 rows × 7 cols} FALSE, FALSE, FALSE, FALSE, …The = operator tests whether the two sides are equal.
IF(MONTH(d)=MONTH(B1),d,""){6 rows × 7 cols} "", "", "", "", …IF: Returns one value if a test is true, another if false.- logical_test
MONTH(d)=MONTH(B1){6 rows × 7 cols} FALSE, FALSE, FALSE, FALSE, … - value_if_true
d{6 rows × 7 cols} 46292, 46293, 46294, 46295, … - value_if_false
""
- logical_test
LET(s,B1-WEEKDAY(B1)+1,d,SEQUENCE(6,7,s),IF(MONTH(d)=MONTH(B1),d,"")){6 rows × 7 cols} "", "", "", "", …LET: Names values inside a formula.- name1
s46292 - value1
B1-WEEKDAY(B1)+146292 - name2, value2
d{6 rows × 7 cols} 46292, 46293, 46294, 46295, … - calculation
SEQUENCE(6,7,s){6 rows × 7 cols} 46292, 46293, 46294, 46295, … - name2
IF(MONTH(d)=MONTH(B1),d,""){6 rows × 7 cols} "", "", "", "", …
- name1
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(IF(MONTH(SEQUENCE(6,7,B1-WEEKDAY(B1)+1))=MONTH(B1),SEQUENCE(6,7,B1-WEEKDAY(B1)+1),"")). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SEQUENCE(6,7,B1-WEEKDAY(B1)+1) | spills 6 × 7Different result | 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,"")) | spills 6 × 7 | Works when B1 holds any date in the month: EOMONTH(B1,-1)+1 finds the 1st first. |
Common mistakes
B1must be the 1st of the month. If it might be any date, useB1-DAY(B1)+1in 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)+1and relabel the headers.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Make a monthly calendar with a formula
A3, build a six-week calendar for the month that starts on the date in B1, Sunday first, with the days of other months left blank.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.