It is Thanksgiving day weekend for our friends & readers in USA. That means a good portion of our readers are relaxing with their family and having a good meal. Naturally, we celebrated thanksgiving at our home (we pretty much celebrate any festival as long as eating good food is one of its rituals). I am too tempted to leave the blog un-updated until Tuesday next week, but then I remembered about remaining good portion of our readers, that is you.
So, here is a simple quiz for you.
Find in which year thanksgiving day occurs on the same date again
using Excel Formulas, of course.
See this short demo to understand what I have in mind:
Thanksgiving day is celebrated on 4th Thursday in November, every year. See how to calculate Thanksgiving day using excel formulas.
Go ahead and find a solution, then post it using comments.
Related Awesomeness: Calculating Common Public Holiday Dates in Excel | Perpetual Calendar – Excel Template
29 Responses to “Homework – When does Thanksgiving Day occur on same date again?”
Assume the year is input in B1:
to find the day that year:
{=LARGE(((WEEKDAY(DATE(B$1,11,ROW(1:30)))=5)*ROW(1:30)),SUM((--(WEEKDAY(DATE(B$1,11,ROW(1:30)))=5)))-3)}
Assume the day is in B2:
to find the next year
{=B1+MATCH(5,WEEKDAY(DATE(ROW(INDIRECT(B1+1&":"&B1+20)),11,B2)),0)}
Explanation:
WEEKDAY(DATE(B$1,11,ROW(1:30))
returns an array the serial numbers of all the days in november (1 to 30) for the input year.
The sum formula counts how many thursdays occur in november for that year. I need to find the 4th, so when there's 4that year I want to find the largest, but if there's 5 I need the second largest (Thus -3)
the large finds the required day of the month.
The second formula sets up an array of the serial numbers for the day of thanksgiving this year for the next 20 years
WEEKDAY(DATE(ROW(INDIRECT(B1+1&":"&B1+20)),11,B2))
Then, just find (match) thursday (5) in that array and you found the next time it's thanksgiving on that day.
3rd line should read
{=LARGE(((WEEKDAY(DATE(B$1,11,ROW(1:30)))=5)*ROW(1:30)),SUM((--(WEEKDAY(DATE(B$1,11,ROW(1:30)))=5)))-3)}
3rd line should read
{=LARGE(((WEEKDAY(DATE(B$1,11,ROW(1:30)))=5)*ROW(1:30)),
SUM((--(WEEKDAY(DATE(B$1,11,ROW(1:30)))=5)))-3)}
My solution:
http://cid-6b219f16da7128e3.office.live.com/view.aspx/Thanksgiving%20Day.xlsx
Input the year in B1 cell and do this:
C1=B1+1
Follow this C1 cell until N1.
B2=DATE(B1,11,CHOOSE(WEEKDAY(DATE(B1,11,1)),26,25,24,23,22,28,27))
Follow this B2 cell until N2.
C3=IF(B3="",IF(DAY($B$2)=DAY(C2),C2,""),B3)
Follow this C3 cell until N3.
Result:
C4=YEAR(N3)
I did it with arrays. I tried to pull it off without helper cells, but I couldn't pull it off. If I were sharing it, I would hide all the arrays, but I think my methodology is sound. Here is the doc:
https://docs.google.com/leaf?id=0B9kgqSZwNoWJZWFlYjc5Y2YtMzliMi00ZDEwLThhOTUtOTMyMTIyMDViOTcx&hl=en&authkey=COzQzIwF
The formulae:
Year Array beginning in Cell A8
Thanksgiving Array in B8
=DATE(ROW(INDIRECT($B$1&":"&$B$1+ROW())),11,29)-WEEKDAY(DATE(ROW(INDIRECT($B$1&":"&$B$1+ROW())),11,3))
Day calculation beginning in Cell C8
Offset to find the next matching Thanksgiving day
=OFFSET(B8,MATCH(C8,C9:$C$57,0),)
Then a custom YYYY format. That is all.
I will be curious to find out how Chandoo pulled it off and if he did it without helpers. Thanks and keep up the great work, Chandoo!
-MP
Year
B2=2010
.
Thanksgiving date
B3=DATE(B2,11,29)-WEEKDAY(DATE(B2,11,3))
.
Next year Thanksgiving occurs on same day
B4=B2+MATCH(DAY(B3),DAY(DATE(ROW(INDEX(A:A,B2+1):INDEX(A:A,B2+100)),11,29)-WEEKDAY(DATE(ROW(INDEX(A:A,B2+1):INDEX(A:A,B2+100)),11,3))),0).
Confirm with Ctrl+Shift+Enter
.
Regrads
That is brilliant, Elias.
Assuming B2 holds the Year value, with Excel 2010 you can use the new return_type argument for WEEKDAY to produce the following formula for Thanksgiving Day:
=DATE(B2,11,29-WEEKDAY(DATE(B2,11,1),15))
where the return_type argument of 15 is giving WEEKDAY values of 1 (Friday) through 7 (Thursday).
But then you realize that shifting the date will give you the solution provided by @Elias, which is also backward compatible.
Year in B1 and B2:
=DATE(B1,11,CHOOSE(WEEKDAY(DATE(B1,11,1)),26,25,24,23,22,28,27))
Array formula in B3:
=SMALL(IF(DAY(B2)=DAY(
DATE(B1+ROW(A1:A12),11,
CHOOSE(WEEKDAY(DATE(B1+ROW(A1:A12),11,1)),
26,25,24,23,22,28,27))),
B1+ROW(A1:A12),10000),1)
Confirm with Ctrl+Shift+Enter
Best regards.
@Pedro,
You can shorten your formula by 11 characters by using the MIN function instead of the SMALL function, using ROW(1:12) instead of ROW(A1:A12) and using 9999 instead of 10000 for the false IF condition (I think that is a safe thing to do)...
=MIN(IF(DAY(B2)=DAY(DATE(B1+ROW(1:12),11,CHOOSE(WEEKDAY(DATE(B1+ROW(1:12),11,1)),26,25,24,23,22,28,27))),B1+ROW(1:12),9999))
Now that I have shortened Pedro's formula so much, the following array-entered** formula, at 6 characters more, seems lengthy by comparison (but I am posting it anyway because is shows yet another way to solve this problem)...
B2: The Year
B3: =DATE(B2,11,29)-WEEKDAY(DATE(B2,11,3))
B4: =B2+MIN(99*(DAY(DATE(B2,11,29)-WEEKDAY(DATE(B2,11,3)))DAY(DATE(B2+ROW(1:99),11,29)-WEEKDAY(DATE(B2+ROW(1:99),11,3))))+ROW(1:99))
**commit formula using CTRL+SHIFT+ENTER, not just Enter by itself
Pedro and Rick formulas are really nice, but they could return a wrong result if you insert a row before row 1 after the formula is entered. The reason is because ROW(1:12) will change to ROW(2:13) and ROW(1:99) to ROW(2:100).
.
Regards
The "not equal" symbol for my posted formula was removed by this blog's comment processor. Here is my array-entered** formula again with .NE. (notice the surrounding dots) placed where the "not equal" symbol should be (just replace it with a "less than" symbol followed by a "greater than" symbol)...
=B2+MIN(99*(DAY(DATE(B2,11,29)-WEEKDAY(DATE(B2,11,3))).NE.DAY(DATE(B2+ROW(1:99),11,29)-WEEKDAY(DATE(B2+ROW(1:99),11,3))))+ROW(1:99))
@Elias,
Row 1 is the header row... no one inserts rows before that.{grin} All kidding aside, that is an excellent point and should definitely be mentioned... so thanks for doing so. I would note, though, that since the minimum date-repeat-time is 5 years, Pedro and my formulas are safe for up to 4 row insertions before they will begin to produce incorrect results. Assuming one needs to protect against insertion before Row 1, here is a fix for my formula which handles the problem (I could not get this same fix to work with Pedro's formula though)...
=B2+MIN(99*(DAY(DATE(B2,11,29)-WEEKDAY(DATE(B2,11,3))).NE.DAY(DATE(B2+ROW(INDIRECT("1:99")),11,29)-WEEKDAY(DATE(B2+ROW(INDIRECT("1:99")),11,3))))+ROW(INDIRECT("1:99")))
**commit formula using CTRL+SHIFT+ENTER, not just Enter by itself
NOTE: As indicate in my previous message, this blog's comment processor removes "not equal" symbols ("less then" symbol followed by "greater then" symbol), so I used .NE. (notice the surrounding dots) in place of the "not equal" symbol... just replace those characters with a "less then" symbol followed by a "greater then" symbol when trying to copy the formula. I think the quote marks used in my formula will have to be replaced by *real* quote marks after you copy the formula as well.
My formula is similar to Elias and few others above.
The years is in cell C2
Thanksgiving date is in C3 =DATE(C2,11, CHOOSE(WEEKDAY(DATE(C2,11,1)), 26,25,24,23,22,28,27))
Then, I defined a named formula lstYears as =ROW(OFFSET(Sheet2!$A$1,,,30,))+Sheet2!$C$2
This gives a bunch of 30 year numbers from the value in C2.
Finally, the array formula to find the next year with same thanksgiving date is,
=INDEX(lstYears, MATCH(TRUE, DAY(C5)=CHOOSE(WEEKDAY(DATE(lstYears,11,1)), 26,25,24,23,22,28,27) ,0))
(Make sure you array enter it with CTRL+SHIFT+Enter)
@Elias, Rick & Pedro: a technique like ROW(OFFSET(Sheet2!$A$1,,,30,)) will overcome the limitation of row(1:99)
Thanks to Elias, Rick and Chandoo by your array formulas.
Only one observation, the following year with the same day occurs 12 years later at worst case. I've used 12 items, why do you use arrays of 30, 99 or 100 items?
Out of this weekend homework, now here it is my solution without arrays:
=$B$2+IF(AND(MID($B$2,3,1)="9",MOD(INT($B$2/100)-19,4)0),CHOOSE(MID($B$2,4,1)+1,12,5,6,6,6,6,7,12,6,6),
CHOOSE(MOD($B$2,4)+1,6,6,11,5))
Just Enter by itself.
Not equal symbol by this blog’s comment processor.
My last formula with .NE. (“not equal” symbol should be replaced with a “less than” symbol followed by a “greater than” symbol)
=$B$2+IF(AND(MID($B$2,3,1)="9",MOD(INT($B$2/100)-19,4).NE.0),
CHOOSE(MID($B$2,4,1)+1,12,5,6,6,6,6,7,12,6,6),
CHOOSE(MOD($B$2,4)+1,6,6,11,5))
It runs from 1900 til 9999.
My last formula with pre and code HTML tags (between open and close tags)
=$B$2+IF(AND(MID($B$2,3,1)="9",MOD(INT($B$2/100)-19,4)0),
CHOOSE(MID($B$2,4,1)+1,12,5,6,6,6,6,7,12,6,6),
CHOOSE(MOD($B$2,4)+1,6,6,11,5))
It works?
@Chandoo,
Unfortunately the issue persists. Look what happened with you result when you insert more than 10 rows on Row 1, or when you delete Row 1.
Regards
@Pedro,
>> =$B$2+IF(AND(MID($B$2,3,1)="9",
>> MOD(INT($B$2/100)-19,4).NE.0),
>> CHOOSE(MID($B$2,4,1)+1,12,5,6,6,6,6,7,12,6,6),
>> CHOOSE(MOD($B$2,4)+1,6,6,11,5))
That is it definitely the formula to use! I would consider changing the absolute cell references to relative ones so the formula could be copied down or across though. In any event, you demonstrated some excellent pattern recognition skills there Pedro... well done!!!
Just for the first part of the task, how about finding the date of first Thursday in November for the particular year and then add 21 day to get the date of the 4th Thursday?
Year is entered in A1 and there is a Defined Name "FirstNovember" =DATE(Sheet1!$A$1;11;1) just to be more clear. In my formula Thursday is 4th day in a week (parameter 2 as a second argument in WEEKDAY function).
Formula is:
=IF(WEEKDAY(FirstNovember;2).EQ.4;DATE(A1;11;1+21);IF(WEEKDAY(FirstNovember;2).GT.4;DATE(A1;11;7-WEEKDAY(FirstNovember;2)+4+1+21);DATE(A1;11;4-WEEKDAY(FirstNovember;2)+1+21)))
@Pedro,
I agree with Rick, that's a great formula.
Regards
@ Pedro Wave
... http://en.wikipedia.org/wiki/Century_leap_year
Your assumption of a leapyear if divisible by 4 is not correct
[MOD(INT($B$2/100)-19,4).NE.0)]
@Drazen, based on your formula is possible to obtain this one:
=DATE(A1;11;22+IF(FNWD2=4;0;4+IF(FNWD2>4;7;0)-FNWD2))
where FNWD2 is defined as:
=WEEKDAY(DATE(A1;11;1);2)
Changing ; separator by , and defining FNWD1 as:
=WEEKDAY(DATE(A1,11,1))
the new formula is:
=DATE(A1,11,22+IF(FNWD1=5,0,5+IF(FNWD1>5,7,0)-FNWD1))
Similar to @Chandoo US Thanksgiving Day simplified:
=DATE(A1,11,22+CHOOSE(FNWD1,4,3,2,1,0,6,5))
Note: FNWD = FirtsNovemberWeekDay
@ikkeman, I don't assume leap years.
Next formula:
AND(MID($B$2,3,1)="9",MOD(INT($B$2/100)-19,4).NE.0)
is TRUE when year =
199u+400*n (u=0..9; n=0..20)
.
My formula simplified:
=B2+IF(AND(MID(B2,3,1)="9";MOD(INT(B2/100)-19,4).NE.0),
CHOOSE(MID(B2,4,1)+1,7,0,1,1,1,1,2,7,1,1),
CHOOSE(MOD(B2,4)+1,1,1,6,0))+5
@Rick and @Elias, thank you very much for your kind comments.
@ikkeman, excuse me, change TRUE by FALSE in my last comment.
@ Pedro,
Mea culpa, I used "<=" rather than ""
("less than, equal to" rather than "less than, greater than")
it works beautifully.
@ikkeman, I made a mistake in my explanation.
I meant that the formula is TRUE for all nineties except when year =
199u+400*n (u=0..9; n=0..20)
The pattern key is:
For nineties as (2090, 2190, 2290, 2490, 2590, 2690, 2890 and so on)'s, u=0..9:
CHOOSE(u+1,7,0,1,1,1,1,2,7,1,1)
For other years:
CHOOSE(MOD(B2,4)+1,1,1,6,0)
Add the result to B2+5 and that's it.
hi
thank you my dear