Creating a list of holidays in Excel is fairly straight-forward to accomplish with a nesting of the CHOOSE, WEEKDAY, and DATE functions. Using these three functions, you can build formulas in Excel that will automatically tell you when all of the holidays are for a given year and what day of the week each holiday falls on. Some holidays are pretty easy to determine. For example, Halloween occurs on the same day every year. But for the holidays that move every year (holidays like Thanksgiving, which occurs on the 4th Thursday of November), the solution is a little more complicated. At the end of this article, we’ll provide you with your very own Holiday Template, or you can use the information below to build your own, customized list of holidays.
The first step in building a list of holidays in Excel is in understanding how the CHOOSE, WEEKDAY, and DATE functions work. The DATE function simply returns the date (in Excel date format) from a value you specify. The syntax is DATE=(Year,Month,Day). So, all you need to do is specify the year, month, and day of the date you need. WEEKDAY works on a similar premise. It returns a value between 1 and 7 (1= Sunday, 2= Monday, etc.) based on a date that you specify. So, all you need to do is insert a date into the WEEKDAY formula, and it will tell you the day of the week that particular date falls on.
The key function to creating your list of holidays is the CHOOSE function. The way this function works is that you specify a list of options and then tell Excel which option you’d like to pick. The syntax is =CHOOSE(Option_You_Want,Option1,Option2,Option3,…). The first value in the function will be a positive number. So, if you put a “1” in there, the formula will return Option1. If you put a “2”, it will return Option2.
So, if you wanted to calculate the date that Martin Luther King, Jr. Day falls on in a given year, and your year is provided in cell B2, your formula would look like this:
This formula will determine what day of the week the first of the month falls on, and then add 14 days (since the holiday never falls on the first or second Tuesday of the month), and then adds additional days based on what day of the week the first of the month is. So, if the first of the month is a Sunday, the formula will add 14 to Jan. 1 (bringing you to Jan. 15) and then add 1 to that to get you from Sunday to the third Monday. The result is Jan. 16. You can use this same formula structure to build out your full list of holidays, especially for holidays that fall on a particular day of the month rather than a particular date.
Get Your Free List of Holidays Now!
Download a free template to create your own list of holidays! And don’t forget to sign up for our free email list, where you’ll receive free Excel tips and tricks directly in your inbox and exclusive discounts for our upcoming training classes.