Second Saturday Of The Month

  • Thread starter Thread starter FrankM
  • Start date Start date
F

FrankM

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)
 
Try this with a date in A1

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

Mike
 
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?
Click to expand...


=A1-DAY(A1)+15-WEEKDAY(A1-DAY(A1)+1)
--ron
 
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?
Click to expand...

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?
 
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
 
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.
 
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?
 
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.
 
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
 
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?
Click to expand...

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