Home > Formulas > Date > Calendar

Excel Calendar Formulas

Create 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. Step-by-step video and written steps.

Note: Uses functions that are available in Excel 365, and in Excel for the Web.

Excel Calendar Formulas - Intro

In 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, step-by-step, and there are written steps below the video.

Video Timeline:

• 00:00 Introduction
• 00:13 Start Calendar
• 00:58 Year and Month
• 01:25 Date Formulas
• 02:20 Month End
• 02:50 Sunday Formula
• 04:56 Calendar Formula
• 05:32 Calendar Format
• 06:15 Calendar Heading
• 07:13 Hide Dates
• 08:35 Highlight Holidays
• 09:48 List Holidays
• 10:22 Get the Excel File

List of Holidays

In 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

• In the table, the first column contains the holiday dates
• That column is named Holidays

Enter Month and Year

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

• For the month, enter a number between 1 and 12
• For the year, enter any number between 1900 and 9999

Calendar Date Formulas

On the Calendar sheet, in column M, there are three cells with date formulas

1. Month Start - cell M2
2. Month End - cell M3
3. Month Start Week Sunday - cell M1

1) Month Start

First, 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:

• =DATE(J1,J2,1)

The Excel DATE function returns a date, and it has 3 arguments:

• 1) year, 2) month, 3) day

For our formula, here are the settings for each argument:

1. year is in cell J1
2. month number is in cell J2
3. We want the first day of the month, so the number 1 is used for the third argument.

2) Month End

Next, I created the Month End formula, which is used in the calendar's conditional formatting.. Here is the month end formula in cell M2:

• =EOMONTH(M2,0)

The Excel EOMONTH function returns a date, and it has 2 arguments:

• 1) Start Date, 2) Months

For our formula, here are the settings for each argument:

1. start date is in cell M2
2. months is set at zero, because we want the end date for the start date's month

3) Start Week Sunday

Finally, 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:

• =M2-(WEEKDAY(M2)-1)

The Excel WEEKDAY function returns a number, based on a date's day of the week, and it has 2 arguments:

• 1) serial number (date)
• 2) [optional] return type (weekday order)

Here's how our formula works:

• Start with the month start date, in cell M2
• Subtract the result of the WEEKDAY formula, minus 1, to find the Sunday in the start week
  1. First argument in WEEKDAY is the start date (M2), minus one day
  2. Default return type is used - Sunday (1) to Saturday (7)

Calendar Creation Formula

For the calendar, the days will be in a standard wall calendar format:

• 7 columns - one for each weekday, Sunday to Saturday
• 6 rows - one for each week -- months can range from 4 to 6 weeks, so allow for the maximum
• First day of month should appear in its correct day column

Excel SEQUENCE Function

To 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:

• =SEQUENCE(rows,[columns],[start],[step])

The 4 arguments are:

• rows - required - number of rows to use in the formula result
• columns - optional - number of columns to use in the formula result
• start - optional - first number in the sequence
• step - optional - amount to increment each subsequent value

SEQUENCE Formula for Calendar

Here is the calendar creation formula in cell A4:

• =SEQUENCE(6,7,M1)

This formula uses the first 3 arguments for the SEQUENCE function

• rows - 6 - each month's calendar could use a maximum of 6 weeks
• columns - 7 - a column for each weekday
• start - M1 - cell with formula that calculates Sunday in month's start week

Example Month - 6 Weeks

For 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 Weeks

For 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 Formulas

On 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 Names

Here's a quick way to add the weekday names in row 3, for the calendar headings:

• In cell A3, type the short weekday name, "Sun"
• Next, select cell A3, and point to its fill handle
• When the pointer chantges to a black plus sign, drag to the right, across to cell G3.
  • As you drag, a tooltip will show the weekday name for each cell, as you pass it
• Release the mouse button, and Excel will AutoFill the rest of the weekday names

Main Heading

Here's the formula in cell A1, which shows the selected month name and year:

• =TEXT(M2,"mmmm yyyy")

The TEXT function formats the month start date to show the full month name, and the year.

List of Holidays

The 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:

• =TEXTJOIN(", ",TRUE, FILTER(tblHol[Holiday], (tblHol[Date]>=M2) * (tblHol[Date]<=M3), "No Holidays"))

And here's how the formula works:

• The FILTER function returns a list of holiday names, where the dates are between the month start date and the month end date.
• The TEXTJOIN function combines all the holiday names in a single cell, with a "comma space" separator.
• If the month doesn't have any holidays, the formula result shows "No Holidays".

Calendar Formatting

In 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 Cells

For the Year and Month cells, I used the following cell formatting:

• Fill colour - light blue
• Alignment - Horizontal - Centre
• Alignment - Vertical - Centre
• Number Format - General

Date Formula Cells

For the date formula cells, I used the following cell formatting:

• Alignment - Vertical - Centre
• Number Format - Short Date

Calendar Day Cells

For the calendar day cells, I used the following cell formatting:

• Alignment - Vertical - Top
• Number Format - Custom - "d" (to show the day number only)
• Font - Calibri, 16 pt, Bold
• Border - All sides - Medium Grey

Conditional Formatting Calendar Cells

For 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 Month

To 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:

• Select cells A4:G9, where the SEQUENCE formula was applied
• On the Excel Ribbon, go to the Home tab
• In the Styles group, click the Condtional Formatting command
• Click the New Rule command, top open the New Formatting Rule dialog box
• In top section (Select a Rule Type), click on Use a formula to determine which cells to format
• In the Rule Description section, enter the following formula:
  • =OR(A4<$M$2,A4>$M$3)
• Next, click the Format button
• In the Format Cells dialog box, for Font colour, select white
• Next, go to the Fill tab, and for Background colour, select white
• Click OK, to close the Format Cells dialog box, and click OK to apply the Conditional Formatting rule.

2) Highlight Holidays

To 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:

• Select cells A4:G9, where the SEQUENCE formula was applied
• On the Excel Ribbon, go to the Home tab
• In the Styles group, click the Condtional Formatting command
• Click the New Rule command, top open the New Formatting Rule dialog box
• In the top section (Select a Rule Type), click on Use a formula to determine which cells to format
• In the Rule Description section, enter the following formula:
  • =COUNTIF(Holidays,A4)
• Next, click the Format button
• In the Format Cells dialog box, for Background colour, select light orange
• Click OK, to close the Format Cells dialog box, and click OK to apply the Conditional Formatting rule.

Get the Sample File

To 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

Date Functions

Conditional Formatting Intro

Conditional Formatting Examples

Excel Named Table

Spill Function Examples