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

--
Thanks,

Morocco Mole

[Niek Otten]

Hi Morocco,

Look here and a little bit above and below:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Morocco Mole" <Moroc...@discussions.microsoft.com> wrote in message
news:EDF80773-D169-4400...@microsoft.com...

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

--
Biff
Microsoft Excel MVP

"Morocco Mole" <Moroc...@discussions.microsoft.com> wrote in message
news:EDF80773-D169-4400...@microsoft.com...

[barry houdini]

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

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

--
barry houdini
------------------------------------------------------------------------
barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=120021

[Ron Rosenfeld]

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