Excel Calendar with One Formula? Yes, Here’s How

Formulas & functions · Updated November 22, 2026

Yes, you can create a dynamic calendar in Excel with a single formula using the SEQUENCE function combined with date and formatting functions. The core formula, entered in one cell and spilled across a grid, generates all days of a month and can be formatted to look like a traditional calendar.

Why a Single Formula Calendar Works

Traditional Excel calendars use a grid of formulas, each cell calculating a day number based on the month and year. A single-formula approach leverages dynamic array functions (available in Excel 365 and Excel 2021) that spill results into adjacent cells automatically. This eliminates the need to copy formulas down or across, reduces file size, and makes the calendar instantly responsive to changes in the month or year.

The One-Formula Calendar: Step-by-Step

Here is the exact setup for a one-formula calendar that displays a full month in a 7-column grid (Sunday through Saturday).

  1. In cell A1, type a month number (e.g., 1 for January). In cell B1, type a year (e.g., 2025).
  2. Select cell A3 (or any cell where you want the calendar to start).
  3. Enter this formula:
=LET(
  d, DATE(B1, A1, 1),
  start, d - WEEKDAY(d) + 1,
  SEQUENCE(6, 7, start, 1)
)

This formula assumes the month number is in A1 and the year in B1. The LET function defines d as the first day of the month, then calculates the Sunday on or before that date. SEQUENCE(6,7,start,1) generates a 6-row by 7-column array of consecutive dates starting from that Sunday.

When you press Enter, the formula spills into 42 cells (6 weeks × 7 days). The result is a series of serial numbers (e.g., 45658 for a date in 2025). To display them as day numbers, select the spilled range and press Ctrl+1 to open Format Cells, then choose a custom format of d to show just the day number.

Formatting the Spilled Array into a Calendar

To make the array look like a calendar, you need to add a header row with weekday names and apply conditional formatting to hide dates from adjacent months.

  • In row 2, enter the weekday names manually: Sun, Mon, Tue, Wed, Thu, Fri, Sat across columns A through G.
  • Select the spilled range (e.g., A3:G8). Go to Home > Conditional Formatting > New Rule, choose “Use a formula to determine which cells to format,” and enter this formula:
=MONTH(A3)<>$A$1

Click Format, go to the Font tab, and set the font color to white (or a light gray) so dates from other months become invisible. This makes the calendar appear clean, showing only the current month’s days.

How the Formula Works Under the Hood

The magic relies on three functions:

  • DATE(year, month, day) – constructs a date from the given year and month. In the formula, DATE(B1, A1, 1) gives the first day of the target month.
  • WEEKDAY(date, [return_type]) – returns the day of the week as a number. The default (return_type=1) makes Sunday=1, Monday=2, etc. Subtracting this number from the first-of-month date and adding 1 yields the Sunday on or before that date.
  • SEQUENCE(rows, columns, start, step) – creates an array of sequential numbers. Here, it creates 6 rows and 7 columns, starting at the calculated Sunday and incrementing by 1 day.

Because SEQUENCE is a dynamic array function, the result spills automatically. If you change the month in A1 or the year in B1, the calendar instantly recalculates.

Customizing: Start Week on Monday, Different Formats

To start the week on Monday instead of Sunday, adjust the WEEKDAY function to use return_type 2 (Monday=1):

=LET(
  d, DATE(B1, A1, 1),
  start, d - WEEKDAY(d, 2) + 1,
  SEQUENCE(6, 7, start, 1)
)

Then change the header row to Mon through Sun. You can also display the full date (e.g., “15-Jan”) by using a custom number format like d-mmm. To show week numbers, add a column with a formula that calculates the ISO week number, but that would require a second formula.

Common Problems and How to Fix Them

If the calendar doesn’t spill, you likely have something in the adjacent cells. Clear any content in the range where the formula would spill. If you see #SPILL!, select the obstructing cell and delete its contents.

Another issue: the calendar may show 42 days even when the month has only 30 or 31 days. That’s by design—the extra cells are hidden via conditional formatting. If you prefer a shorter calendar that only shows the actual weeks, you can use a more complex formula with LET and FILTER, but that would exceed “one formula” simplicity.

If you’re using an older Excel version without SEQUENCE, this approach won’t work. You’d need a traditional multi-formula grid.

Alternatives: Traditional Grid vs. PivotTable Calendar

The one-formula method is ideal for a simple, dynamic monthly view. However, if you need a full-year calendar or want to integrate with a data list (e.g., showing events), consider these alternatives:

  • Traditional formula grid: Each cell has a formula like =IF(MONTH(DATE($B$1,$A$1,1)-WEEKDAY(DATE($B$1,$A$1,1))+1+(ROW()-3)*7+COLUMN()-1)=$A$1, ...). This works in any Excel version but is harder to maintain.
  • PivotTable calendar: Create a PivotTable from a list of dates, then group by month and day. This is useful for tracking events but not a pure calendar.
  • Excel’s built-in templates: Search “calendar” in File > New to find pre-designed templates that use VBA or multiple formulas. They are easy to use but not single-formula.

Frequently Asked Questions

Does this formula work in Excel 2019 or older?

No. The SEQUENCE and LET functions require Excel 365 or Excel 2021. In older versions, you must use a traditional grid with individual cell formulas.

Can I use this calendar in Google Sheets?

Yes, Google Sheets supports SEQUENCE and LET as well. The formula works the same, but you’ll need to use the same conditional formatting logic. Note that Google Sheets uses semicolons as argument separators in some locales, but in the US it’s commas.

How do I change the month or year quickly?

Simply type a new number in cell A1 (month) or B1 (year), and the calendar updates instantly. You can also use a spin button (Developer tab > Insert > Spin Button) linked to those cells for a more interactive feel.

Why does my calendar show dates from the previous and next month?

That’s normal because the grid always shows 6 weeks. The conditional formatting hides them by setting the font color to white. If you want to remove them entirely, you’d need to adjust the formula to only show days within the month, but that would require a more complex array.

Can I add events or notes to specific dates?

Not easily with a single spilled formula, because spilled cells are treated as one array. You could overlay a separate range with event text, but that’s not a single-formula solution. For event tracking, consider a different layout.

Is there a way to make the calendar start on any day of the week?

Yes, change the WEEKDAY return type. Use 2 for Monday, 3 for Tuesday, etc. Then adjust the header row accordingly. The formula will always start on the chosen weekday.

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.