Alok Paul
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.
Download Practice Workbook
Download the following practice workbook to exercise while you are reading this article.
Steps to Make a Vacation Calendar in Excel
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.
Read More: How to Create a Yearly Calendar in Excel (with Easy Steps)
📌 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 (With Easy Steps)
📌 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 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.
Read More: How to Create Calendar Using VBA in Excel (with Easy Steps)
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.
=DATE($B$5,1,1)
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.
=DATE($B$5,1,1)+14+CHOOSE(WEEKDAY(DATE($B$5,1,1)),1,0,6,5,4,3,2)
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.
=AD5+1
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
Read More: How to Create a Weekly Calendar in Excel (3 Suitable Ways)
📌 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:
=DATEVALUE("12/1/"&$B$5)-WEEKDAY(DATEVALUE("12/1/"&$B$5))+1
Read More: How to Create an Event Calendar in Excel (with Easy Steps)
📌 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, "")
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, "")
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, "")
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, "")
- Input a similar formula on all cells for the rest of the 11 months.
Formula Breakdown:
- YEAR(Sun_1st_Jan)
Returns the year value of Sun_1st_Jan.
Result: 2023
- MONTH(Sun_1st_Jan)
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
Read More: How to Create a Monthly Calendar in Excel (3 Effective Ways)
📌 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.
=Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
- 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.
Read More: How to Make a Blank Calendar in Excel (Download Free Template)
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.
=Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Vacation
- 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.
="Total Vacations: "&SUMPRODUCT(COUNTIF(Jan,Vacation))
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.
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. Please have a look at our website ExcelDemy and give your suggestions in the comment box.
