Feb 04 2020 08:04 PM
I am looking for 1 formula that can do the following;
If cell has a date stamp that falls on the following days then it would equal to
Monday = Previous Tuesday (or 6 days ago)
Tuesday = Previous Tuesday (7 days ago)
Wednesday = 2 Tuesdays back (8 days ago)
Thursday = 2 Tuesdays back (9 days ago)
Friday = Previous Tuesday (or 3 days ago)
Saturday = Previous Tuesday (4 days ago)
Sunday = Previous Tuesday (5 days ago)
Currently using the following:
=$I$27-WEEKDAY($I$27+4)-(7*(WEEKDAY($I$27)=5))
...which is not working for the Wednesdays & Thursdays. Can someone help with what needs amending to make the above work for me?
Feb 04 2020 08:18 PM
If your date is in cell B6, this formula seems to work:
=CHOOSE(WEEKDAY(B6),-5,-6,-7,-8,-9,-3,-4)+B6