Finding the date of the previous Thanksgiving Day

[Bob]

Given any date, I need to find the date of the previous Thanksgiving Day.

For example, if the date in cell A1 is 9/28/2009, then the date of the
previous Thanksgiving Day in cell B1 would be 11/27/2008. However, if the
date in cell A1 is 12/5/2009, then the date of the previous Thanksgiving Day
in cell B1 would obviously be 11/26/2009.

I know how to find the date of the current year’s Thanksgiving Day using
=FLOOR(DATE(YEAR(TODAY()),11,29),7)-2. But finding the previous Thanksgiving
Day has been elusive.

Any help would be greatly appreciated.

Thanks,
Bob

 

[T. Valko]

What if the date in A1 *is* the date for Thanksgiving? Do you still want the
previous Thanksgiving date? That's what this formula will do.

A1 = some date

=FLOOR(DATE(YEAR(A1)-(A1<=FLOOR(DATE(YEAR(A1),11,29),7)-2),11,29),7)-2

 

[T. Valko]

Disregard that formula.

I did some further testing and discovered your formula for the Thanksgiving
date returns incorrect results.

I know how to find the date of the current year's Thanksgiving Day using
=FLOOR(DATE(YEAR(TODAY()),11,29),7)-2

For example...

Thanksgiving 2019 is 11/28/2019. That formula returns 11/21/2019

Let me see what I can come up with.

 

[T. Valko]

Ok, try this. Kind of long but it works.

A1 = some date

=DATE(YEAR(A1)-(A1<=DATE(YEAR(A1),11,29)-WEEKDAY(DATE(YEAR(A1),11,3))),11,29)-WEEKDAY(DATE(YEAR(A1)-(A1<=DATE(YEAR(A1),11,29)-WEEKDAY(DATE(YEAR(A1),11,3))),11,3))

Here's a generic formula for the Thanksgiving date:

=DATE(year,11,29)-WEEKDAY(DATE(year,11,3))

 

[Teethless mama]

Create define name range:
TGDthisyear =DATE(YEAR(A1),11,29)-WEEKDAY(DATE(YEAR(A1),11,3))
TGDlastyear =DATE(YEAR(A1)-1,11,29)-WEEKDAY(DATE(YEAR(A1)-1,11,3))

In B1: =IF(A1>=TGDthisyear,TGDthisyear,TGDlastyear)

 

[Bob]

Thanks for your solution! It works great!

Although I had tested my original Thanksgiving formula with years 2008-2012,
I didn't go beyond that (and in hindsight I should have).

Thanks again for all your help,
Bob

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Ron Rosenfeld]

Given any date, I need to find the date of the previous Thanksgiving Day.

For example, if the date in cell A1 is 9/28/2009, then the date of the
previous Thanksgiving Day in cell B1 would be 11/27/2008. However, if the
date in cell A1 is 12/5/2009, then the date of the previous Thanksgiving Day
in cell B1 would obviously be 11/26/2009.

I know how to find the date of the current year’s Thanksgiving Day using
=FLOOR(DATE(YEAR(TODAY()),11,29),7)-2. But finding the previous Thanksgiving
Day has been elusive.

Any help would be greatly appreciated.

Thanks,
Bob

=IF(A1>DATE(YEAR(A1),11,29)-WEEKDAY(DATE(YEAR(A1),11,3)),
DATE(YEAR(A1),11,29)-WEEKDAY(DATE(YEAR(A1),11,3)),
DATE(YEAR(A1)-1,11,29)-WEEKDAY(DATE(YEAR(A1)-1,11,3)))

--ron

 

[Bob]

Thanks for your interesting solution! Although my original question stated
that the source date resides in cell A1, in reality, I have several source
dates residing in multiple discontinuous cells. Can you please show me how
to modify your solution so that it will work with all these target dates?

Thanks again,
Bob

 

[Bob]

Ron,

Thanks for your help! It appears that your solution is a variation of the
one given by Teethless mama, except that it can work with a target date
located in any cell.

Thanks again,
Bob

 

[Bob]

I think Ron's solution may have already answered my question. Do you agree?

Thanks again,
Bob

 

[Ron Rosenfeld]

Ron,

Thanks for your help! It appears that your solution is a variation of the
one given by Teethless mama, except that it can work with a target date
located in any cell.

Thanks again,
Bob

You're welcome. Glad to help.

And yes it can work with any cell (just change A1). But I didn't see TM's
submission, so cannot comment on that.
--ron