Excel Formula for Creating a Calendar

Formulas & functions · Updated November 22, 2026

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 to H9 (6 rows × 7 columns).
  • Add weekday headers in row 3: Mon, Tue, … Sun (or Sun first if you chose WEEKDAY(d,1)).
  • Format the cells with a date format like d to show just the day number.
  • Merge the title cell (e.g., B1) and use =DATE(B1,B2,1) formatted as mmmm yyyy to 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 where MONTH(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 WEEKDAY return type. 2 starts Monday, 1 starts Sunday.
  • Year/month changes don’t update: Ensure B1 and B2 are 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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.