Find Thanksgiving...

Juan Sanchez

unread,

Sep 10, 2004, 5:41:16 PM9/10/04

to

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

unread,

Sep 10, 2004, 5:56:25 PM9/10/04

to

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 in
November, so it can only fall between 22-Nov and 28-Nov, inclusive.
So

=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}))
---------
Message sent via www.excelforums.com

Juan Sanchez

unread,

Sep 10, 2004, 6:15:23 PM9/10/04

to

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

Thnaks

Juan

>.
>

Daniel.M

unread,

Sep 10, 2004, 6:28:19 PM9/10/04

to

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.

"hrlngrv - ExcelForums.com" <hrl...@aol-dot-com.no-spam.invalid> wrote in
message news:%23zOSFE4...@TK2MSFTNGP12.phx.gbl...
> Juan Sanchez wrote...

Harlan Grove

unread,

Sep 10, 2004, 11:58:11 PM9/10/04

to

"Daniel.M" <prenom...@bigfoot.inutil.com> wrote...

>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

unread,

Sep 11, 2004, 4:55:33 PM9/11/04

to

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

"hrlngrv - ExcelForums.com" <hrl...@aol-dot-com.no-spam.invalid> wrote in
message news:%23zOSFE4...@TK2MSFTNGP12.phx.gbl...
> Juan Sanchez wrote...
> ..

Daniel.M

unread,

Sep 11, 2004, 5:52:03 PM9/11/04

to

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

unread,

Sep 11, 2004, 6:11:42 PM9/11/04

to

Daniel,

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

Interesting, may I ask why?

Regards,

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk

"Daniel.M" <prenom...@bigfoot.inutil.com> wrote in message
news:OUM80lEm...@TK2MSFTNGP14.phx.gbl...

Myrna Larson

unread,

Sep 12, 2004, 2:52:00 AM9/12/04

to

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

On Sat, 11 Sep 2004 23:11:42 +0100, "Sandy Mann" <sand...@mailinator.com>
wrote:

Juan Sanchez

unread,

Sep 13, 2004, 12:51:06 PM9/13/04

to

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

Cheers
Juan

>.
>