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?

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

where A1 has any date in the month in question.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"FrankM" wrote in message
...
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?

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?

On Wed, 26 Mar 2008 13:25:01 -0700, 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?


=A1-DAY(A1)+15-WEEKDAY(A1-DAY(A1)+1)
--ron

On Wed, 26 Mar 2008 13:25:01 -0700, 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?

I should have written:

With some date in the desired month in A1:

=A1-DAY(A1)+15-WEEKDAY(A1-DAY(A1)+1)

Or, for "this" month, substitute TODAY() for A1 in the above formula.
--ron

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?

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?

That was perfect. I am very grateful. The formula was functional and I
appreciated your taking the time to explain each part of the formula.

Thank you very much, Mike.


"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?

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?

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?

Use this formula instead


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


the generic version is


=DATE(Yr,MM,1+7*Nth)-WEEKDAY(DATE(Yr,MM,8-DoW))

where nth is the number you want 1st, 2nd, 3rd etc thus in my formula 7*3
since you wanted the 3rd Monday
and where DoW stands for day of the week with Sunday starting with 1 and so
on and where I put
2 for Monday. If you want the 2nd Saturday in the month of A1 it would look
like

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




--


Regards,


Peo Sjoblom

"FrankM" wrote in message
...
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?

On Wed, 12 Nov 2008 09:08:03 -0800, 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?





Frank,

This formula will calculate the first N-day of any month:

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-DOW)

where DOW is 1 for Sunday, 2 for Monday, etc.

So to calculate the third Monday, you would substitute 2 for DOW, and add 14:

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-2)+14

--ron