Excel 2010: Exlanation of formula for Thanksgiving date of current year 2

[dcompto]

I found a formula for determining the date of Thanksgiving for the current year from

http://chandoo.org/wp/2009/11/25/findout-thanksgiving-date/,

and it works great. However, I do not understand the explanation of the ",26,25,24,23,22,28,27".

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.

Can someone explain it to me so I can understand that part of the formula?

My sheet has the year as =TEXT(C5, "yyyy") in D5.

=DATE(D5,11,CHOOSE(WEEKDAY(DATE(D5,11,1)),26,25,24,23,22,28,27))

Appreciate any help.

 

[SkipVought]

My sheet has The year as..."

Wrong! What you have is a string of 4 digits that represent the year. The year is YEAR(C5).

You can't do arithmetic with strings (text)!

 

[SkipVought]

all the possible dates in the fourth week are listed.

 

[dcompto]

Yes, I'm sure I'm technically wrong in my phrasing; however, D5 is the cell reference in the formula:

=DATE(D5,11,CHOOSE(WEEKDAY(DATE(D5,11,1)),26,25,24,23,22,28,27))

and D5's formula is =TEXT(C5, "yyyy").

Maybe, that's irrelevant; I don't know. I'm just hoping someone can explain the ... ,26,25,24,23,22,28,27 part which, judging by the parentheticals, seems to be part of the CHOOSE command.(???)

Please forgive my ignorance--I'm a lowly Word Processing Technician who tries to accomplish things beyond my skill set, and, by golly(!), I often do(!!)--albeit with the grace and patience of experts who are kind enough to answer my bumbling questions.

 

[SkipVought]

The DATE() function wants three NUMBERS, not TEXT!

Who put the text function in D5? It needs NUMBER!

Hence...

=YEAR(C5)

 

[SkipVought]

When the first falls on Sunday, Thanksgiving falls on the 22nd. And so on.

 

[mintjulep]

If November 1 is Sunday then turkey on the 26th.
If November 1 is Monday then turkey on the 25th.
Etc.

 

[SkipVought]

Mint, you are so right, I Bleu it! How cheesie was THAT?

 

[mintjulep]

Turkey and cheese.

Yum!

 

[bluedragon2]

To give you a breakdown of how the formula works:

Main function is DATE() which gives output of an excel date by giving it the year, month, and day as criteria.
D5 is the year.
11 is the month.
Now, for the day: The weekday() function will return a number for the day of week a date falls on. So, weekday(d5,11,1) is asking for what day November 1 falls on and will return a 1 through 7 for Sunday through Saturday. The CHOOSE() function will return a value from a list based on an index. So the weekday value 1-7 is the index for the list of 26,25,24,23,22,28,27. If the index is 1 then the value 26 is returned, if it is 4 then 23 is returned. This will give the day portion of the main DATE() function.

Since Thanksgiving will fall on one of the days in the list depending on what day the 1st of November falls on, the function is just a date function with a determination of day.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[zelgar]

Another formula that would work, using the same logic but skipping the Choose command

=DATE(D5,11,29 -(WEEKDAY(DATE(D5,11,31))))

 

[dcompto]

bluedragon2, thank you for the detailed explanation. It was very helpful.

zelgar, thank you, too, for your response. Your formula works fine, but I don't understand how you determined to use the numbers 29 and 31. ??

 

[zelgar]

Actually, I cheated. Instead of looking at the date of the beginning of the month, I looked at the end since it would be a lot closer to the date. Now that I look at the formula, it's in error even though it's calculating everything correctly. THERE ISN'T A NOVEMBER 31st IN ANY YEAR

=DATE(D5,11,29 -(WEEKDAY(DATE(D5,12,1))))

I thought using the last day of November and subtracting it from some arbitrary number, I'd get it right. It's a good thing I didn't start out with November 30th, because you can't get the right date every year; but it does work with the December 1st. Just goes to show you that sometimes dumb luck works whereas actually getting things right gives you the wrong answer