How to Make a Vacation Calendar in Excel (with Detailed Steps)

There are several vacation days throughout the year. Usually, companies make a list of those vacation days and send it to the employees. But we can prepare vacation calendars in MS Excel in a smarter and more dynamic way. The advantage of making a calendar in Excel is that you can make any change in the vacation list that will impact the calendar. In this article, we will explain how to make a vacation calendar in Excel step-by-step with detailed explanations.

How to Make a Vacation Calendar in Excel: with Easy Steps

We make a vacation calendar for 2021 to 2030. There will be a button to change the year and we will get the vacation calendar of the respective year. Have a look at the steps in the below section for details.

📌 Step 1: Add a Spin Button to Select Year

First, we will add a Spin button to choose a year from a given list.

  • Look at the following image.

choosing an year

Here, cell B5 contains the year. We will insert Spin Button from the Form Controls section.

  • Go to the Developer tab.
  • Choose the Insert option from the Control group.
  • Finally, select the Spin Button (Form Controls).

Selecting Spin Button from Form Controls

  • Insert the Spin button in the dataset beside the Year cell.

Inserting a Spin button

  • We can see the Spin Button is being shown in your spreadsheet.

An Excel Spin button

The up arrow will increase, and the down arrow will decrease the value.

  • Now, we will configure the Spin button.
  • Select the Spin button and press the right button of the mouse.
  • Choose the Format Control option from the Context menu.

Approaching to format the Spin button

  • The Format Control window will appear.
  • Choose the Control tab.
  • Insert values for the Current value, Minimum value, Maximum value, and Incremental change section.
  • We also choose cell B5 as the Cell link.
  • Finally, press the OK button.

Setting the control value of the Spin button

After that, we can change the year value using the arrows.

📌 Step 2: Introduce a Suitable Calendar Format

Now, we will declare a calendar format and apply that in Excel.

  • We insert the name of the month and the first letter of the day’s name in the calendar format. Look at the image below.

a calendar format in excel

  • Similarly, we create the calendar format for the whole year.

calendar format of a year

Read More: How to Create Calendar with Time Slots in Excel

📌 Step 3: List All the Vacation Days of the Corresponding Year

We have the following vacation days of the year. We have to insert the vacation days in the dataset and apply that to the calendar.

A list of vacation days in a year

  • Hide the calendar format.
  • Now, create a data table to insert the vacation days in our dataset.
  • Our table has three columns: Vacation, When Occurs, and Date.
  • Select all three columns and press Ctrl+T to create a table.
  • We can see the Create Table window appears.
  • Mark the My table has headers option and then press the OK button.

Create a Table in Excel

  • We can see the Filter button has been added to the dataset.

Table inserted in the dataset

This indicates the tab has been created.

  • Now, copy the data from the Vacation list and paste that into our dataset.

Copy and paste vacation days in the working dataset

After that, we have to insert the dates of the vacations. Vacations are of two types. One is fixed, and another one is moving. We will use different functions to insert dates in the dataset.

Formula to Insert Fixed Vacation Dates:

Now, we will insert the dates of the fixed vacation days. We need to use the DATE function only.

  • Now, use the formula based on the DATE function on cell AD5.

Insert formula for fixed vacation days

This returns the date of the 1st of January.

  • Similarly, insert formulas for the rest of the fixed vacations.

Insert all vacation days in the dataset

Formula to Insert Variable Vacation Dates:

To get the moving vacation dates, we need to use a formula based on the combination of DATE, WEEKDAY, and CHOOSE functions.

  • Look at the following formula applied to cell AD6.

Formula for moving vacation dates

It returns based on cell B5 that is indicating the year.

  • Apply similar formulas for the rest of the moving vacation days.

Formula for all moving vacation dates

  • There is a movable formula for cell AD16.

Formula for a special vacation date

Formula Explanation:

  • DATE($B$5,1,1)

This returns a date value based on the inputs.

Result: 1/1/2023

  • WEEKDAY(DATE($B$5,1,1))

This returns the respective number of weekdays from the applied date.

Result: 1/1/2023

  • CHOOSE(WEEKDAY(DATE($B$5,1,1)),1,0,6,5,4,3,2)

Here, the CHOOSE function will return a number based on the result of the WEEKDAY function. We used 1 for the second argument of the CHOOSE function. Because we want to get the Monday and this is marked as 1 for Monday and similarly the rest of the days.

Result: 1

  • =DATE($B$5,1,1)+14+CHOOSE(WEEKDAY(DATE($B$5,1,1)),1,0,6,5,4,3,2)

Here, we added 14 as we want to get the 3rd Monday of the month with previously calculated formulas.

Result: 1/16/2023

📌 Step 4: Define the Names of the Operational Factors

Now, we will Define Names of some ranges that will be used for calculations.

  • First, go to the Formulas tab.
  • Then, click on the Name Manager of the Defined Names group.

Use Name Manager

  • The Name Manager window appears.
  • Press the New button.

Add newly defined name

  • The New Name window appears.
  • Insert a name in the Name box and put the following formula in the Refers to section.

Add new name with formula

  • Finally, press the OK button.

New name inserted in the Name Manager

We can see the newly created Name has been added in the Name Manager. This will return the date of the 1st Sunday on the 1st week of January.

  • Similarly, define new names for all 12 Just change the argument of the date from “1/1/” to “2/1/” and so on. For December we will use this:

12 New name range added in the Name Manager

📌 Step 5: Apply Formula to Insert Dates of Months in a Year

