Find first Monday in a month

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Aug 22, 2002

• #2

In A3 enter:

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

where A2 houses the date of interest.

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Aug 22, 2002

• #3

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

 

Upvote 0

lenze

Legend

Joined

Feb 18, 2002

Messages

13,690

• Aug 22, 2002

• #4

Here's another formula using CHOOSE

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

 

Upvote 0

D

Dave Patton

Well-known Member

Joined

Feb 15, 2002

Messages

6,496

Office Version

1. 365
2. 2010

Platform

1. Windows

• Aug 22, 2002

• #5

If A2 has any valid date for the month, try

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

 

Upvote 0

P

Pioneer-1752

Spammer

Joined

Mar 14, 2002

Messages

15

• Aug 22, 2002

• #6

Thank you everyone for all the help.

This is exactly what I needed!!!

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Aug 22, 2002

• #7

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)

Click to expand...

Or simply...

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

 

Upvote 0

I

IML

MrExcel MVP

Joined

Feb 15, 2002

Messages

1,743

• Aug 22, 2002

• #8

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

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Aug 22, 2002

• #9

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.

 

Upvote 0

B

BJLong76

New Member

Joined

Nov 12, 2013

Messages

2

• Jan 6, 2014

• #10

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!

 

Upvote 0