Find Thanksgiving...

[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

 

[hrlngrv - ExcelForums.com]

Juan Sanchez wrote..
..

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

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

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})

 

[Juan Sanchez]

Thanks Harlan, should have think of that... BTW I found
why the other one didn't work...

Thnaks

Juan

 

[Daniel.M]

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.

 

[Harlan Grove]

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

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

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.

 

[Dana DeLouis]

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

 

[Daniel.M]

Hi Dana,

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

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.

 

[Sandy Mann]

Daniel,

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

Interesting, may I ask why?

Regards,

Sandy

 

[Myrna Larson]

I'm interested in the answer to "why?" also.

Daniel,


Interesting, may I ask why?

Regards,

Sandy

 

[Juan Sanchez]

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

Cheers
Juan