Recently, Jerry Latham showed us how to use Excel to calculate the date of Easter in any year, by using a worksheet formula or Excel User Defined Function (UDF).
Now, it’s getting close to Thanksgiving in the USA, so lets see how to calculate that date, with an Excel worksheet formula.
Date Range for Thanksgiving
First, you can use simple arithmetic to figure out the possible date range for the US Thanksgiving, which falls on the fourth Thursday in November.
The first Thursday could occur anytime during the first 7 days of November, and if we add 21 days to that date range, the fourth Thursday falls between November 22nd and November 28th each year.
As part of the formula to calculate the Thanksgiving date, we’ll use the WEEKDAY function, which assigns a number to each day of the week. The default setting is to start with Sunday, as weekday number 1. In the table below, you can see that Thursday is weekday number 5.
The Thanksgiving formula that I’m using was posted by Daniel.M in the old Excel newsgroups. The formula starts with the DATE function, using the year in cell C2, 11 as the month number, and 29 as the day. That is the first date after the latest possible Thanksgiving date (November 22-28).
We want the result to have a WEEKDAY value of 5 – a Thursday. So, the formula finds the weekday number for November 24th, which is 5 days prior to November 29th.
To calculate the Thanksgiving date, the completed formula is:
Other Thanksgiving Calculations
There are other ways to calculate the date for Thanksgiving, and other floating holidays. For a few more examples, you can visit the Excel Holidays page on Chip Pearson’s website.
Do you have another favourite method for calculating floating holidays?
Download the Excel Christmas Planner
If you’re ready to start shopping and organizing your holiday activities, you can visit the Contextures website, and download the Excel Christmas Planner. It has sheets to help you with your Christmas budget planning, gift lists, dinner preparation, and much more.
Please let me know if you have suggestions for improving the planner!