Excel Calendar With Correct Dates: A Step-by-Step Guide

How-to basics · Updated November 20, 2026

To make an Excel calendar with the right dates, enter a year and month, then use a date formula to fill a six-week grid that starts on the correct weekday. In Excel for Microsoft 365 on Windows, a single SEQUENCE formula can generate all 42 dates; formatting and a weekday check make the calendar easy to read and verify. You will need Excel and the year and month you want to show.

What You’ll Need

  • Excel for Microsoft 365 on Windows. The main grid formula uses SEQUENCE, which is also available in Excel 2021.
  • A year and month for the calendar, such as 2026 and 5 for May.
  • A blank worksheet with room for seven weekday columns and six date rows.

Step-by-Step

  1. Set up the input cells. On a blank worksheet, put the label Year in A2 and the year, such as 2026, in B2. Put Month (1-12) in C2 and the month number, such as 5, in D2. Using a number for the month makes the formula predictable and avoids ambiguity between month names and dates. Leave B5:H10 clear; the calendar formula will need this entire area for its results.
  2. Add a title that follows the selected month. Put the title formula in B3. With the year in B2 and month number in D2, enter =TEXT(DATE($B$2,$D$2,1),"mmmm yyyy"). For B2 set to 2026 and D2 set to 5, the result is May 2026. You can format the title in bold and increase its font size, then center it across B:H if desired. Use Home > Merge & Center only for the title row, not for any calendar date cells.
  3. Enter the weekday headings in the correct order. In B4:H4, type Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, and Sunday, one heading per cell. The formula in the next step uses Monday as the first day of the week, so the headings must match that order. If you prefer abbreviated labels, use Mon through Sun, but keep their positions unchanged. Do not sort the headings alphabetically.
  4. Generate the six-week date grid. Select B5 and enter =SEQUENCE(6,7,DATE($B$2,$D$2,1)-WEEKDAY(DATE($B$2,$D$2,1),2)+1,1). This assumes the year is in B2, month number in D2, weekday headings are in B4:H4, and B5:H10 is empty. It spills 42 consecutive date values across six rows and seven columns. For May 2026, the first cell is April 27, 2026, because May 1 falls on a Friday; the last cell is June 7, 2026. The dates before and after May are intentional: they complete the calendar weeks. Do not type the formula separately in every grid cell.
  5. Format the dates as day numbers. Select B5:H10, open the Format Cells dialog with Ctrl+1 on Windows, choose Number > Custom, and enter d as the format. The cells will show 27, 28, and so on, while retaining full date values underneath. That distinction matters: the entries remain real Excel dates, so their weekday positions and date-based formatting stay accurate. If you want leading zeroes, use the custom format dd instead. On Mac, Command+1 opens Format Cells; the formula and date values work the same way.
  6. Make dates outside the chosen month less prominent. Select B5:H10, then choose Home > Conditional Formatting > New Rule. Choose the option to use a formula to determine which cells to format. With the grid starting at B5 and the month number in D2, use =MONTH(B5)<>$D$2 as the rule formula, set the Applies to range to B5:H10, and choose a lighter font color or fill. B5 is deliberately relative, so Excel checks each cell in the selected range; $D$2 stays fixed on the month input. This change is visual only and does not remove any dates.
  7. Improve spacing and readability. Select B:H and use Home > Format > Column Width to make the columns wide enough for the headings. Select rows 5:10 and choose Home > Format > Row Height to provide space for notes or events. Add borders around B4:H10 and use a contrasting fill for the heading row. If the calendar is for printing, choose Page Layout > Orientation > Landscape, then use File > Print to review the page preview before printing.
  8. Check the result against the inputs. Confirm the title shows the intended month and year, that the first date is under the correct weekday, and that the grid contains every date in the month exactly once. For May 2026, verify that 1 appears under Friday, 31 appears under Sunday, and the adjacent dates are shaded as outside-month dates. Change D2 to another month and confirm both the title and grid update. Change B2 as well to check that leap years and year boundaries are handled by Excel’s date calculations.

If you use an Excel version without SEQUENCE, you can fill the grid with a formula copied into each cell. With B2 holding the year, D2 holding the month number, and B5 as the top-left date cell, enter =DATE($B$2,$D$2,1)-WEEKDAY(DATE($B$2,$D$2,1),2)+1+(ROW()-ROW($B$5))*7+COLUMN()-COLUMN($B$5) in B5, then copy it across and down through H10. The formula uses each cell’s row and column to advance the date by the right number of days. Format the completed range as d or dd as above.

