To change the year in an Excel calendar formula, update the year cell the calendar refers to; the dates should recalculate automatically. For example, if the year is in B1, the month number is in B2, and the calendar grid begins in A5, change B1 to the new year. If your calendar has no year input cell, edit the year in its date formula or add a year cell and point the formulas to it. The steps below show how to set up a reusable calendar in Excel for Microsoft 365 on Windows.
What You’ll Need
- An Excel workbook with a calendar template, or a blank worksheet where you can build one.
- A cell for the year and a cell for the month. This guide uses B1 for the year and B2 for the month number, from 1 to 12.
- A seven-column by six-row area for calendar dates. The example uses A5:G10, with Sunday as the first day of the week.
You can use another layout if you prefer, but adjust the cell references in the formulas to match it. Save a copy of an existing calendar before changing its formulas, especially if it contains appointments, notes, or custom formatting.
Step-by-Step
- Check how the existing calendar gets its year. Click a date cell and inspect the formula bar. If the formula refers to a year selector, note its cell address. For example, a formula may use an absolute reference to B1. If the cell contains a date entered directly rather than a formula, changing a separate year cell will not affect it. You can also select a date and look at the formula bar to distinguish a calculated date from a typed value. In Excel for Windows, Formulas > Show Formulas can display formulas across the sheet; select it again to return to the usual results view.
- Set up the year and month inputs. Enter a four-digit year, such as 2025, in B1. Enter a month number from 1 to 12 in B2; for example, enter 2 for February. Add labels in nearby cells if useful, such as Year in A1 and Month in A2. The formula below expects B2 to contain a number, not the word February. If you already have a month selector or year dropdown, keep using it and substitute its cell address for B1 or B2 in the formula.
- Enter the date formula in the first calendar cell. With the year in B1, the month number in B2, and the top-left date cell in A5, enter this formula in A5. It finds the Sunday on or before the first day of the selected month, so the first row starts on Sunday:
=DATE($B$1,$B$2,1)-WEEKDAY(DATE($B$1,$B$2,1),1)+1
For example, with B1 set to 2025 and B2 set to 2, the formula returns January 26, 2025, because that is the Sunday at the beginning of the calendar week containing February 1. This is intentional: the first few cells of a monthly calendar can show dates from the previous month. The dollar signs keep the year and month references fixed when you copy the formula.
- Fill the whole calendar grid with consecutive dates. To make every cell calculate its own date from the top-left cell, replace the formula in A5 with the following version. With the same layout—year in B1, month number in B2, and the grid beginning at A5—enter it in A5, then copy or drag it across and down through G10:
=DATE($B$1,$B$2,1)-WEEKDAY(DATE($B$1,$B$2,1),1)+1+(ROW()-ROW($A$5))*7+COLUMN()-COLUMN($A$5)
The ROW and COLUMN parts add the correct number of days as the formula moves through the grid. Each row advances by seven days, and each column advances by one day. Use the fill handle at the lower-right corner of A5 to fill across to G5 and then down to row 10, or copy A5, select A5:G10, and paste. A six-row grid is useful because some months need six calendar weeks when the first day falls late in the week.
- Format the cells so they look like calendar dates. Select A5:G10, open the Format Cells dialog, choose Number, then Custom, and enter d as the format. This displays the day number while retaining each cell’s full date value. You can instead choose a date format that includes the month if you want to make dates from adjacent months obvious. Add weekday headings in A4:G4 in the same order as the formula, Sunday through Saturday. Use borders, alignment, and row height to make the grid easier to read; those design choices do not change the underlying dates.
- Optionally de-emphasize days outside the selected month. The grid contains the complete weeks around the month, so it may show a few dates from the previous and following months. To fade those dates, select A5:G10 and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the selected range starting at A5 and the month number in B2, use this rule formula:
=MONTH(A5)<>$B$2
Choose a font color that blends into the cell background, or choose another subdued format, then confirm the rule. Because A5 is a relative reference, Excel evaluates the appropriate date in each cell. The dates remain real date values even when they are hard to see. If you prefer to display every date, skip this optional rule.
- Change the year and verify the result. Enter a different year in B1 and check that the calendar dates and weekday positions update. Try setting B1 to 2024 and B2 to 2: the grid should include February 29, 2024, which is a leap day. Then set B1 to 2025 and leave B2 at 2: February should end on the 28th. If you use a month dropdown, change that too and confirm that the dates move to the correct month. Save the workbook after confirming the layout and any conditional formatting still work.
If your existing template already has a formula in each date cell, you may not need to rebuild the grid. Find the year input it references, update that input, and test the result. The setup above is most useful when the calendar has hard-coded years, broken references, or no clear year selector.
Common Mistakes
Changing the displayed text instead of the date input. A heading such as 2025 may be plain text and not control any calendar formulas. Update the cell used by the date calculations, not just the heading. If you are unsure which cell matters, select a date and inspect its formula bar for references.
Entering a month name when the formula expects a number. In this guide B2 must contain a number from 1 to 12. Typing February there can cause an error or an incorrect result. You can make a dropdown with month names, but then the formula must convert the selected text into a month number; do not mix the two input styles without adjusting the formula.
Using the wrong week-start convention. The formula shown uses WEEKDAY with return type 1, which treats Sunday as day one. If your column headings start with Monday, the dates will appear under the wrong headings unless you use a Monday-based formula as well. With the year in B1, month number in B2, and grid starting at A5, use this formula instead:
=DATE($B$1,$B$2,1)-WEEKDAY(DATE($B$1,$B$2,1),2)+1+(ROW()-ROW($A$5))*7+COLUMN()-COLUMN($A$5)
That version places Monday in the first column. Make sure the headings also run Monday through Sunday.
Overwriting formulas while entering dates or appointments. Typing into a date cell replaces its formula, so that one square may stop updating when you change the year. Put notes in a separate row, a text box, or a dedicated appointment area if you need the calendar to remain reusable. Before clearing or pasting into the grid, check whether you are replacing formulas you want to keep.
Using too few rows. A five-row grid does not fit every month. If the first day and the number of days extend across six calendar weeks, a five-row calendar can omit the final dates. Keep six rows available, even when some months leave a few cells unused.
Tips
Keep the year and month in clearly labeled input cells near the calendar. This makes it easier for another person to use the workbook and reduces the risk of changing a formula by mistake. You can apply a distinct fill color to the inputs so they are easy to spot. If the workbook is shared, add a brief note that B1 controls the year and B2 controls the month.
Use a real year number in B1, not a date-looking text entry such as 2025 calendar. DATE expects a year, a month number, and a day number. Real date values also make sorting, date calculations, and conditional formatting more reliable than text labels that merely look like dates.
Keep your weekday headings consistent with your formula. For a Sunday-first calendar, use Sunday in A4 and Saturday in G4. For a Monday-first calendar, use Monday in A4 and Sunday in G4, along with the Monday formula above. This alignment matters even when the dates look plausible at a glance.
Consider testing several combinations before sharing the workbook: a 31-day month, a 30-day month, February in a leap year, and February in a non-leap year. This catches common issues with the grid size and month selector. If your calendar includes holidays or linked schedules, verify those separately; changing the calendar date formulas does not automatically update a manually typed holiday list.
Excel for Mac uses the same DATE, WEEKDAY, ROW, and COLUMN formulas, so the date calculations do not need a Mac-specific version. The ribbon layout and some dialog details can differ slightly from Windows. In Excel for the web, the formulas and grid can also recalculate, although some formatting controls may be arranged differently. If you use a downloaded template with named ranges or other formulas, preserve its existing structure unless you know which references can be changed safely.
Troubleshooting
The calendar does not change when you edit the year. Click a date cell and inspect the formula bar. If it contains a fixed year, such as a DATE formula with 2025 written directly into it, that formula is not linked to B1. Replace the fixed year with a reference to your year input, or use the reusable setup above. Also confirm that you changed the input cell the formula actually references, rather than a nearby label.
You see a number instead of a day of the month. Excel stores dates as serial numbers, and the cell may be using General or Number formatting. Select the grid and apply a date format, or use the custom format d to display day numbers. The underlying cell value can still be a valid date even if its current format makes it look like an ordinary number.
The formula displays as text or shows an error. Check that the entry begins with an equals sign and that the cell is not formatted as Text. If it is, change the format to General, edit the cell, and confirm the formula again. Check that B1 contains a valid year and B2 contains a month number from 1 through 12. A pasted formula with missing parentheses or altered punctuation can also fail; compare it with the version in this guide.
Dates are shifted under the weekday headings. Check whether the first heading is Sunday or Monday and use the matching formula. A Sunday-based formula paired with Monday-first headings produces a consistent seven-day sequence but places each date in the wrong weekday column. Change either the headings or the formula convention, not just the date format.
Dates from other months remain visible. That is the normal result of a full-week calendar grid. If you added the optional conditional formatting rule, make sure it applies to A5:G10 and that the formula uses A5 as the starting relative reference. Also confirm B2 contains the selected month number. If you do not want to hide adjacent dates, remove the rule; the dates themselves are not an error.
Changing the year produces a strange result. Check that the year is a number and that the month input is not blank or outside 1–12. Then inspect the references in the formula: the dollar signs should keep B1 and B2 fixed as the grid formula is copied. If you use a calendar template with merged cells or a different starting position, adapt the cell references before filling the formula across the whole area.
Frequently Asked Questions
Can I change the year without rebuilding the calendar?
Yes. If the date formulas refer to a year input cell, change that cell and let Excel recalculate. Rebuilding is only needed if the calendar uses fixed dates or its formulas do not refer to a year input.
Why does my calendar formula show dates from the previous month?
A standard calendar grid begins on the first day of the week containing the first of the month. That means the opening cells can belong to the previous month, and the last cells can belong to the next month. They make the weeks complete and can be left visible or de-emphasized with conditional formatting.
Can I use a month name instead of a number?
Not with the formulas as written: B2 must be a month number from 1 to 12. A month-name dropdown is possible, but you need to adapt the formula to convert the chosen name into a number before using it in DATE.
Does the same formula work for leap years?
Yes. DATE and WEEKDAY use Excel’s date system, so the calendar includes February 29 when the selected year is a leap year. Check the result by selecting February 2024, then February 2025.
Can I make the week start on Monday?
Yes. Use the Monday-based formula in Common Mistakes and make the headings run Monday through Sunday. Both the formula and the weekday labels need to follow the same convention.
Why do some calendar cells stop updating after I add notes?
Entering a note directly into a formula cell replaces its date formula. Keep notes in a separate area or use a layout that reserves space for appointments, then restore the formula if a date cell was overwritten.
Related
- How to Add a Black Border in Excel to Cells and Tables
- How to Add Borders in Excel With Shortcut Keys
- Add a Zero Before a Number in Excel Without Losing It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Sort and Filter a Table in Excel Step by StepTo sort and filter an Excel table, select a cell in it and use the arrow in a…
- Replace a Circular Reference With an Excel FormulaYes—replace a circular reference by rewriting the calculation so it uses input cells or earlier results, rather than…
- Excel IFERROR Formula Examples: Uses and Common FixesIf the value is in A2 and the divisor is in B2, use =IFERROR(A2/B2,"Cannot divide") to show a…
- Excel Day and Date Formulas: Show Days, Dates, and NamesIn Excel, use =DAY(A2) to return the day of the month from a date in A2, or =TEXT(A2,"dddd")…
- Excel Compare Dates to Today: Formulas and ExamplesTo compare a date in Excel with today, use the TODAY() function: for a date in A2, =A2=TODAY()…
- How to Copy Formulas in Excel to a New Workbook SafelyTo copy formulas into a new Excel workbook, open both workbooks, copy the formula cells in the source,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.