Why do dates in my Excel calendar show up from the wrong month?

This is by design. A 6x7 grid contains 42 cells but most months have 28-31 days, so the first and last rows often include days from the adjacent month. To visually distinguish them, apply conditional formatting with =MONTH(A3) <> MONTH($B$1) and format those cells with grey text. If you prefer a strict current-month-only view, replace the =PreviousCell + 1 formula with =IF(MONTH(PreviousCell + 1) = MONTH($B$1), PreviousCell + 1, "").

How do I make an Excel calendar where I can add events or notes?

Either use the 6-row x 7-column grid with enlarged row heights so you can type notes directly below each date, or keep the calendar compact and use a separate Events table with Date and Note columns. Then use VLOOKUP or TEXTJOIN to pull events matching each calendar cell's date: =TEXTJOIN(", ", TRUE, IF(tblEvents[Date] = A3, tblEvents[Note], "")) — array entered in pre-365 Excel, dynamic in Excel 365. This keeps the calendar clean and the event data maintainable.

Excel

How to Create a Calendar in Excel That Updates Automatically Every Month

Q: How do I create a calendar in Excel that updates automatically every month?

Put the month and year in one input cell (for example =DATE(2026, 4, 1) in cell B1), then build a 6-row x 7-column grid whose first cell equals B1 minus WEEKDAY(B1) so the calendar always starts on the correct Sunday or Monday. Each subsequent cell adds 1 to the previous. Change B1 and the entire calendar updates. This pattern works from Excel 2007 onwards and does not require macros.

Q: What is the formula for an automatic calendar in Excel?

Three formulas form the backbone. For the first date cell: =B1 - WEEKDAY(B1, 2) + 1 (for Monday-start weeks). For every subsequent cell: =PreviousCell + 1. For dates outside the current month, use conditional formatting with =MONTH(A3) <> MONTH($B$1) to grey them out. In Excel 365 you can use SEQUENCE as a single-formula alternative: =SEQUENCE(6, 7, B1 - WEEKDAY(B1, 2) + 1).

Q: How do I highlight weekends or holidays on an Excel calendar?

Use conditional formatting with a formula rule. For weekends: =WEEKDAY(A3, 2) > 5 (highlights Saturday and Sunday). For holidays: =COUNTIF(HolidayList, A3) > 0 where HolidayList is a named range on another sheet containing public holidays. The conditional formatting applies across the whole grid because the formula uses a relative reference to the first calendar cell.

By Sagnik Bhattacharya 18 Feb 2026 4 min read

Coding Liquids blog cover featuring Sagnik Bhattacharya for creating an automatic calendar in Excel, with date-grid and planning visuals.

A reusable calendar is one of those spreadsheet projects that looks simple until you want it to stay useful month after month. The moment the calendar has to update cleanly, handle dates properly, and support planning work, the structure matters.

Coming Soon

Complete Excel Guide with AI Integration

Master formulas, pivot tables, data analysis, and charts — with AI integration.

Learn more

The good news is that modern Excel makes this much easier than the old copy-and-edit approach. Using DATE, WEEKDAY, and (on Excel 365) SEQUENCE, you can build a calendar whose entire grid updates when you change one input cell. Combine that with conditional formatting for weekends and holidays, and a small Events table for notes, and you have a planner that rebuilds itself every month for as many years as you want.

This tutorial is written for people who want a calendar they control — not a template they are afraid to touch. It assumes basic Excel (cell references, entering formulas, copying a formula across a range). It works in Excel 2016, 2019, 2021, 365, Excel for Mac, and Excel for the web. The Excel 365 SEQUENCE shortcut is optional; the core pattern works everywhere.

By the end you will have: (1) a one-cell month input that controls the entire calendar, (2) the three formulas that make the grid fill itself, (3) conditional formatting rules for weekends, holidays, and out-of-month dates, (4) an Events table that links notes to dates, (5) a Troubleshooting reference for the five most common problems. For the wider Excel context, start at the Excel formulas and analysis hub.

Quick answer

Build the calendar from a real month input and date logic rather than a hard-coded grid. Once the calendar is driven by dates, it becomes far easier to reuse for planning, attendance, or team scheduling.

You need a reusable monthly planning layout that works every month of the year.
You want to avoid rebuilding the calendar by hand each month.
You want a base sheet that later supports attendance tracking, event scheduling, or project deadlines.

