Find Thanksgiving...

  • Thread starter Thread starter Juan Sanchez
  • Start date Start date
J

Juan Sanchez

Hi all, can someone tell me why this formula is not
working?, I get a #Num! result

=SMALL(IF(WEEKDAY(DATE(YEAR(TODAY()),11,ROW(INDIRECT
("1:"&ROW(A1:A30)))))=5,DATE(YEAR(TODAY()),11,ROW(INDIRECT
("1:"&ROW(A1:A30)))),""),4)

Its supposed to find Thanksgiving date for the current
year...

Is this an elaborated solition? is there an easyer way?...

Regards
Juan
 
Juan Sanchez wrote..
..
Its supposed to find Thanksgiving date for the current year..
Click to expand...
Is this an elaborated solition? is there an easyer way?..
Click to expand...

Yes, there's an easier way. Thanksgiving is always the 4th Thurdsay i
November, so it can only fall between 22-Nov and 28-Nov, inclusive
S

=LOOKUP(2,1/(WEEKDAY(DATE(YEAR(NOW()),11
22+{0,1,2,3,4,5,6}))=5),DATE(YEAR(NOW()),11,22+{0,1,2,3,4,5,6})
 
Thanks Harlan, should have think of that... BTW I found
why the other one didn't work...

Thnaks

Juan
 
Hi,

Or this one (which is another way of saying Thursday before Nov 29):

=DATE(YEAR(TODAY()),11,29)-WEEKDAY(DATE(YEAR(TODAY()),11,24))

Regards,

Daniel M.
 
Daniel.M said:
Or this one (which is another way of saying Thursday before Nov 29):

=DATE(YEAR(TODAY()),11,29)-WEEKDAY(DATE(YEAR(TODAY()),11,24))
Click to expand...

Svelte brute force is still brute force. Elegance is so much nicer.

I had a feeling you'd come up with something better. I'll just point out
that 24 = 29 - target WEEKDAY result.
 
This is not as nice, but another option to starting on the 22nd might be
something like this. This is a little awkward because of the size
limitation to Excel's Mod() function. This is just the day of the month...

=22+MOD(14256750,2*WEEKDAY(DATE(Year,11,22))+5)

Dana DeLouis
 
Hi Dana,
=22+MOD(14256750,2*WEEKDAY(DATE(Year,11,22))+5)
Click to expand...

Interesting. Altough I'm an active opponent to the usage to MOD(WEEKDAY())
pattern in formulas. ;-)

Also, to find the day number:
=29-WEEKDAY(DATE(aYear,11,24))

Regards,

Daniel M.
 
Thanks Daniel, this is such a nice and slick solution...
with more applications than just that one of finding
thanksgiving...

Cheers
Juan
 
Back
Top