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

One mo-
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)
--
J

How can i set 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
---------------
Jacob Skaria


"J" wrote:

One mo-
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)
--
J

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

yes. it works

thx
--
J


"Jacob Skaria" wrote:

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


"J" wrote:

One mo-
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)
--
J

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

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
---------------
Jacob Skaria


"J" wrote:

yes. it works

thx
--
J


"Jacob Skaria" wrote:

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


"J" wrote:

One mo-
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)
--
J

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

On Tue, 30 Jun 2009 03:46:02 -0700, J wrote:

yes. it works

thx
--
J


"Jacob Skaria" wrote:

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


"J" wrote:

One mo-
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)
--
J


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

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

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


"J" wrote:

One mo-
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)
--
J