Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 45
Default Second Saturday Of The Month

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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default Second Saturday Of The Month

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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default Second Saturday Of The Month

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?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Second Saturday Of The Month

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
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Second Saturday Of The Month

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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 45
Default Second Saturday Of The Month

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?

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default Second Saturday Of The Month

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?

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 45
Default Second Saturday Of The Month

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?

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 45
Default Second Saturday Of The Month

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?

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 45
Default Second Saturday Of The Month

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?



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default Second Saturday Of The Month

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?



  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Second Saturday Of The Month

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find Last Saturday of the Year jlclyde Excel Discussion (Misc queries) 23 March 18th 08 02:14 PM
Help finding the date of the last Saturday of a given month Chuck M Excel Worksheet Functions 9 May 23rd 07 08:38 PM
first saturday in a month Barry Excel Worksheet Functions 8 February 7th 06 04:05 PM
How to set Saturday as a working day Chatter_tk Setting up and Configuration of Excel 3 August 16th 05 02:38 PM
"Saturday as a work day? keith m Excel Worksheet Functions 6 August 12th 05 10:17 PM


All times are GMT +1. The time now is 10:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"