Home > Formulas > Date > Calendar Excel Calendar FormulasCreate an Excel calendar, based on a few short formulas. Change the year or month number, to update the calendar, with that month's holidays highlighted. Stepbystep video and written steps. Note: Uses functions that are available in Excel 365, and in Excel for the Web. 
Excel Calendar Formulas  IntroIn this example, an Excel worksheet has 5 cells with formulas, and two data entry cells  for the year and the month.. From that simple layout, you can create a monthly calendar, based on the year number and a month number that you entered. Holidays from the selected month are highlighted, and the holiday names are listed at the top of the calendar. This video shows how to make the monthly Excel calendar, stepbystep, and there are written steps below the video. Video Timeline:

List of HolidaysIn the sample workbook, there is a list of holidays, on a worksheet named Lists. The holiday list is in a formatted Excel table, named tblHol

Enter Month and YearThe main sheet in the sample file has the calendar formulas, and the cells where you enter a year number and a month number.

Calendar Date FormulasOn the Calendar sheet, in column M, there are three cells with date formulas
1) Month StartFirst, I created the Month Start formula, because the other two formulas refer to the Month Start date. Here is the month start formula in cell M2:
The Excel DATE function returns a date, and it has 3 arguments:
For our formula, here are the settings for each argument:
2) Month EndNext, I created the Month End formula, which is used in the calendar's conditional formatting.. Here is the month end formula in cell M2:
The Excel EOMONTH function returns a date, and it has 2 arguments:
For our formula, here are the settings for each argument:
3) Start Week SundayFinally, I created the Month Start Week Sunday which gives us the starting date for the calendar. Here is the month end formula in cell M2:
The Excel WEEKDAY function returns a number, based on a date's day of the week, and it has 2 arguments:
Here's how our formula works:

Calendar Creation FormulaFor the calendar, the days will be in a standard wall calendar format:
Excel SEQUENCE FunctionTo create the calendar, with the above settings, the sample file uses the Excel SEQUENCE function. This function, which is available in Excel 365 and Excel for the Web, creates an array with a sequential list of numbers. The SEQUENCE function has the following syntax for its arguments, with one required argument, and 3 optional arguments:
The 4 arguments are:
SEQUENCE Formula for CalendarHere is the calendar creation formula in cell A4:
This formula uses the first 3 arguments for the SEQUENCE function
Example Month  6 WeeksFor example, here is the calendar for April 2023. The month starts on a Saturday, and ends on a Sunday, over a span of 6 weeks. Example Month  4 WeeksFor example, here is the calendar for February 2026. The month starts on a Sunday, and ends on a Saturday, with its 28 days fitting perfectly into only 4 weeks. 
Calendar Heading FormulasOn the Calendar sheet, there are two cells with formulas, to create the calendar headings. In row 3, below the formula cells, I added the weekday names, in cells A3: G3. Weekday NamesHere's a quick way to add the weekday names in row 3, for the calendar headings:
Main HeadingHere's the formula in cell A1, which shows the selected month name and year:
The TEXT function formats the month start date to show the full month name, and the year. List of HolidaysThe second heading formula is in cell A2, and it shows a list of the holidays in the selected month, if there are any. Here is the formula in cell A2:
And here's how the formula works:

Calendar FormattingIn the sample file, I formatted the calendar dates, the heading cells, the date formula cells, and the year/month cells. I used a combination of cell formatting and conditional formatting, described below. Year/Month and Date CellsFor the Year and Month cells, I used the following cell formatting:
Date Formula CellsFor the date formula cells, I used the following cell formatting:

Calendar Day CellsFor the calendar day cells, I used the following cell formatting:

Conditional Formatting Calendar CellsFor the calendar day cells, I used 2 conditional formatting rules:  1) Hide days before and after the month  2) Highlight holidays The rule details are shown below. 1) Hide Days Before and After MonthTo create a clean, uncluttered calendar, this conditional formatting rule hides the day numbers that are not within the selected month. To apply the rule, follow these steps:

2) Highlight HolidaysTo make holidays stand out in the calendar, this conditional formatting rule highlights the holiday cells for the selected month. To apply the rule, follow these steps:

Get the Sample FileTo see how these Excel calendar formulas work, download the sample Excel Calendar Formulas workbook. The file is zipped, and is in xlsx file format, with no macros 
Related Tutorials 
Last updated: April 17, 2023 4:26 PM