Common Mistakes

  • Putting the first of the month in the first cell. That places the first date under Monday whether or not the month actually starts on Monday. Calculate the start of the week before the first instead, as the grid formula does.
  • Mixing the weekday order and formula. The main formula is Monday-first. If the headings start with Sunday, the dates will appear under the wrong labels unless you also change the formula’s weekday calculation.
  • Using text instead of dates. Typing day numbers such as 1, 2, and 3 by hand can look right at first but does not automatically adjust when the month changes. Keep actual date values in the cells and change only their display format.
  • Overwriting part of the spilled result. A value or merged cell anywhere in B5:H10 can prevent SEQUENCE from filling the range. Clear the whole area before entering the formula, and avoid merging date cells.
  • Changing the month input to a name without changing the setup. The instructions expect a number from 1 to 12 in D2. A text entry such as May may make the date formula return an error.

Tips

  • Use Data > Data Validation for D2: choose Whole number, set the data to between, and enter 1 and 12 as the limits. This helps prevent invalid month entries. You can also apply whole-number validation to B2 and choose a reasonable year range for your calendar.
  • Freeze the weekday labels if you plan to scroll: select B5, then choose View > Freeze Panes > Freeze Panes. The row above and the columns to the left stay visible while you move through the sheet.
  • To make a Sunday-first calendar, change the weekday headings to Sunday through Saturday and use the Sunday-based start calculation. With the same inputs and grid position, the start-date part becomes DATE($B$2,$D$2,1)-WEEKDAY(DATE($B$2,$D$2,1),1)+1. Use it in the SEQUENCE formula in place of the Monday-based start calculation. For May 2026, the grid then begins Sunday, April 26.
  • Keep the date grid as a real-date grid even if you add events. Put notes in a separate area or in a second line within a cell; do not replace a date formula with a typed event label if you still need the calendar to update automatically.
  • Excel formulas may use semicolons instead of commas in some regional settings. If Excel rejects a formula with commas, check the list separator used by your installation and replace the separators consistently.

Troubleshooting

The formula shows #SPILL!: Check that B5:H10 is empty and contains no merged cells. Select the range and clear its contents, then enter the formula only in B5. A spill error can also occur if the formula is inside an Excel Table; move the calendar grid outside the table.

The result looks like large numbers: The formula has probably returned valid Excel date serials, but the cells are formatted as General or Number. Select B5:H10 and apply the custom d format. Do not use a formula that turns the dates into text just to hide the year.

The weekday positions are off by one day: Compare the weekday headings with the start-day setting. The supplied formula is Monday-first and uses WEEKDAY with return type 2. If you changed the headings to Sunday-first, use the Sunday-first version in Tips. Also make sure the selected formula cell is B5 and the headings begin in B4.

The title or grid displays an error: Check that B2 contains a year and D2 contains a whole number from 1 to 12. In regional settings that use semicolons, replace formula argument commas with semicolons. If the formula is shown as text rather than calculated, set the cell format to General, re-enter the formula, and ensure the entry begins with an equals sign.

The month does not update: If you used the SEQUENCE version, change B2 or D2 and check that calculation is set to automatic under Formulas > Calculation Options > Automatic. If you used the fill-down alternative, make sure the formula was copied through the full B5:H10 range.

Frequently Asked Questions

Why does the calendar show dates from the previous and next month?
Most month views use complete weeks. A six-row grid therefore includes any days needed to finish the first and last weeks. The conditional formatting rule makes those dates visually distinct without breaking the continuous date sequence.

Will the calendar handle leap years?
Yes. DATE and WEEKDAY calculate from the year and month inputs, so February has 29 days in leap years and 28 in other years. For example, changing the input to February 2024 displays February 29 in the correct weekday column.

Can I make a calendar that starts on Sunday?
Yes. Change the heading order to Sunday through Saturday and use the Sunday-first start calculation in Tips. The heading order and the formula must agree; changing only one will misalign the dates.

Can I use this in Excel for the web?
Yes. Excel for the web supports SEQUENCE and the date formulas used here. The ribbon layout and some formatting dialogs can differ from Windows desktop Excel, but the cell layout and formula logic are the same.

Can I display the full date instead of only the day number?
Yes. Select B5:H10 and choose a date format from Home > Number Format, or set a custom format such as d mmm. The cell still stores the complete date, including its year, even when the display shows only a day and abbreviated month.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.