Fri six months ago from today

...if today is not a Friday.. do you want the date of the Friday that would fall next? Previous?... o_O

Gives you the next Friday, 6 month case:


=6-WEEKDAY((MONTH(TODAY())-6<=0)*DATE(YEAR(TODAY())-1,18-MONTH(TODAY()),DAY(TODAY()))+(MONTH(TODAY())-6>0)*(DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY()))))+(MONTH(TODAY())-6<=0)*DATE(YEAR(TODAY())-1,18-MONTH(TODAY()),DAY(TODAY()))+(MONTH(TODAY())-6>0)*(DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())))


Next Friday, 1 year ago:

=6-WEEKDAY(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))+DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))

k, updated the previous posting (didn't see another reply when I finished the answer).

Basic Idea:

6 = the weekday for Friday.

So, look for the value of: 6 - Weekday(Date(six months ago)) + Date(six months ago)

If six months ago was Friday then the first 2 terms cancel 6 - Weekday(Date(six months ago)) = 6 - 6 = 0 and you have Date(six months ago)

If it was not a Friday on the nose, (assume it was a Thursday), then you get: 6 - 5 + Date(six months ago) OR 1+ Date(six months ago).


EDIT -- Excel has an EDATE() function! Damn... I always reinvent the wheel thank you for that info teethless mama (shouldn't that be "toothless"??)

Just make sure the cell is formatted how you want the date to appear, otherwise you'll get the serial number for the date.