How can i set a formula which shows 2nd last Monday of the month?

[J]

One more:-
1. How can i set a formula which shows 2nd last Monday of the month?
A1 = 2009/07/10
A2 = 2009/07/20 (a formula which shows 2nd last Monday of the month)

OR

A1 = 2009/07/22
A2 = 2009/08/24 (a formula which shows 2nd last Monday of the month)

 

[Jacob Skaria]

Try with date in A1

In B1
=(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-WEEKDAY((DATE(YEAR(A1),MONTH(A1)+1,1)-1))-5

If this post helps click Yes

 

[J]

yes. it works

thx
--
J

Try with date in A1

In B1
=(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-WEEKDAY((DATE(YEAR(A1),MONTH(A1)+1,1)-1))-5

If this post helps click Yes

 

[Jacob Skaria]

There is a small correction to the formula..To see the error try a date in A1
of May2009 with the previous formula and the current.

=(DATE(YEAR(A1),MONTH(A1)+1,1)-1)-WEEKDAY((DATE(YEAR(A1),MONTH(A1)+1,1)-1))-IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)=1,12,5)

If this post helps click Yes

 

[Ron Rosenfeld]

yes. it works

thx

There seem to be a number of instances for which this formula returns the Last
Monday, and not the second to last Monday.

For example: 10-May-2009 --> 25-May-2009
January 2010 also --> Last Monday
Feb 2010

Try this instead:

=DATE(YEAR(A1),MONTH(A1)+1,-6)-
WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0)-1)

--ron

 

[Teethless mama]

=EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0),3)-7