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

  • Thread starter Thread starter Morocco Mole
  • Start date Start date
M

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).
 
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))
 
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).
Click to expand...

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
 
Back
Top