How to determine the date?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eric - 18 May 2008 04:46 GMT

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

Reply to this Message

Gary''s Student - 18 May 2008 11:03 GMT

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.

Signature

Gary''s Student - gsnu200786

> 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.
[quoted text clipped - 3 lines]
> Thanks in advance for any suggestions
> Eric

Reply to this Message

David Biddulph - 18 May 2008 11:32 GMT

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) ?
--
David Biddulph

> In cell C1 enter:
> =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)
[quoted text clipped - 22 lines]
>> Thanks in advance for any suggestions
>> Eric

Reply to this Message

Gary''s Student - 18 May 2008 12:00 GMT

Thank you David.

Signature

Gary''s Student - gsnu200786

> I assume that
> =C1-DAY(C1)+8-WEEKDAY(C1-DAY(C1)+2)-7 is the implementation of some more
[quoted text clipped - 32 lines]
> >> Thanks in advance for any suggestions
> >> Eric

Reply to this Message