Microsoft Excel Calendar Formula: Build a Monthly View

Formulas & functions · Updated November 20, 2026

In Microsoft Excel for Microsoft 365, enter a year in B1 and a month number in C1, then put this formula in A3 to create a Monday-first monthly calendar that spills into a six-row, seven-column grid: =LET(monthStart,DATE($B$1,$C$1,1),gridStart,monthStart-WEEKDAY(monthStart,2)+1,dates,SEQUENCE(6,7,gridStart),IF((MONTH(dates)=$C$1)*(YEAR(dates)=$B$1),dates,"")). Add weekday labels in A2:G2; the formula returns real Excel dates for the chosen month and blanks for neighboring-month days.

What the calendar formula needs

The formula works by combining the year and month inputs into a real date, finding the first day of the week containing that date, and generating 42 consecutive dates. It then displays only dates belonging to the selected month and year. The result is a reusable calendar: change either input and the grid recalculates.

Set up the worksheet like this:

  • Enter a four-digit year in B1, such as 2025.
  • Enter a month number from 1 to 12 in C1; for February, enter 2.
  • Type Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, and Sunday into A2:G2.
  • Paste the formula into A3 and leave A3:G8 empty so the result can spill into those cells.

For February 2025, the calendar begins its first week on Monday, January 27. February 1 appears under Saturday, and February 28 appears under Friday. Dates in January and March are blank, so the grid keeps its consistent shape without showing days outside the selected month.

How the formula works

DATE($B$1,$C$1,1) creates the first day of the chosen month. The dollar signs lock the year and month references if the formula is copied, although this particular dynamic-array formula is normally entered only once. WEEKDAY(monthStart,2) numbers Monday as 1 and Sunday as 7. Subtracting that weekday number and adding 1 moves back to the Monday at the start of the first calendar row.

SEQUENCE(6,7,gridStart) returns six rows and seven columns of consecutive date values, starting at that Monday. The final IF checks each date’s month and year. The multiplication between the two tests acts as an AND condition: both the month and year must match the inputs for the date to be shown. This year check also makes the logic explicit when the calendar crosses December and January.

The formula uses LET to give names to intermediate calculations, which makes the expression easier to follow than repeating the same date calculations several times. LET and SEQUENCE are available in Microsoft 365 and Excel 2021 or later. In Excel for the web, the formula can be entered in a cell as usual; the cells in the spill area must still be clear.

Format the spilled dates as calendar days

The formula returns date values, not text. That matters because the results can still be sorted, compared, and used in date calculations. To show only the day number while preserving the underlying date, select A3:G8, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, choose Number, then Custom, and enter d as the format. The cells will display 1, 2, 3, and so on rather than full dates.

For a more readable sheet, widen columns A:G enough to show the weekday headings, center the headings and day numbers, and add borders to A3:G8. You can increase row height to leave room for notes. These are presentation choices; they do not change the dates produced by the formula. If you want dates such as 1-Feb rather than just 1, choose a suitable date format in Format Cells instead of the custom format d.

Do not type into the cells where the formula’s output needs to appear. A value, another formula, or even a merged cell in the spill range can prevent the array from expanding. Keep A3:G8 free of such obstructions, and enter or edit the formula only in A3.

Choose Monday or Sunday as the first day

The main formula uses a Monday-first week, a common layout in many regions. If your calendar should start on Sunday, use Sunday-to-Saturday headings in A2:G2 and replace the formula in A3 with this version. It assumes the same year in B1 and month number in C1, and the result still occupies A3:G8:

=LET(monthStart,DATE($B$1,$C$1,1),gridStart,monthStart-WEEKDAY(monthStart,1)+1,dates,SEQUENCE(6,7,gridStart),IF((MONTH(dates)=$C$1)*(YEAR(dates)=$B$1),dates,""))

In this version, WEEKDAY(monthStart,1) numbers Sunday as 1, so the calculation backs up to Sunday. The weekday headings must match the formula’s week order; otherwise, the dates will appear under the wrong labels. Excel’s regional calendar conventions do not automatically rearrange labels typed into worksheet cells.

Make the month easier to change

For a simple calendar, typing a number into C1 is sufficient. To reduce input mistakes, select C1 and use Data > Data Validation. Set Allow to Whole number and set the minimum and maximum values to 1 and 12. This prevents entries such as 13, which would make the formula calculate a different date than the user may expect.

