The simplest Excel formula for creating a calendar is =SEQUENCE(6,7) combined with date arithmetic, but the most practical approach uses =LET(d, DATE(year, month, 1), seq, SEQUENCE(6,7, d - WEEKDAY(d) + 1), seq) to generate a full month grid. Below, I’ll break down how this works, how to customize it, and common pitfalls.
Why Use a Formula Instead of a Template?
Excel ships with calendar templates, but they are static—changing the month means editing dozens of cells. A formula-driven calendar updates instantly when you change a year or month cell. It also lets you add conditional formatting for weekends, holidays, or deadlines without manual tweaks. For anyone who builds schedules, planners, or content calendars, a formula approach saves time and reduces errors.
The Core Formula: SEQUENCE and DATE
Assume you want a calendar for a specific month. Put the year in cell B1 and the month number (1–12) in B2. In cell B4 (the top-left of the grid), enter:
=LET(d, DATE(B1,B2,1), seq, SEQUENCE(6,7, d - WEEKDAY(d,2) + 1), seq)
This spills a 6-row by 7-column array. The WEEKDAY(d,2) returns 1 for Monday, 2 for Tuesday, etc., so the grid starts on Monday. If you prefer Sunday-first, use WEEKDAY(d,1) (1=Sunday). The SEQUENCE function generates 42 consecutive dates starting from the first Sunday (or Monday) before the 1st. To display only the day numbers, wrap it: =DAY(...)—but keep the date values for formatting.
If you don’t have LET (Excel 2021 or earlier), use the longer version: =SEQUENCE(6,7, DATE(B1,B2,1) - WEEKDAY(DATE(B1,B2,1),2) + 1).
Setting Up the Layout
To make it look like a calendar:
- Enter the formula in
B4. The grid spills toH9(6 rows × 7 columns). - Add weekday headers in row 3:
Mon,Tue, …Sun(orSunfirst if you choseWEEKDAY(d,1)). - Format the cells with a date format like
dto show just the day number. - Merge the title cell (e.g.,
B1) and use=DATE(B1,B2,1)formatted asmmmm yyyyto show “January 2025”.
Adding Weekends and Holidays with Conditional Formatting
To highlight weekends, select the grid (e.g., B4:H9), go to Home > Conditional Formatting > New Rule > Use a formula, and enter:
=WEEKDAY(B4,2)>5
Set a fill color. For holidays, create a named range (e.g., Holidays) with dates, then use:
=COUNTIF(Holidays, B4)>0
This works because the grid cells contain real dates, not text.
Common Problems and Fixes
- Grid shows dates from previous/next month: That’s normal. To hide them, use a custom number format like
;;;for cells whereMONTH(B4)<>$B$2—but the formula still returns values. Alternatively, use=IF(MONTH(B4)=$B$2, DAY(B4), "")in each cell (but that breaks the spill). For a clean grid, accept the overflow or use a more complex formula that returns blanks. - Spill error: If something is in the spill range, Excel shows
#SPILL!. Clear the cells below and to the right of the formula. - Wrong first day: Double-check your
WEEKDAYreturn type.2starts Monday,1starts Sunday. - Year/month changes don’t update: Ensure
B1andB2are numbers, not text. Use=VALUE(B1)if needed.
Comparing to Google Sheets
Google Sheets has SEQUENCE and DATE too, but the formula differs slightly: =ARRAYFORMULA(SEQUENCE(6,7, DATE(B1,B2,1) - WEEKDAY(DATE(B1,B2,1),2) + 1)). The LET function is not available in older Sheets versions. Also, conditional formatting rules are created via Format > Conditional formatting with a custom formula. Excel’s WEEKDAY and Sheets’ WEEKDAY both support the same return types.
Alternatives: Dynamic Array vs. Traditional Formulas
If you’re on Excel 2019 or earlier, you don’t have SEQUENCE. Use a traditional formula in each cell, e.g., in B4:
=IF(MONTH(DATE($B$1,$B$2,1) - WEEKDAY(DATE($B$1,$B$2,1),2) + COLUMN()-2 + (ROW()-4)*7) = $B$2, DAY(DATE($B$1,$B$2,1) - WEEKDAY(DATE($B$1,$B$2,1),2) + COLUMN()-2 + (ROW()-4)*7), "")
Drag right and down. This is more complex and error-prone, so upgrading to a dynamic array version is recommended if possible.
Frequently Asked Questions
How do I make the calendar start on Sunday instead of Monday?
Change WEEKDAY(d,2) to WEEKDAY(d,1) in the formula. With return type 1, Sunday is 1, so the sequence starts on the Sunday before the 1st. Also change your header row to Sun, Mon, … Sat.
Can I show the full month name in the title automatically?
Yes. In a merged cell, enter =DATE(B1,B2,1) and format it with a custom number format mmmm yyyy. It will display “January 2025” and update when you change B1 or B2.
Why does my formula show #SPILL!?
#SPILL! means the formula’s output range isn’t empty. Select the cell with the formula, press Ctrl+Z to undo if you just typed it, or clear the cells that block the spill. Check for merged cells or data in the spill area.
How do I highlight today’s date on the calendar?
Use conditional formatting with the formula =B4=TODAY(). Select the grid, create a new rule, and set a fill. This works because the grid contains real date values.
Is there a way to create a full year calendar with one formula?
Yes, but it’s more complex. You can use SEQUENCE(12,1) to list months, then for each month use the same grid formula with a dynamic reference. For example, in a 12×7 grid, use =LET(m, SEQUENCE(12), d, DATE(B1,m,1), seq, SEQUENCE(6,7, d - WEEKDAY(d,2) + 1), seq)—but this spills a 3D array, which Excel doesn’t support. Instead, create 12 separate grids or use a single formula with WRAPROWS (Excel 365) to stack months vertically.
Does this work in Excel for Mac?
Yes, the functions SEQUENCE, LET, and WEEKDAY work the same in Excel for Mac (Microsoft 365). Keyboard shortcuts differ (e.g., Cmd+Z for undo), but the formulas are identical.
Related
- X++ String Split: How to Use str2con and Other Methods
- Excel Before Close Event: How to Run Code on Save
- SUBSTRING in Snowflake: Syntax, Examples, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Databricks SQL Functions: Types, Examples, and UsageDatabricks SQL functions are built-in or user-defined operations you call from SQL to transform values, summarize rows, work…
- How to Multiply Different Cells in Excel With a FormulaTo multiply different cells in Excel, select an empty cell, type a formula that starts with an equals…
- Rank Low to High in Excel: Formulas, Sorting, and TipsYes. To assign ranks with the lowest value as rank 1, use Excel’s RANK.EQ function with its order…
- Excel PV of Future Value: Formula and ExamplesTo find the present value of a future amount in Excel, use the PV function with no periodic…
- Excel Column Width to Match Row Height?Excel has no built-in setting that makes a column’s width equal a row’s height: column width is measured…
- SUMIF Function Examples: Text, Numbers, Dates in ExcelExcel’s SUMIF function adds numbers that meet one condition. With product names in A2:A6 and sales in B2:B6,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.