The three ingredients are: (a) one input cell with a date for the first of the month (e.g. =DATE(2026, 4, 1)), (b) a 6x7 grid where the first cell is the previous Sunday or Monday of that date, and (c) conditional formatting that dims out-of-month days and highlights weekends.

Step 1 — Set up the month input

A reusable calendar begins with one input that defines the month and year. Everything else should flow from date logic, not manual typing.

In cell B1, enter:

=DATE(2026, 4, 1)

This evaluates to 1 April 2026. To switch to any other month, either edit the formula, or better, split it into three driver cells: a Year cell (B1), a Month cell (B2), and a formula cell B3 = =DATE(B1, B2, 1). Add data validation on the Month cell (list 1-12) so the user cannot type an invalid month. Now the whole calendar can be rebuilt by typing two numbers.

Real-world scenario. A school admin used to duplicate a calendar tab every month for 12 class groups — that was 144 calendars per year to manually re-number. Switching to a single driver-cell calendar meant she could change one Month cell at the start of each month and all 12 class calendars (one per sheet, each pointing at the shared input) updated instantly. Setup was 30 minutes; saved roughly 4 hours per month indefinitely.

Beginner pitfall. Entering the month as a text label ("April 2026") instead of a real date. Text labels cannot be used in WEEKDAY or DATE calculations, so every downstream formula breaks. Always use =DATE(year, month, 1) — Excel treats this as a real date you can do arithmetic on, and you can still format the cell to display "April 2026" using Format Cells → Custom → mmmm yyyy.

Step 2 — Build the 6×7 date grid

Once the grid is driven by real dates, you can format, highlight weekends, add events, or connect the calendar to attendance or project workflows more cleanly.

Use cells A3:G8 for the calendar body (six rows, seven columns). Row 2 has the day-of-week headers: Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday (or Sunday-first if you prefer).

The first calendar cell, A3, must hold the first visible date — which is either the 1st of the month (if the month starts on a Monday) or the most recent Monday before the 1st. Formula:

A3:  =B1 - WEEKDAY(B1, 2) + 1

The WEEKDAY(date, 2) call returns 1 for Monday through 7 for Sunday — useful because subtracting it from B1 "rewinds" to the start of that week. For a Sunday-first calendar, use WEEKDAY(B1, 1) and subtract.

Every subsequent cell just adds one day:

B3:  =A3 + 1
C3:  =B3 + 1
...
G3:  =F3 + 1
A4:  =G3 + 1
...

Copy this pattern through all 42 cells (6 rows x 7 columns). Or on Excel 365, use a single SEQUENCE formula in A3:

=SEQUENCE(6, 7, B1 - WEEKDAY(B1, 2) + 1)

Format the entire calendar area with a custom date format d (displays just the day number, e.g. "15" instead of "15/04/2026"). Alternatively d mmm shows "15 Apr" which helps in weeks that span month boundaries.

Step 3 — Highlight weekends, out-of-month days, holidays, and today

Select A3:G8, Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format":

Weekends grey: =WEEKDAY(A3, 2) > 5 → Format: light grey fill
Out-of-month days dim: =MONTH(A3) <> MONTH($B$1) → Format: grey text, white fill
Today highlighted: =A3 = TODAY() → Format: bold, yellow fill
Holidays marked: =COUNTIF(Holidays, A3) > 0 where Holidays is a named range containing public holiday dates → Format: red text

