How To Manage Holiday Schedules in Excel
Reading Time: 4 minutes
Table Of Contents
Now that you’ve (tried, or successfully attempted) to integrate your personal calendar with Excel, you might be wondering… what’s the next step you can take towards mastering your leave tracking spreadsheet?
We’ve got your back. The answer is three words: managing holiday schedules.
With countless national holidays to keep track of (especially across different locations), every HR manager knows the struggle that comes with doing everything manually. Tracking everything for your offices worldwide sure does feel like an enormous task. We get it.
Luckily for you, there is a way you can make this process easier on Excel — and we’re gonna take a deep dive into exactly how. Without further ado, let’s jump right into it!
3 Ways You Can Manage Holiday Schedules in Excel
A good starting point to successfully start managing holiday schedules in Excel is our free Leave Tracking spreadsheet, which you’ve downloaded already. However, you can choose to follow this method in any spreadsheet you’d like, as long as it is in Excel. Choose the method that works best for you based on your needs and preferences!
Block off days in your shared employee calendar
The easiest way to keep track of any national holidays coming your way would be to block off each respective day in your employee’s personal time off calendar. Confused? We’ll show you how to do it below.
Let’s say you’re based in California, United States, and wish to manage holiday schedules for those based within the area. You would then pull up each department’s personal calendar, and manually block off each holiday by creating codes within your sheet.
To get a detailed list of all holidays and leave laws in your specific region, visit our handy Leave Laws page here.
In the example below, we’ve blocked off all applicable national holidays (the 1st and 17th) in the month of January 2022 for those located in the state of California. Seems easy, right? You can create a code for this Absence type to keep track accordingly. We’ve named our code “NH” which stands for non-deductible leave type National Holiday, as seen in the table to the right.
You can then repeat this process for every department and/or location you manage accordingly.
Create a holiday schedules shortcut within Excel
If the thought of manually blocking off each department/location’s holidays every month sounds too tedious (and rightfully so,) another alternative is creating a pivot table in Excel.
This table serves as an easy way for your employees to view all holidays during the year at a quick glance. The process is simple — start by creating a pivot table in Excel on a dedicated Holidays tab in Excel.
Then, fill out the table with all national holidays for the year alongside the dates. You can group the data by month, and use conditional formatting to highlight each state holiday. This way, you can see all the holidays for a particular month in a jiffy, and also easily update the holiday data as needed.
We’ve separated the table with columns like Day, Date, and Holiday Name, as seen below, and created a separate Holidays tab accessible from the same sheet.
It’s important to keep in mind that some rules state that when any federal holiday falls on Sunday, then that holiday is observed on the following Monday. Hence, a major drawback with this method is that if your employees choose to sandwich their holiday or work for time off in lieu, keeping track and informing your team might be hard.
Set up a macro using VBA in Excel
Last but not least, you can set up a macro to help keep track of national holidays in Excel. This method involves writing a macro in Visual Basic for Applications (VBA) in Microsoft Excel. A macro is a series of instructions that automates repetitive tasks and can be used to customize Excel to meet your specific needs.
Here’s the kicker: setting up macros requires some programming knowledge. But, once you’ve set it up, it will make keeping track of holiday schedules easier and more efficient.
Sample VBA
Here is a basic outline of how you can create a macro to keep track of national holidays:
- To access the macro-recording options, you’ll need to turn on the Developer tab in Excel. Go to File > Options > Customize Ribbon, and check the box next to Developer.
- Go to Developer > Macros, give the macro a name, and click Create. This will open the VBA editor, where you can start writing your macro.
- In the VBA editor, create a new subroutine to hold your macro code. Enter the dates and names of the holidays in an array, using the following format:
Dim holidays(1 To 10, 1 To 2) As Variant
holidays(1, 1) = “01/01/2023”
holidays(1, 2) = “New Year’s Day”
holidays(2, 1) = “07/04/2023”
holidays(2, 2) = “Independence Day”
4. In the subroutine, write the code that will loop through the holiday data and highlight the corresponding cells in the calendar. Here’s an example of what the code might look like:
Sub MarkHolidays()
Dim holidays(1 To 10, 1 To 2) As Variant
holidays(1, 1) = “01/01/2023”
holidays(1, 2) = “New Year’s Day”
holidays(2, 1) = “07/04/2023”
holidays(2, 2) = “Independence Day”
For i = 1 To 10
If holidays(i, 1) <> “” Then
With Range(“A1:A31”).Find(holidays(i, 1))
.Interior.ColorIndex = 3
End With
End If
Next i
End Sub
5. Lastly, test the macro by clicking the Run button in the VBA editor, or by pressing the F5 key. The macro should highlight the holiday dates in the calendar.
This is just a basic example of how you can create a macro in Excel to keep track of national holidays. You can modify this code as needed to meet your specific requirements. The options are endless!
Here’s a better way to do it…
What if there was an easier way to manage holiday schedules? A method that doesn’t require any time or effort and allows you to focus on other important tasks and your team self-manage PTO?
Don’t waste your time on setting up macros, creating pivot tables, or filling out spreadsheets. With dedicated leave management software like Vacation Tracker, the entire process is streamlined and automated so you can work smarter — not harder. Let us show you what we mean.
As an HR manager, all you have to do to manage holiday schedules are…
Go to the Location your team is located in from the Locations tab and click on View Holidays.
Follow the steps to import national holidays from our Dashboard (it takes less than a minute, we promise!).
And you’re good to go! All applicable Holidays will be integrated with your personal and shared calendars. Users in Vacation Tracker can then use the employee leave calendar to easily keep track of all national holidays from the comfort of their workspace.
See how easy that was? The entire process took less than a minute to set up and will save hours of valuable time. Sounds like a good deal, doesn’t it?
I hope these tips will help make tracking leaves in your organization even easier. If you have any additional questions about automating your workflow, please let us know at hello@vacationtracker.io — we’re always here to help!
Snigdha Gupta
An avid writer and aspiring marketer, Snigdha is a student at Concordia University’s John Molson School of Business.
