Search closest day in the past from today
From today "Sunday Feb 3rd", I want to know the closest date for "Friday" in the past. Nothing like "Sunday"-2="Friday". To be clear, I want the formula to be fixed on "Friday's" as the date progresses, so tomorrow "Monday Feb 4th", I also want to know the closest date for "Friday" in the past.
On a further step, I need a 2nd formula for the date of the 2nd "Friday" in the past, ie the Friday before the Friday. Thank you all in advance for the help. Edit: I think I found a solution, but I want to know, if there is a more elegant, less messy way to solve this problem: =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-3-WEEKDAY(TODAY(),3)) source: http://www.excelbanter.com/showthread.php?t=259052 and =IF(TEXT(TODAY(),"DDDD")="Monday",-3,IF(TEXT(TODAY(),"DDDD")="Tuesday",-4,IF(TEXT(TODAY(),"DDDD")="Wednesday",-5,IF(TEXT(TODAY(),"DDDD")="Thursday",-6,IF(TEXT(TODAY(),"DDDD")="Friday",0,IF(TEXT(TODAY (),"DDDD")="Saturday",-1,IF(TEXT(TODAY(),"DDDD")="Sunday",-2))))))) [Note: for the Friday before the Friday I would just increase the subtracted days ie -10,-11,-12,-13,-7,-8 and -9 respectively] combined: =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+IF (TEXT(TODAY(),"DDDD")="Monday",-3,IF(TEXT(TODAY(),"DDDD")="Tuesday",-4,IF(TEXT(TODAY(),"DDDD")="Wednesday",-5,IF(TEXT(TODAY(),"DDDD")="Thursday",-4,IF(TEXT(TODAY(),"DDDD")="Friday",0,IF(TEXT(TODAY (),"DDDD")="Saturday",-1,IF(TEXT(TODAY(),"DDDD")="Sunday",-2)))))))-WEEKDAY(TODAY(),3)) I just noticed something and have a question regarding these 4 options: 1) =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+7-WEEKDAY(TODAY(),3)) 2) =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+7) 3) =TODAY()+CHOOSE(WEEKDAY(TODAY()),1,7,6,5,4,3,2) 4) =today()+7 They all give the same date, has the guy been made fun of with complicated ways of describing a simple thing? And I do not understand how "-WEEKDAY(TODAY(),3)" doesn't affect the date ie has the same outcome as option 2. |
Search closest day in the past from today
On Sun, 3 Feb 2013 22:17:48 +0000, Liquie wrote:
From today "Sunday Feb 3rd", I want to know the closest date for "Friday" in the past. Nothing like "Sunday"-2="Friday". To be clear, I want the formula to be fixed on "Friday's" as the date progresses, so tomorrow "Monday Feb 4th", I also want to know the closest date for "Friday" in the past. With some date in A1, the previous Friday is given by the formula: =A1-WEEKDAY(A1-6) On a further step, I need a 2nd formula for the date of the 2nd "Friday" in the past, ie the Friday before the Friday. Just subtract 7 from above: =A1-WEEKDAY(A1-6) -7 |
Search closest day in the past from today
On Mon, 04 Feb 2013 06:19:38 -0500, Ron Rosenfeld wrote:
On Sun, 3 Feb 2013 22:17:48 +0000, Liquie wrote: From today "Sunday Feb 3rd", I want to know the closest date for "Friday" in the past. Nothing like "Sunday"-2="Friday". To be clear, I want the formula to be fixed on "Friday's" as the date progresses, so tomorrow "Monday Feb 4th", I also want to know the closest date for "Friday" in the past. With some date in A1, the previous Friday is given by the formula: =A1-WEEKDAY(A1-6) On a further step, I need a 2nd formula for the date of the 2nd "Friday" in the past, ie the Friday before the Friday. Just subtract 7 from above: =A1-WEEKDAY(A1-6) -7 Or, if you want to reference only today's date, merely substitute TODAY() for A1 in either of the formulas above e.g: =today()-weekday(today()-6) |
All times are GMT +1. The time now is 04:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com