Now, we will insert a formula on the cells of the calendar to get dates.

  • First, we will insert the formula in January month.
  • Go to cell D6 and input the following formula.
=IF(AND(YEAR(Sun_1st_Jan) = $B$5,MONTH(Sun_1st_Jan) = 1), Sun_1st_Jan, "")

Formula for calculating dates in the calendar of 1st January

As the 1st day of January 2023 is Sunday, we get 1 in return as the date.

  • Then, go to cell E6 and insert this formula.
  =IF(AND(YEAR(Sun_1st_Jan+1) = $B$5,MONTH(Sun_1st_Jan+1) = 1), Sun_1st_Jan+1, "")

Formula for calculating dates in the calendar of 2nd January

Here, we added 1 with the Sun_1st_Jan. We will use this similar formula and increase 1 one by one for the rest of the cells.

  • The formula used on cell J11 is below.
  =IF(AND(YEAR(Sun_1st_Jan+41) = $B$5,MONTH(Sun_1st_Jan+41) = 1), Sun_1st_Jan+41, "")

Formula to calculate dates in the calendar for the last day of January

This is the last formula for the month of January.

  • For February month, use this formula. We replaced the Sun_1st_Jan with Sun_1st_Feb.
  =IF(AND(YEAR(Sun_1st_Feb) = $B$5,MONTH(Sun_1st_Feb) = 2), Sun_1st_Feb, "")

Formula for calculating dates in the calendar of 1st February

  • Input a similar formula on all cells for the rest of the 11 months.

Formula for calculating dates in the calendar for the whole year

Formula Breakdown:

  • YEAR(Sun_1st_Jan)

The YEAR function returns the year value of Sun_1st_Jan.

Result: 2023

  • MONTH(Sun_1st_Jan)

The MONTH function returns the month value of Sun_1st_Jan.

Result: 1

  • AND(YEAR(Sun_1st_Jan) = $B$5,MONTH(Sun_1st_Jan) = 1)

Checks if the year is equal to B5 and the month is equal to 1 for January.

Result: TRUE

  • IF(AND(YEAR(Sun_1st_Jan) = $B$5,MONTH(Sun_1st_Jan) = 1), Sun_1st_Jan, “”)

If the condition is fulfilled then return the value of Sun_1st_Jan, otherwise keep blank.

Result: 1

📌 Step 6: Define Named Range for Each Month

In this section, we will name the range of each month from the Name Box.

  • First, select Range D6:J11.
  • Then, go to the Name Box at the left upper side of the sheet and input Jan as the short form of January.

Create a named range for January

  • Similarly, insert names for the rest of the months. Look at the Name Box for the rest of the months.

Create a named range for 12  months

📌 Step 7: Apply Conditional Formatting to Highlight Vacations and Working Days

In this step, we will use Conditional Formatting to highlight blanks and dates without a vacation in the calendar.

Highlighting Blank cells:

1st we will highlight the blank cells of each month in the calendar.

  • Go to the Conditional Formatting Then, choose the New Rule option.

New Rule of Conditional Formatting

  • Choose the Format only cells that contain option as the Rule Type.
  • Choose Blanks as the Format only cells with section.
  • Then, click on the Format button to customize the formatted cells.

Configure New Formatting Rule window

  • Go to the Fill tab of the Format cells window.
  • Choose a color from the list.
  • After that, press the OK button.

Set format for conditional formatting

  • We returned to the previous window. This shows the Preview of the selected format.
  • Again, press the OK button.

Preview of conditional formatting

  • We did not select any range to apply the conditional formatting. For that, go to the Manage Rules option from the Conditional Formatting section.

Manage Rules of conditional formatting

  • The Conditional Formatting Rules Manager window appears.
  • Go to the Applies to section and put in the following formula.
  • After that, press the Apply and OK button.

Insert Range in the Conditional Formatting Rules Manager

  • Look at the calendar.

All blank cells are highlighted

All the blank cells of each month are filled with the selected color.

Highlighting Working Days:

Now, we will highlight the non-vacation days in the calendar using the Unique criteria of Conditional Formatting. And the rest of the days will be vacation days.

  • Go to Conditional Formatting >> New Rules.
  • Choose the Format only unique or duplicate values option from this window.
  • After that, choose Unique as the format.
  • Then, choose a color for the unique cells using the Format button.

Conditional formatting for unique values

  • Again, go to the Manage Rule section to insert the applicable range.

Insert range for unique values in Conditional formatting

  • Look at the calendar again.

Non-vacation days are highlighted

The vacation days contain the default color.

📌 Step 8 (Optional): Calculate Total Vacations

In this section, we will calculate the total vacation days for each month separately. We will use a formula based on the SUMPRODCT and COUNTIF functions.

  • Go to cell D12 and put in the following formula.
="Total Vacations: "&SUMPRODUCT(COUNTIF(Jan,Vacation))

Formula to calculate total vacation days

We get the total vacation days in January.

  • Use similar formulas for the rest of the 11 months, just change the month’s defined names.

Counting total vacation days for 12 months separately

Things to Remember

When using the DATE formula, we must follow the date format of the computer of the user. Like here we used  MM/DD/YYYY format.

Download Practice Workbook

Download the following practice workbook to exercise while you are reading this article.


In this article, we described each step to create a vacation calendar in Excel for a certain range of years. We also explained all the formulas used in this article. I hope this will satisfy your needs. For any further queries, comment below.

Related Articles

<< Go Back to Excel Calendar Templates | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF