Calculating first/last Monday, Tuesday, etc. in a given month in E

[Guest]

Thanks for the replies to my previous question. I would also like to use
Excel to identify the first and last Monday, Tuesday, etc. of a given month
by date. Thanks.

 

[Guest]

Try something like this:

For
A1: (a date) eg 03/01/2006
A2: (a day to find) eg TUE

First occurrence of A2 in the month containing A1
C1:
=DATE(YEAR(A1),MONTH(A1),1+7)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-MATCH(A2,{"SUN","MON","TUE","WED","THU","FRI","SAT"},0)))

Last occurrence of A2 in the month containing A1
C2:
=DATE(YEAR(A1),MONTH(A1)+1,1+7)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,8-MATCH(A2,{"SUN","MON","TUE","WED","THU","FRI","SAT"},0)))-7

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Ron Rosenfeld]

Thanks for the replies to my previous question. I would also like to use
Excel to identify the first and last Monday, Tuesday, etc. of a given month
by date. Thanks.

With a date in some month in A1:

First Monday:

B1: =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+6)

Last Monday

C1: =B1+28-7*(MONTH(B1)<>MONTH(B1+28))


--ron

 

[Ron Rosenfeld]

With a date in some month in A1:

First Monday:

B1: =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+6)

Last Monday

C1: =B1+28-7*(MONTH(B1)<>MONTH(B1+28))


--ron

To expand the above to cover any day of the week, change B1:

B1: =A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-DOW)

where DOW = Day of Week (1=Sun; 2=Mon; etc.)

C1 stays the same.


--ron