How to determine the date?

E

Eric

I would like to know when the last friday is on current month in cell A1, and
the last monday before the last friday on current month in cell B1.
For example, for current month, the last friday is 30 May, and the last
monday before the last friday on current month is 26 May.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric
 
G

Gary''s Student

In cell C1 enter:
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)
first day next month

In cell A1 enter:
=C1-DAY(C1)+8-WEEKDAY(C1-DAY(C1)+2)-7
last Friday this month

In cell B1 enter:
=A1-4
Monday before

To get the last Friday of the current month, we get the first Friday of the
next month and then backoff 7 days. For the previous Monday, backoff 4 more
days.
 
D

David Biddulph

I assume that
=C1-DAY(C1)+8-WEEKDAY(C1-DAY(C1)+2)-7 is the implementation of some more
general formula?

Presumably it can be simplified as the +8 and -7 give +1, and DAY(C1) is 1,
hence
=C1-WEEKDAY(C1+1) ?
 
Top