Formula to find the first/third monday of each month.

[lorcanjc]

I need a way to calculate the first/second monday of each month.

I have tried using the weeknum formula among others but this only give
returns the number within the year.

Any help will be very much appreciated

 

[Andy Brown]

1st & 2nd or 1st & 3rd?

You'll need to play around a bit (return type for WEEKDAY always does my
head in), but assuming you have dates in A1, A2, etc. In B1,

=IF(WEEKDAY(A1,2)=1,A1,A1+(8-WEEKDAY(A1,2)))

In C1 (for 3rd Monday), just something like

=B1+14

HTH,
Andy

 

[Paul]

1st & 2nd or 1st & 3rd?

You'll need to play around a bit (return type for WEEKDAY always does my
head in), but assuming you have dates in A1, A2, etc. In B1,

=IF(WEEKDAY(A1,2)=1,A1,A1+(8-WEEKDAY(A1,2)))

In C1 (for 3rd Monday), just something like

=B1+14

HTH,
Andy

Your formula works if the date in A1 is on or before the first Monday of the
month. This simpler version does the same:
=A1-WEEKDAY(A1-2)+7

 

[Ron Rosenfeld]

I need a way to calculate the first/second monday of each month.

I have tried using the weeknum formula among others but this only gives
returns the number within the year.

Any help will be very much appreciated.

If the first of a month is in A1, then the first Monday of that month is:

=A1+7-WEEKDAY(A1,3)

The second Monday would be:

=A1+7-WEEKDAY(A1,3) + 7 or
=A1+14-WEEKDAY(A1,3)

The third Monday would be:

=A1+7-WEEKDAY(A1,3) + 14 or
=A1+21-WEEKDAY(A1,3)

 

[Paul]

If the first of a month is in A1, then the first Monday of that month is:

=A1+7-WEEKDAY(A1,3)

The second Monday would be:

=A1+7-WEEKDAY(A1,3) + 7 or
=A1+14-WEEKDAY(A1,3)

The third Monday would be:

=A1+7-WEEKDAY(A1,3) + 14 or
=A1+21-WEEKDAY(A1,3)

If the first of the month is itself a Monday, I think
=A1+7-WEEKDAY(A1,3)
will return the eighth rather than the first.

 

[lorcanjc]

Thanks for all the help so far,I have almost completely got what I a
looking for.

The formula so far gives me the first Monday after the dates input.

What I now need is it be able to identify all the first Mondays in th
month from a list of dates.

Any ideas

 

[Peo Sjoblom]

if the dates start in A1 going down this formula will give the first Monday
in the month

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