Calculate the first monday of a month in the future or in the past

[Morocco Mole]

Hello.

If I have a date in cell a1, what would be the formula to calculate the
first Monday of the next month (+1 month), and then the first Monday of next
month after that (+2 months)?

Also, would this formula be able to be used to calculate first Monday of the
current and the past months? (I assume you can just change a number in the
formula).

 

[Niek Otten]

Hi Morocco,

Look here and a little bit above and below:

http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear

 

[T. Valko]

The general formula to return the nth day of the week in a month is:

DATE(year,month,1+N*7)-WEEKDAY(DATE(year,month,8-DOW))

Where:

N = the nth day. For example, 1 = 1st Wednesday of the month or 4 = 4th
Wednesday of the month

DOW = day of the week where:

1 = Sunday
2 = Monday
3 = Tuesday
...
7 = Saturday

So:

A1 = some date like 7/27/2009

=DATE(YEAR(A1),MONTH(A1),1+1*7)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-2))

Returns 7/6/2009 (Monday)

For future and previous months just add/subtract the number of months like
this:

For the 1st Monday in August (based on the date entered in A1):

=DATE(YEAR(A1),MONTH(A1)+1,1+1*7)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,8-2))

For the 1st Monday in June (based on the date entered in A1):

=DATE(YEAR(A1),MONTH(A1)-1,1+1*7)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)-1,8-2))

 

[Ron Rosenfeld]

Hello.

If I have a date in cell a1, what would be the formula to calculate the
first Monday of the next month (+1 month), and then the first Monday of next
month after that (+2 months)?

Also, would this formula be able to be used to calculate first Monday of the
current and the past months? (I assume you can just change a number in the
formula).

Next month first Monday:

=DATE(YEAR(A1),MONTH(A1)+1,8)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,6))

2nd next month first Monday:

=DATE(YEAR(A1),MONTH(A1)+2,8)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,6))
--ron