Finding the date of the previous Thanksgiving Day
 

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 years 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

Finding the date of the previous Thanksgiving Day
 

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

--
Biff
Microsoft Excel MVP


"Bob" wrote in message
...
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

Finding the date of the previous Thanksgiving Day
 

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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

--
Biff
Microsoft Excel MVP


"Bob" wrote in message
...
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

Finding the date of the previous Thanksgiving Day
 

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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

--
Biff
Microsoft Excel MVP


"Bob" wrote in message
...
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

Finding the date of the previous Thanksgiving Day
 

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" wrote:

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 years 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

Finding the date of the previous Thanksgiving Day
 

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" wrote:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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

--
Biff
Microsoft Excel MVP


"Bob" wrote in message
...
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

Finding the date of the previous Thanksgiving Day
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Bob" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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

--
Biff
Microsoft Excel MVP


"Bob" wrote in message
...
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

Finding the date of the previous Thanksgiving Day
 

On Mon, 28 Sep 2009 18:56:07 -0700, Bob wrote:

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(A1DATE(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

Finding the date of the previous Thanksgiving Day
 

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


"Teethless mama" wrote:

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" wrote:

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 years 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

Finding the date of the previous Thanksgiving Day
 

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 Rosenfeld" wrote:

On Mon, 28 Sep 2009 18:56:07 -0700, Bob wrote:

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 years 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(A1DATE(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

Finding the date of the previous Thanksgiving Day
 

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

Thanks again,
Bob


"Teethless mama" wrote:

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" wrote:

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 years 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

Finding the date of the previous Thanksgiving Day
 

On Wed, 30 Sep 2009 02:47:01 -0700, Bob wrote:

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