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

 

[barry houdini]

If you want a series of first Mondays then you can just add th
requisite number of days to your initial calculation, e.g. if you us
Biff's formula to get the first Monday of this month in B2 then you ca
use this formula in C2 copied down to get the first Monday of eac
subsequent month.

=B2+28+(DAY(B2+28)>7)*

 

[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