Finding the date of the previous Thanksgiving Day

  • Thread starter Thread starter Bob
  • Start date Start date
B

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
 
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
 
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
Click to expand...

For example...

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

Let me see what I can come up with.
 
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))
 
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)
 
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
 
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
Click to expand...


=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
 
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
 
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
 
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
Click to expand...

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
 
Back
Top