Find first Monday in a month

[Pioneer-1752]

I need to find the first Monday of a given month.

If cell A2 has the first day of the month, I need cell A3 to have the first Monday of the month referenced in A2 in mm/dd/yyyy format.

Can someone help me?

Thank you.

 

[Aladin Akyurek]

In A3 enter:

=A2+IF(2 < WEEKDAY(A2), 7 - WEEKDAY(A2) + 2, 2 - WEEKDAY(A2))

where A2 houses the date of interest.

 

[Mark W.]

=A2+MOD(8-WEEKDAY(A2,2),7)
This message was edited by Mark W. on 2002-08-22 13:22

 

[lenze]

Here's another formula using CHOOSE

=CHOOSE(WEEKDAY(A2,2),A2,A2+6,A2+5,A2+4,A2+3,A2+2,A2+1)

 

[Dave Patton]

If A2 has any valid date for the month, try

=DATE(YEAR(A2),MONTH(A2),7)-WEEKDAY(DATE(YEAR(A2),MONTH(A2),7),3)

 

[Pioneer-1752]

Thank you everyone for all the help.

This is exactly what I needed!!!

 

[Mark W.]

On 2002-08-22 08:56, Dave Patton wrote:

If A2 has any valid date for the month, try

=DATE(YEAR(A2),MONTH(A2),7)-WEEKDAY(DATE(YEAR(A2),MONTH(A2),7),3)

Or simply...

=TEXT(A2,"mm/yyyy")+MOD(8-WEEKDAY(TEXT(A2,"mm/yyyy"),2),7)

 

[IML]

A very late entry:
=A2+6-WEEKDAY(A2-1,3)

I was going to post this morning but pesky work got in the way. I post this now only because I only recently discoverd the "3" in the second argument of weekday. That goes monday (0) thru Sunday (6).

Also, I think weekday is "off the shelf" and not a part of the tool pak add im

 

[Mark W.]

IML, good use of Return_type 3! You're right about WEEKDAY not being a part of the Analysis ToolPak. I confused it with WEEKNUM which I've been using quite a bit lately.

 

[BJLong76]

Piggybacking here- This formula worked perfectly to help me calculate Labor Day 2014. Which happens to be the first day of the month AND the first Monday. Thank you, IML!