Calculating number of Saturday's in a Year
 

Hi -

How can I calculate the number of Saturday's between two dates (say
4/1/2005 and 3/31/2006)?

Thanks!
Erica


--
ecurns
------------------------------------------------------------------------
ecurns's Profile: http://www.excelforum.com/member.php...o&userid=29751
View this thread: http://www.excelforum.com/showthread...hreadid=494678

Calculating number of Saturday's in a Year
 

Assume your first date is in A1 (4/1/2005) and your second date is in A2
(3/31/2006), use the following formula (Note, this is an array formula so
confirm it with CTRL+SHIFT+ENTER).

=SUM(--(WEEKDAY(A1+ROW(INDIRECT("1:"&DAYS360(A1,A2)))-1)=7))

Change the reference of A1 and A2 to fit your data.

--
Regards,
Dave


"ecurns" wrote:


Hi -

How can I calculate the number of Saturday's between two dates (say
4/1/2005 and 3/31/2006)?

Thanks!
Erica


--
ecurns
------------------------------------------------------------------------
ecurns's Profile: http://www.excelforum.com/member.php...o&userid=29751
View this thread: http://www.excelforum.com/showthread...hreadid=494678

Calculating number of Saturday's in a Year
 

Possibly this:

=ROUNDUP(((DATEDIF(A1,A2,"d")+1)-(NETWORKDAYS(A1,A2)))/2,0)

where A1 contains start date
and A2 contains end date

You have to have the Analysis Toolpak Addin installed too!


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=494678

Calculating number of Saturday's in a Year
 

Thanks - but this isn't quite right. For dates 4/1/1983 to 3/31/1984 it
should return 53, but it retuns only 52. I'm thinking it might be the
days360 function that is rounding?



David Billigmeier Wrote:
Assume your first date is in A1 (4/1/2005) and your second date is in
A2
(3/31/2006), use the following formula (Note, this is an array formula
so
confirm it with CTRL+SHIFT+ENTER).

=SUM(--(WEEKDAY(A1+ROW(INDIRECT("1:"&DAYS360(A1,A2)))-1)=7))

Change the reference of A1 and A2 to fit your data.

--
Regards,
Dave


"ecurns" wrote:


Hi -

How can I calculate the number of Saturday's between two dates (say
4/1/2005 and 3/31/2006)?

Thanks!
Erica


--
ecurns

------------------------------------------------------------------------
ecurns's Profile:
http://www.excelforum.com/member.php...o&userid=29751
View this thread:
http://www.excelforum.com/showthread...hreadid=494678




--
ecurns
------------------------------------------------------------------------
ecurns's Profile: http://www.excelforum.com/member.php...o&userid=29751
View this thread: http://www.excelforum.com/showthread...hreadid=494678

Calculating number of Saturday's in a Year
 

This one didn't work either with 4/1/1984 - 3/31/1985, should be 52
instead it returns 53. Hmm...





Vito Wrote:
Possibly this:

=ROUNDUP(((DATEDIF(A1,A2,"d")+1)-(NETWORKDAYS(A1,A2)))/2,0)

where A1 contains start date
and A2 contains end date

You have to have the Analysis Toolpak Addin installed too!


--
ecurns
------------------------------------------------------------------------
ecurns's Profile: http://www.excelforum.com/member.php...o&userid=29751
View this thread: http://www.excelforum.com/showthread...hreadid=494678

Calculating number of Saturday's in a Year
 

With your two dates entered,

My suggested formula returned 53 as you say you expect!


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=494678

Calculating number of Saturday's in a Year
 

For the number of Saturdays between two periods, inclusive, try...

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=6))

....where A1 contains your start date and B1 contains your end date.

Hope this helps!

In article ,
ecurns wrote:

Hi -

How can I calculate the number of Saturday's between two dates (say
4/1/2005 and 3/31/2006)?

Thanks!
Erica

Calculating number of Saturday's in a Year
 

Vito Wrote:
With your two dates entered,

My suggested formula returned 53 as you say you expect!

Didn't work with the next 2 dates you posted, though?

Hmmm...perhaps Leap year? I'll have to look into this more....


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=494678

Calculating number of Saturday's in a Year
 

Using DAYS(360) will potentially undercount your Saturdays (as well as
being less efficient by a function call). Better:

=SUMPRODUCT(--(WEEKDAY(A1+ROW(INDIRECT("1:"&(A2-A1))))=7))

Whether to subtract one or not depends on the OP's interpretation of
"between" (i.e., exclusive or inclusive). For instance, if the period
ends on a Saturday, using

=SUMPRODUCT(--(WEEKDAY(A1+ROW(INDIRECT("1:"&(A2-A1)-1)))=7))

will ignore the last day, where the first formula will include it.


In article ,
"David Billigmeier" wrote:

Assume your first date is in A1 (4/1/2005) and your second date is in A2
(3/31/2006), use the following formula (Note, this is an array formula so
confirm it with CTRL+SHIFT+ENTER).

=SUM(--(WEEKDAY(A1+ROW(INDIRECT("1:"&DAYS360(A1,A2)))-1)=7))

Change the reference of A1 and A2 to fit your data.

Calculating number of Saturday's in a Year
 

Awesome - this works!!

Thanks so much
Erica



Domenic Wrote:
For the number of Saturdays between two periods, inclusive, try...

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=6))

....where A1 contains your start date and B1 contains your end date.

Hope this helps!

In article ,
ecurns wrote:

Hi -

How can I calculate the number of Saturday's between two dates (say
4/1/2005 and 3/31/2006)?

Thanks!
Erica


--
ecurns
------------------------------------------------------------------------
ecurns's Profile: http://www.excelforum.com/member.php...o&userid=29751
View this thread: http://www.excelforum.com/showthread...hreadid=494678