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

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.

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


"Rossta" wrote:

> 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.

On Fri, 26 May 2006 11:49:03 -0700, Rossta <[email protected]>
wrote:

>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

On Fri, 26 May 2006 20:31:35 -0400, Ron Rosenfeld <[email protected]>
wrote:

>On Fri, 26 May 2006 11:49:03 -0700, Rossta <[email protected]>
>wrote:
>
>>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

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