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

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" wrote in message
...
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

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

Whe

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

DOW = day of the week whe

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" wrote in message
...
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

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/sh...d.php?t=120021

On Mon, 27 Jul 2009 15:03:02 -0700, Morocco Mole
wrote:

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