Lesson 59: How to create a Holiday Calendar in Excel

In this article, I will show you how to create a holiday calendar for any year (2021, 2022, …) in Excel.

Before you start: the CHOOSE function returns a value from a list of values, based on the number of places. For example, =CHOOSE(3, “Car”,”Train”,”Boat”,”Plane”) will return Boat. The WEEKDAY function returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week.

How to create a holiday calendar in Excel

1. If you enter a year in cell C2, Excel returns all holidays in that year.

2. Holiday information will be described in the form: x day in month y. As the example below is Thanksgiving Day.

You can see the calendar below to understand better.

Explain:

DATE(C2,11,1) = 11/1/2025. WEEKDAY(DATE(C2,11,1) = 7 (Saturday).

Now the formula will be 11/1/2025 + 21 + CHOOSE(7,4,3,2,1,0,6,5) = 11/1/2025 + 21 + 5 = 11/27/2025.

We need 5 more days because it takes 5 days until the first Thursday of November. From there, it will take another 21 days (3 weeks) until the 4th Thursday of November.

It doesn’t matter which date November 1 falls on, the CHOOSE function will add the correct number of days until the first Thursday of November. Then it will always take another 21 days until the 4th Thursday of November. So , this formula works for every year.

3. One more example is Martin Luther King Jr. Held on the 3rd Monday in January.

You can see the calendar below to understand better.

In addition, you can also view many other excel articles here.