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

  • Thread starter Thread starter J
  • Start date Start date
J

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)
 
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
 
yes. it works

thx
--
J


Jacob Skaria said:
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
Click to expand...
 
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
 
yes. it works

thx
Click to expand...


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