| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding a Date 90 days previous to present date | Excel Worksheet Functions | |||
| Finding the previous Saturday, then Friday date | Excel Worksheet Functions | |||
| Finding My previous post | Excel Discussion (Misc queries) | |||
| spreadsheets for thanksgiving dinner routes | New Users to Excel | |||
| Finding my previous questions... | Excel Discussion (Misc queries) | |||
Linear Mode
