Free Download Excel Holiday Calendar Template + Formula to Get the Holiday Date

Last few weeks have been really hectic for me at work. Weekdays are crazy and the weekends are never enough.

Early this week, I was going through our annual holiday calendar to check for the next holiday, and plan a vacation if possible. And that gave me the idea for this blog post.

In this blog post, I will show you how to calculate the date of a given holiday (say Labor day). And you also get an awesome Excel holiday template (free download) at the end of this blog.

Formula to Calculate Holidays in Excel

Here is the formula to get the date for Labor Day in 2015 (which is the 1st Monday in September)

=DATE(2014,9,1)+IF(WEEKDAY(DATE(2014,9,1),2)>1,7-WEEKDAY(DATE(2014,9,1),2)+1,1-WEEKDAY(DATE(2014,9,1),2))

It returns 01 September 2014, which is the date of labor day in 2014.

This formula can easily be tweaked to get the date of holidays that fall on a given day of the month (such as President’s Day or Memorial Day).

How this Formula Works (Infographic)

Holiday Calendar - Date calculation in Excel

BONUS EXCEL HOLIDAY CALENDAR TEMPLATE

Here is a FREE Excel Holiday Calendar template that gives you:

  • The list of holidays (in the selected year).
  • The number of days to go for the next holiday (number in the yellow box).
  • The number of days to go for the next holiday (number in the blue box).

Excel Holiday Calendar Template

I have created this for the holidays in the US, as it occurs on fixed dates or days in a month. In India (where I live), many holidays depend on the position of the moon (and many other factors), and hence it’s difficult to come up with such a holiday template.

Download Excel Holiday Calendar Template
Download File

Note: Easter is one holiday that is difficult to calculate. I Googled and found a formula that seems to be working. However, I have no idea how it works 🙂

Excel Functions used to create this holiday calendar template: –> WEEKDAY | DATE | IF.

Related Templates:
  • Very creative little dashboard! My only recommendation would be to put the data inside an Excel table so it is easy to add/subtract holidays based on the year without messing up the lookup formulas. Great job!

    • Thanks Chris.. And your are right, table is the right way to go. Will make that change in the download file

  • shipsales

    I too was searching for a holiday calendar suitable for US Federal offices (observing the 10 standard holidays) and ended up hacking together the attached version, which is printable as well. Feel free to share!

  • Lauren

    Wow this is great! Any chance you have a dashboard for Public Holidays in India?

    • Sumit Bansal

      Hello Lauren.. A lot of Public holidays in India vary year on year. It makes almost impossible to get a formula that can track it.

    • Hey Lauren.. Public holidays in India are not on fixed dates or days, so it’s almost impossible to create a formula that can return the correct date for every year.

  • Frank

    Sumit – Very useful! Thank you for making the downloadable template! Quick question, how can I tweak the formulas, for say Independence Day, to show the holiday as the day before or the day after? For example, if 7/4 is a Saturday, the holiday “day off” is Friday 7/3. And if 7/4 is a Sunday, the day off is Monday 7/5.
    Thank you!

    • Hey Frank.. In such a case, you can create an additional column and use the following formula: =IF([@Weekday]=”Saturday”,[@Date]-1,IF([@Weekday]=”Sunday”,[@Date]+1,[@Date]))