How to Create a Vacation Calendar in Excel – 8 Steps

Create a vacation calendar from 2021 to 2030 with a button to change the year.

Step 1 – Add a Spin Button to Select the Year

Look at the following image.

choosing an year

B5 contains the year.

  • Go to the Developer tab.
  • Choose Insert in Control.
  • Select 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

The Spin Button is displayed.

An Excel Spin button

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

  • Configure the Spin button.
  • Select the Spin button and right-click.
  • Choose Format Control.

Approaching to format the Spin button

  • In the Format Control window, choose Control.
  • Enter values in Current value, Minimum value, Maximum value, and Incremental change.
  • Select B5 as Cell link.
  • Click OK.

Setting the control value of the Spin button

You can change the year using the arrows.


 Step 2 – Enter a Suitable Calendar Format

  • Enter the name of the month and the first letter of the name of the day in the calendar format.

a calendar format in excel

  • 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 Holidays in the Year

  • Enter the holidays in the calendar.

A list of vacation days in a year

  • Hide the calendar format.
  • Create a data table to enter the holidays with three columns: Vacation, When Occurs, and Date.
  • Select the three columns and press Ctrl+T to create a table.
  • In Create Table, check My table has headers and click OK.

Create a Table in Excel

The Filter button is added to the dataset.

Table inserted in the dataset

 

  • Copy the data from the Vacation list and paste it into the dataset.

Copy and paste vacation days in the working dataset

 


Formula to Enter Fixed Vacation Dates:

  • Enter the formula in AD5.
=DATE($B$5,1,1)

Insert formula for fixed vacation days

It returns: 1st of January.

  • Enter formulas for the rest of the fixed vacations.

Insert all vacation days in the dataset


Formula to Enter Variable Vacation Dates:

  • Enter this formula in AD6.
=DATE($B$5,1,1)+14+CHOOSE(WEEKDAY(DATE($B$5,1,1)),1,0,6,5,4,3,2)

Formula for moving vacation dates

It returns data based on B5 that indicates the year.

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

Formula for all moving vacation dates

  • There is a movable formula for AD16.
=AD5+1

Formula for a special vacation date

Formula Explanation:

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

This returns a date value based on the input.

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)

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 to get Monday.

Result: 1

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

14 was added to get the 3rd Monday of the month with the previously calculated formulas.

Result: 1/16/2023


Step 4 – Define the Names of the Operational Factors

  • Go to the Formulas tab.
  • Click Name Manager in Defined Names.

Use Name Manager

  • In the Name Manager window, click New.

Add newly defined name

  • In the New Name window, enter a name in Name.
  • Enter the following formula in Refers to.

Add new name with formula

  • Click OK.

New name inserted in the Name Manager

Name is added to the Name Manager. It will return the date of the 1st Sunday in the 1st week of January.

  • Define new names for the 12 months, changing the date argument from “1/1/” to “2/1/” and so on. For December use:
=DATEVALUE("12/1/"&$B$5)-WEEKDAY(DATEVALUE("12/1/"&$B$5))+1 

12 New name range added in the Name Manager


Step 5 – Enter a Formula to Insert Dates of Months in a Year

  • Enter the formula in January.
  • Go to D6 and use 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, 1 is returned as the date.

  • Go to E6 and enter 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

1 was added to Sun_1st_Jan. Use a similar formula and increase it 1 one by one for the rest of the cells.

  • The formula used in J11 is:
  =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 January.

  • For February, use this formula. Sun_1st_Jan was replaced 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 to all cells for the other 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, it returns the value Sun_1st_Jan, otherwise it returns blank.

Result: 1


Step 6 – Define a Named Range for Each Month

  • Select D6:J11.
  • Go to the Name Box and enter Jan (short form for January).

Create a named range for January

  • Enter the names of the rest of the months.

Create a named range for 12  months


 Step 7 – Apply Conditional Formatting to Highlight Vacations and Working Days

Highlighting Blank cells:

  • Go to Conditional Formatting and choose New Rule.

New Rule of Conditional Formatting

  • Choose Format only cells that contain in Rule Type.
  • Choose Blanks in Format only cells with.
  • Click Format.

Configure New Formatting Rule window

  • Go to the Fill tab.
  • Choose a color.
  • Click OK.

Set format for conditional formatting

  • Preview the selected format.
  • Click OK.

Preview of conditional formatting

  • To select a range to apply conditional formatting, go to Manage Rules in Conditional Formatting.

Manage Rules of conditional formatting

  • In the Conditional Formatting Rules Manager window, go to Applies to and enter the following formula.
=Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec 
  • Click Apply and OK.

Insert Range in the Conditional Formatting Rules Manager

  • Look at the calendar.

All blank cells are highlighted

All blank cells are filled with the selected color.


Highlighting Working Days:

  • Go to Conditional Formatting >> New Rules.
  • Choose Format only unique or duplicate values.
  • Choose Unique as the format.
  • Select a color for the unique cells in Format.

Conditional formatting for unique values

  • Go to Manage Rule and enter the range.
=Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,Vacation

Insert range for unique values in Conditional formatting

  • Look at the calendar.

Non-vacation days are highlighted

Vacation days contain the default color.


Step 8 (Optional) – Calculate Total Vacations

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

Formula to calculate total vacation days

The total vacation days in January will be displayed.

  • Use similar formulas for the other 11 months, changing the name of the months.

Counting total vacation days for 12 months separately


 

Download Practice Workbook

Download the practice workbook to exercise.


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

 

 

ExcelDemy
Logo