OK I found an issue with this formula when for January 2012. In 2012 if I use
the following ...

=IF(MONTH(A1)=1,A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),22,21,20,19,18,17,23),"")

A1 is 1/1/2012, result of the above formula is 1/23/2009 but that is not the
third Monday it is the fourth (1/2/2012, 1/9/2012, 1/16/2012, 1/23/2012 and
1/30/2012 are the Mondays for January 2012).

Interestingly 1/1/2012 is a Sunday and 2/1/2009 is a Sunday. I'm wondering
if the formula doesn't work if the 1st is a Sunday.




"FrankM" wrote:

I tried the modification for the Third Monday and it seemed to work fine when
I was delaing with the month of January but when I'm working with February I
seem to have run into a glitch.

I used the following formula ...

=IF(MONTH(A1)=2,A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),22,21,20,19,18,17,23),"")

A1 is 2/1/2009, the result is 2/23/2009 which is the fourth Monday of the
month (the first Monday is 2/2/2009, followed by 2/9/2009, 2/16/2009 and
2/23/2009).

I could have sworn I had checked and double checked this formula but now
that I'm looking at it again it does not appear to be doing what I thought it
was. If the date is in January it appears to be functioning correctly but I'm
going to double check that now too.

Any ideas?




"Mike H" wrote:

Frank,

I prefer functional to awesome. Your not certain what the string of numbers
do, so to understand you need to break down the formula as follows:-
Lets assume we are working with any date in March

1. Find the last day of the previous month- =A1-DAY(A1) returns 29/2/2008
2. Find out what day that was- =WEEKDAY(A1-DAY(A1)) returns 6 for Friday
3. Add the required amount of days
=CHOOSE(WEEKDAY(A1-DAY(A1)),13,12,11,10,9,8,14)
We know the middle bit evalustes to 6 and the Choose bit tells it to 'choose
the 6th element of that array of numbers which is 8
5. Add 8 days to 29/2 and you get the second saturday

So to make it work for the third monday we use exactly the same formula but
alter the array of numbers to 22,21,20,19,18,17,23

Mike

"FrankM" wrote:

That was awesome ... I'm not certain what the "13,12,11,10,9,8,14" are doing
but this was perfect. Can this be modified to calculate the third Monday?

"Mike H" wrote:

Try this with a date in A1

=A1-DAY(A1)+CHOOSE(WEEKDAY(A1-DAY(A1)),13,12,11,10,9,8,14)

Mike

"FrankM" wrote:

OK, this may seem fairly simple but I'm missing something ...

Is there a formula that will calculate the Second Calendar Saturday of the
Month for me?