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.

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).

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

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

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.

• 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.

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.

• Similarly, we create the calendar format for the whole 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.

• 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.

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

This indicates the tab has been created.

• Now, copy the data from the Vacation list and paste that into our 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.

This returns the date of the 1st of January.

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

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.

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

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

• There is a movable formula for cell AD16.

📌 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.

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

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

• Finally, press the OK button.

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:

📌 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.

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.

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.

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.

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

📌 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.

• Similarly, insert names for the rest of the months. Look at the Name Box for the rest of the 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.

• 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.

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

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

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

• 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.

• Look at the calendar.

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.

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

• Look at the calendar again.

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.

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.

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.

Conclusion

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

• How to Create Project Management Calendar in Excel
• How to Create an Event Calendar in Excel

<< Go Back to Excel Calendar Templates | Excel Templates