You can label the inputs in nearby cells, for example, Year in A1 and Month in A2, if you rearrange the layout. If you do, adjust the formula references to point to the new input cells and make sure the weekday headings remain immediately above the calendar grid. For a month name, either enter its corresponding number in C1 or use a separate display cell with a date-format choice; the formula itself expects a numeric month input.

To create another month’s calendar on the same sheet, copy the input area and formula to a separate, unobstructed range, then update the formula references for that range. Each dynamic array needs enough blank cells to spill. Alternatively, duplicate the worksheet for each month and change the input values. A single formula-driven sheet is usually easier to maintain when the goal is to browse different months rather than print a full year at once.

Add events and highlight weekends

A useful next step is to store appointments in a separate list rather than typing them into the calendar’s spill cells. For example, put event dates in J2:J50 and descriptions in K2:K50. The calendar can remain a clean date grid, while the list serves as the source for lookup formulas, filters, or a separate schedule. Keeping event data separate makes it easier to update dates and descriptions without disturbing the array formula.

You can apply conditional formatting to emphasize Saturdays and Sundays. With the calendar formula in A3 and dates in A3:G8, select A3:G8 and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Use this rule formula: =AND(A3<>"",WEEKDAY(A3,2)>5). Choose a fill or font format, then confirm the rule. The relative reference A3 lets Excel evaluate each cell in the selected range; the blank check prevents empty cells outside the month from being highlighted.

Conditional formatting changes how cells look, not the dates they contain. You can create other rules for holidays or deadlines, but any formula-based rule should refer to the top-left cell of the selected range and use absolute references where a reference must remain fixed. If you want event descriptions visible inside the calendar, that requires a different design from this date-only formula, because each grid cell currently returns either one date or a blank.

Common errors and limitations

  • #SPILL! usually means that a cell in A3:G8 is occupied or merged. Clear the blocked cells, unmerge them if needed, and keep the formula in A3.
  • #NAME? can indicate that the Excel version does not recognize LET or SEQUENCE, or that a function name was mistyped. These functions require Excel 2021 or Microsoft 365.
  • A number appears instead of a day number. Excel stores dates as serial numbers. Apply a date format such as d to show only the day of the month.
  • Dates appear under the wrong weekday. Check that the heading order matches the formula: Monday through Sunday for the first version, or Sunday through Saturday for the alternative.
  • The month is wrong. Confirm C1 contains a number from 1 to 12 and B1 contains the intended year. The month input is numeric, not a typed month name.

A six-row grid is intentional. Some months occupy only four or five calendar weeks, but a six-row layout handles every month without changing the grid’s dimensions. This makes formatting and printing more predictable. The blank cells at the beginning or end are not missing dates; they represent dates outside the selected month that the formula deliberately suppresses.

Options for older Excel versions

Excel 2021 and Microsoft 365 support the dynamic-array formula above. Older Excel releases may not recognize LET or SEQUENCE, so pasting the formula there can produce an error. A practical alternative is to use a calendar template or build a fixed 6-by-7 grid with conventional date formulas and fill them across and down. That approach takes more setup and may need extra logic to hide dates outside the month, but it avoids relying on newer dynamic-array functions.

Templates are also useful when the priority is a polished printable layout with space for notes, rather than an automatically changing date grid. For a compact, reusable worksheet, the formula is easier to adapt. In either case, check that displayed dates correspond to the intended year and that week headings follow the chosen first day of the week before printing or sharing the calendar.

Frequently Asked Questions

What is the formula for a monthly calendar in Excel?
For Microsoft 365 or Excel 2021 and later, use the LET and SEQUENCE formula above with the year in B1 and month number in C1. It creates a 6-by-7 calendar grid with Monday as the first day of the week.

Can Excel make a calendar automatically when I change the month?
Yes. Enter a different month number in C1 or year in B1. The formula recalculates the calendar dates automatically, provided the spill range is clear.

How do I make Sunday the first day in an Excel calendar?
Use the Sunday-first version of the formula and change the headings to Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday. The formula and labels need to use the same order.

Why does my calendar formula show #SPILL!?
Excel cannot place the full array if one or more cells in its output range are occupied or merged. Clear A3:G8, unmerge any merged cells in that area, and keep the formula at A3.

Can I use this formula in an older version of Excel?
Not in versions that lack LET and SEQUENCE. Use Excel 2021 or Microsoft 365 for this dynamic-array formula, or choose a template or a manually filled calendar design for an older release.

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.