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.
Weekday Numbers
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.
Thanksgiving Formula
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).
- DATE(C2,11,29)
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.
- WEEKDAY(DATE(C2,11,24))
To calculate the Thanksgiving date, the completed formula is:
=DATE(C2,11,29)-WEEKDAY(DATE(C2,11,24))
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!
_________________
Here is a generic formula for finding the Nth such-and-such day of the month…
=DATE(Y,M,1+7*Nth)-WEEKDAY(DATE(Y,M,8-DoW))
Where Nth is a 1, 2 ,3 or 4 for the first, second, third or fourth such-and-such day of the month. DoW is the weekday number with Sunday being 1, Monday being 2, etc. M is the month whose Nth DoW we are looking for and Y is the year we are interested in. Substituting Y=2011, M=11, Dow=5 and Nth=4 yields this…
=DATE(2011, 11, 1 + 7 * 4) – WEEKDAY(DATE(2011, 11, 8 – 5)
Performing the math and removing the spaces I inserted to “neaten things up” yields this…
=DATE(2011,11,29)-WEEKDAY(DATE(2011,11,3)
You can substitute C2 for the 2011 to match your blog article’s given conditions. Note that this is the same formula as in the blog article except there is a 3 instead of 24 for the day in the last DATE function call… the 3rd of the month is exactly 21 days (3 exact weeks) later than the 24th of the month, so they both fall on the same weekday; hence the two formulas produce identical results.
Hello;
I am trying to work out the percentage completed for a task with in a particular day. in my excel spread sheet I have
START TIME – DURATION (HR) – FINISH TIME – PERCENTAGE.
How do I work out the percentage from the current time to finish time which will give me my percentage?
Kind Regards & Thank-you