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.

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

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.

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

On Fri, 26 May 2006 11:49:03 -0700, 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.

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

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

On Fri, 26 May 2006 20:31:35 -0400, Ron Rosenfeld
wrote:

On Fri, 26 May 2006 11:49:03 -0700, 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.

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