Every year, on 4th Thursday of November, folks in US celebrate Thanksgiving day.
Thanksgiving Day is a harvest festival. Traditionally, it is a time to give thanks for the harvest and express gratitude in general. [Source: Wikipedia]
A similar holiday exists in Canada too, they celebrate it on Second Monday of Every October.
We will celebrate thanksgiving in PHD style, by sharing a wacky formula tip.
Today, we are going to learn how to use excel formulas to find out thanksgiving day’s date for any year. Now, if only turkeys could use excel, they would be running for cover.
The formula:
Assuming cell A1 has the year, the formula to find US thanksgiving day’s date is,
To find Canadian thanksgiving date,
How does this formula work?
It is fetching the fourth Thursday of November by finding out what day of week November first is and then adding sufficient number of days to it. For eg. November First, 2009 is a Sunday, so thanksgiving day will be on 26th.
Happy thanksgiving everybody
I am in Denmark now, and there is no concept of Thanksgiving day here. But we don’t need a holiday to be thankful for all the wonderful things we have in life. I am thankful to have a loving wife and 2 wonderful kids and 6919 PHD members. Thank you.
PS: Watch out for a thanksgiving sale announcement on PHD in the next 3 hours.
23 Responses to “Findout Thanksgiving Day’s Date for Any Year [Excel Formulas]”
[...] his blog, Chandoo, at Pointy Haired Dilbert, shows how to calculate Thanksgiving dates, although it might be a bit late to figure out when Thanksgiving is this [...]
Here is a slightly shorter way to calculate Thanksgiving Day (US and Canada) that uses one less function call...
US: =DATE(A1,11,29)-WEEKDAY(DATE(A1,11,3))
Canada: =DATE(A1,10,15)-WEEKDAY(DATE(A1,10,6))
The generic version of these formulas (which I first saw posted online by Peo Sjoblom, although I don't know if it is original with him or not), which can be used to find the Nth such and such day of a given month for a given year, looks like this...
=DATE(Year,Month,1+7*NthDay)-WEEKDAY(DATE(Year,Month,8-DayOfWeek))
So, just plug in the year, month, NthDay and DayOfWeek (1 for Sunday, 2 for Monday, etc.) and perform the indicated math. As an example, for this year's US Thanksgiving...
Year = 2009
Month = 11
DayOfWeek = 5 (for Thursday)
NthDay = 4 (for 4th Thurday in the month)
which gives...
=DATE(2009,11,1+7*4)-WEEKDAY(DATE(2009,11,8-5)
which reduces to the formula I indicated earlier.
@Rick.. Welcome to PHD, I just saw your comment on Deb's blog and was about to link to it here. Good thing you have posted the same stuff here.
I liked the formula very much. It is generic and pretty small compared to the version above. Thank you for sharing it with us.
If your using these formulae to figure out stat holidays, remember that you need to offset the dates if they come out on a weekend. I use the following formula to check the weekday, and add one day to a Sunday or two days to a Saturday:
=IF(WEEKDAY(date)=7,date+2,IF(WEEKDAY(date)=1,date+1,date))
Along the same lines, I came across this formula a while ago to find the date for Good Friday:
=FLOOR("5/"&DAY(MINUTE(YEAR/38)/2+56)&"/"&YEAR,7)-36
Not the most intuitive, but it's worked for me so far!
......oh, but for Boxing Day, you need to account for that fact that if Christmas Day was the Saturday, you would get the Monday and Tuesday as stat holidays, so the formula would be this:
=IF(OR(WEEKDAY(date)=7,WEEKDAY(date)=1),date+2,date)
Andy... your "advance weekend dates to Monday" formula...
=IF(WEEKDAY(date)=7,date+2,IF(WEEKDAY(date)=1,date+1,date))
can be shortened both in length and in number of function calls used...
=Date+(2-MOD(Date,7))*(MOD(Date,7)<2)
Fantastic, I was hoping someone would come up with something like that. Thanks!
Boxing Day still throws up an issue though, in a year when Christmas Day falls on a Sunday. In this instance, the stat holidays would be Monday (for Christmas Day), and Tuesday (for Boxing Day). Is there a way to allow for this without returning to:
=IF(OR(WEEKDAY(date)=7,WEEKDAY(date)=1),date+2,if(WEEKDAY(date)=2,date+1,date))
@Andy & Rick ... I assume you are using the "advance weekend dates to monday" in the context of finding the next working day, in which case, the formula =WORKDAY(date,1) is good enough. It gives you next working day's date no matter what.
Chandoo... true, but only if you have the Analysis ToolPak add-in activated (except in XL2007 where the WORKDAY function is an always activated function). As an aside, why does your Comments "counter" shown under the tip's title show one more comment than there actually are?
@Rick.. true... WORKDAY requires the analysis toolpak. Considering the fact that most users have it anyway, I am sure it is an easy way than writing date manipulation formulas (which are more difficult than traditional arithmetic or lookup formulas)
Also, my comment counter include trackbacks / pingbacks as well - there is a pingback from deb's site to this article...
@Chandoo: thanks for that, I'm really not sure how I missed that function! However, it still needs to only advance the date if it falls on a weekend. So putting the WORKDAY function within an IF as follows seems to work:
=IF(OR(WEEKDAY(date)=1,WEEKDAY(date)=7),WORKDAY(date,1),date)
Again, a modification needs to be made for Boxing Day, but this is now simpler as the WORKDAY function can be defined to exclude specific dates (ie Christmas Day). So this can be amended as follows:
=IF(OR(WEEKDAY(date)=1,WEEKDAY(date)=7,WEEKDAY(date)=2),WORKDAY(date,1,date-1),date)
Chandoo... The only reason I mentioned the Analysis ToolPak is because in my volunteering efforts in the online newsgroups, I see quite a lot of people who do not have this add-in activated, more than I think you are imagining (they usually write back asking about the error they are getting when an Analysis ToolPak function is used in a posted answer to their question). If a worksheet using your WORKDAY solution is given to one of these types of users, then they may not know what to do with the error they see. As XL2007 and XL2010 take hold more and more, this problem will diminish to almost nothing; but, for now, I think it needs to be acknowledged.
Andy... I don't think I understand the rules governing Boxer Day sufficiently to address your question. I tried looking it up online, but can't find an explanation that satisfies my lack of understanding. I realize that if December 26th falls on a Tuesday, Wednesday, Thursday or Friday, the stat holiday is observed on that day... can you list what day it is observed on when December 26th falls on Saturday, Sunday or Monday?
Rick: When Dec 26th is a Saturday, the stat holiday is Monday (28th). When a Sunday, it will be Tuesday (28th) (due to the Christmas Day stat holiday moving from the Saturday to the Monday). When a Monday, it will be Tuesday (27th) (due to Christmas Day stat moving from Sunday to Monday). Any other days of the week it will be the normal day.
Ok, so a correction on my part: you don't need to specify Christmas Day for the Boxing Day formula. It should be as follows:
=IF(OR(WEEKDAY(date)=1,WEEKDAY(date)=2,WEEKDAY(date)=7),WORKDAY(date,1),date)
@Andy... I think this formula will produce the same results as your last posted Boxer Day formula...
=WORKDAY(date,--(MOD(date,7)<3))
although I'm not sure why we are using "date" as an argument with that date will always be December 26th of the given year (right?).
@Andy... there is an "error" in the formula the way it got posted... there should be *two* minus signs (I am pretty sure I typed two of them) in front of the expression containing the MOD function, not just the one minus sign that got posted.
Chandoo,
I owuld like to generate a list of saturdays, sundays, US holidays, and non-working days in EXCEL.
Can u help write me a formula statring 1/1/2011 for next 40 years please?
Thx.
John
[...] [...]
[...] Thanksgiving day is celebrated on 4th Thursday in November, every year. See how to calculate Thanksgiving day using excel formulas. [...]
chandoo,
This is my first posting in your blog. Please see my own formula to find out date of the 4th Thursday of November of the given year.
DATE(A1,11,(5-WEEKDAY(DATE(A1,11,1),1))+22)
[...] focused and specific to one day (Thursday) of one week (the 4th) in one month (November). Albeit a link in that article showed another formula solution to Canada’s Thanksgiving day that takes place [...]
Thanks That's Great