Real-world scenario. A UK marketing team tagged Bank Holidays this way and the calendar automatically marked 1 Jan, Easter Monday, May Day, Spring Bank, Summer Bank, Christmas and Boxing Day once the holiday list was entered. When the list needed an extra date (King's Coronation 2026), they added one row to the Holidays named range and every 2026 calendar sheet picked it up.

Beginner pitfall. Using absolute references ($A$3) in conditional formatting formulas. Absolute references cause every cell in the range to evaluate against A3 specifically, so either all cells are highlighted or none are. Use relative references (A3) so Excel adjusts the reference for each cell in the range. The exception is references to the month input ($B$1) which legitimately need to stay fixed.

Step 4 — Add events or notes

A calendar is not only a date exercise. It also needs space for notes, events, or markers, depending on the planning job it supports.

Option 1: Enlarge row heights and type notes

Simplest approach. Select rows 3-8, Home → Format → Row Height → 60 pixels. Now each calendar cell has space below the date number for a short note. Works well for personal planners and small team calendars (under 20 events per month).

Option 2: Keep the calendar compact, use an Events table

On a separate sheet, create an Excel Table tblEvents with columns Date and Note. Then in each calendar cell, combine the day number with events matching that date:

=TEXT(A3, "d") & IF(COUNTIF(tblEvents[Date], A3) > 0, CHAR(10) & TEXTJOIN(CHAR(10), TRUE, IF(tblEvents[Date] = A3, tblEvents[Note], "")), "")

Enable Wrap Text on the calendar cells (Home → Wrap Text). In pre-365 Excel the array part needs to be entered with Ctrl+Shift+Enter; in 365 it works dynamically. The result: the Events table stays the source of truth, and the calendar is just a view.

Worked example: a simple team planner

A small team uses one calendar sheet to mark leave days, launches, and monthly reminders. The month changes from one input cell, and the calendar updates without rebuilding the layout.

Inputs sheet setup: B1 = =DATE(B3, B4, 1), B3 = 2026, B4 = dropdown-validated 1-12. Calendar header shows the month via =B1 with custom format mmmm yyyy.

Events sheet: tblEvents with rows like:

Date          Note
2026-04-02    Product launch
2026-04-10    Alex leave
2026-04-10    Priya leave
2026-04-15    Board meeting
2026-04-22    Team offsite

Calendar sheet: A3:G8 uses the SEQUENCE or +1 pattern; conditional formatting highlights weekends and today; events appear under each day via the TEXTJOIN formula above. Changing B4 to 5 switches the entire view to May with all of the April events disappearing and May events appearing.

The full formula reference

Where	Formula	What it does
B1 (or B3) Month input	`=DATE(B1, B2, 1)`	Single source of truth — Year + Month → first of month
A3 First grid cell (Mon-first)	`=B1 - WEEKDAY(B1, 2) + 1`	Most recent Monday on or before the 1st
A3 First grid cell (Sun-first)	`=B1 - WEEKDAY(B1, 1) + 1`	Most recent Sunday on or before the 1st
B3 onwards (per-cell)	`=A3 + 1`	Each cell is one day after the previous
A3 (single dynamic-array)	`=SEQUENCE(6, 7, B1 - WEEKDAY(B1, 2) + 1)`	Microsoft 365 / 2021 — fills the whole 6×7 grid in one cell
Conditional format — weekends	`=WEEKDAY(A3, 2) > 5`	Greys out Saturday and Sunday
Conditional format — out-of-month	`=MONTH(A3) <> MONTH($B$1)`	Dims days that belong to neighbouring months
Conditional format — today	`=A3 = TODAY()`	Highlights today's cell
Conditional format — holidays	`=COUNTIF(Holidays, A3) > 0`	Marks any date that appears in the Holidays named range
Calendar cell with events	`=TEXT(A3, "d") & IF(COUNTIF(tblEvents[Date], A3) > 0, CHAR(10) & TEXTJOIN(CHAR(10), TRUE, IF(tblEvents[Date] = A3, tblEvents[Note], "")), "")`	Shows day number plus matching event notes
Header cell display format	Custom format `mmmm yyyy`	Renders B1 as "April 2026" without changing the underlying date
Day-only display format	Custom format `d`	Shows the day number only inside grid cells

Variations: customise for your planning job

The driver-cell + 6×7 grid skeleton stays identical. What changes is what you layer on top — extra columns on Events, extra conditional-format rules, or a different cadence header.

Use case	What to add	Conditional-format extras
Personal monthly planner	Single Events table	Today highlight only
Team leave / WFH planner	tblEvents columns: Date, Person, Type	Colour by Type (leave / WFH / training)
School / class calendar	Holidays named range, Term named range	Term-time vs holiday colours
Editorial / content calendar	tblEvents columns: Date, Channel, Owner, Status	Colour by Status (draft / scheduled / live)
Project deadline calendar	tblEvents columns: Date, Milestone, Project	Bold for milestones; red within 7 days
Year-at-a-glance (12 months)	Twelve copies of the grid driven by `=DATE(B1, n, 1)`	Same rules, repeated per month

For a year-at-a-glance, lay 12 mini-grids out 4 columns × 3 rows on one sheet. Each grid points at a different DATE(B1, n, 1) driver where n runs 1–12. Change B1 (the year) and all 12 grids reflow simultaneously.

Common mistakes

Hard-coding dates into the grid — any reusable calendar must be formula-driven.
Treating a one-off month view as if it were reusable — duplicate-and-edit breaks the first time a formula or format needs to change.
Using =TODAY() inside a saved calendar — this will recalculate every time the workbook opens, which you may not want. Use TODAY() only in conditional formatting rules, not in the calendar cells themselves.
Mixing calendar grid cells with event-entry cells — keep events in a dedicated Table so the calendar stays visually clean.
Forgetting to widen column widths to match — calendars look wrong when the 7 day columns are different widths. Select columns A-G, right-click → Column Width → set to 14 for a square grid.

Troubleshooting common Excel calendar errors

These are the five most common problems with auto-updating Excel calendars and their exact fixes.

1. Calendar does not start on the correct day of the week

Cause: mismatch between the WEEKDAY type argument (1 for Sunday-start, 2 for Monday-start) and the day-of-week header row. Fix: if your headers are Monday-Tuesday-...-Sunday, use =B1 - WEEKDAY(B1, 2) + 1. If headers are Sunday-Monday-...-Saturday, use =B1 - WEEKDAY(B1, 1) + 1. Mismatched settings cause the calendar to start on Sunday when you expected Monday, shifting every date by one column.

2. Dates display as 5-digit numbers (e.g. 45753)

Cause: Excel stores dates internally as serial numbers. The 45753 is 1 April 2026 without the date format applied. Fix: select the calendar range, Home → Number Format → choose Short Date (or Custom → d for day-only). This is the most common "my formulas look broken" question but nothing is broken — it is just display.

3. Conditional formatting highlights only A3 or nothing at all

Cause: absolute reference in the formula rule (=$A$3 = TODAY() instead of =A3 = TODAY()). Absolute references make every cell check only A3. Fix: edit the rule (Home → Conditional Formatting → Manage Rules → Edit Rule) and remove the dollar signs in front of the row and column. Keep dollar signs on references to fixed cells like the month input $B$1.

4. Events table TEXTJOIN formula returns #SPILL! or #VALUE!

Cause: in pre-Excel-365 versions, the IF(tblEvents[Date] = A3, ...) portion is an array formula that must be entered with Ctrl+Shift+Enter. Without that, Excel only evaluates the first match. In Excel 365, dynamic arrays handle this automatically, but if you see #SPILL! it means a neighbouring cell is blocking the spill range. Fix: in pre-365, press F2 on the cell and then Ctrl+Shift+Enter. In 365, clear cells adjacent to the spill.

5. Calendar recalculates slowly when I change the month

Cause: heavy use of volatile functions (TODAY(), NOW(), OFFSET(), INDIRECT()) inside the calendar grid, or the workbook has many calendar sheets all pointing at a shared Events table. Fix: avoid putting TODAY() inside grid cells — use it only in conditional formatting. If you have many calendar sheets, consider one Master calendar with a dropdown for the team/class, rather than 12 duplicated tabs.

Frequently Asked Questions

How do I create a calendar in Excel that updates automatically every month?

Put the month in one input cell using =DATE(year, month, 1). Build a 6x7 grid where the first cell is =B1 - WEEKDAY(B1, 2) + 1 and every other cell adds one day. Change the input cell and the grid updates.

What is the formula for an automatic calendar in Excel?

First cell: =B1 - WEEKDAY(B1, 2) + 1. Subsequent cells: =PreviousCell + 1. Or in Excel 365: =SEQUENCE(6, 7, B1 - WEEKDAY(B1, 2) + 1) as a single formula.

How do I highlight weekends or holidays on an Excel calendar?

Conditional formatting with a formula rule. Weekends: =WEEKDAY(A3, 2) > 5. Holidays: =COUNTIF(Holidays, A3) > 0 where Holidays is a named range on a Holidays sheet.

Why do dates from the wrong month show up on my calendar?

A 6x7 grid has 42 cells but months have 28-31 days, so the first and last rows include adjacent-month days. Dim them with conditional formatting: =MONTH(A3) <> MONTH($B$1) formatted with grey text.

How do I make an Excel calendar with events or notes?

Either enlarge row heights and type notes directly, or maintain a separate Events Table and pull matching events via =TEXT(A3, "d") & IF(COUNTIF(tblEvents[Date], A3) > 0, CHAR(10) & TEXTJOIN(CHAR(10), TRUE, IF(tblEvents[Date] = A3, tblEvents[Note], "")), "").

When to use something else

For full team scheduling with per-person leave tracking, an attendance tracker is a better fit. For project deadlines and dependencies, use a project tracker. When the calendar needs to integrate with a budget or cashflow, pair it with the monthly budget spreadsheet.