software

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: www.excelforum.com/member.php...oamp;userid=29751
View this thread: www.excelforum.com/showthread...hreadid=494678Assume 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(quot;1:quot;amp;DAYS360(A1,A2)))-1)=7))

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

--
Regards,
Davequot;ecurnsquot; wrote:

gt;
gt; Hi -
gt;
gt; How can I calculate the number of Saturday's between two dates (say
gt; 4/1/2005 and 3/31/2006)?
gt;
gt; Thanks!
gt; Erica
gt;
gt;
gt; --
gt; ecurns
gt; ------------------------------------------------------------------------
gt; ecurns's Profile: www.excelforum.com/member.php...oamp;userid=29751
gt; View this thread: www.excelforum.com/showthread...hreadid=494678
gt;
gt;


Possibly this:

=ROUNDUP(((DATEDIF(A1,A2,quot;dquot;) 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: www.excelforum.com/member.php...oamp;userid=29182
View this thread: www.excelforum.com/showthread...hreadid=494678
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:
gt; Assume your first date is in A1 (4/1/2005) and your second date is in
gt; A2
gt; (3/31/2006), use the following formula (Note, this is an array formula
gt; so
gt; confirm it with CTRL SHIFT ENTER).
gt;
gt; =SUM(--(WEEKDAY(A1 ROW(INDIRECT(quot;1:quot;amp;DAYS360(A1,A2)))-1)=7))
gt;
gt; Change the reference of A1 and A2 to fit your data.
gt;
gt; --
gt; Regards,
gt; Dave
gt;
gt;
gt; quot;ecurnsquot; wrote:
gt;
gt; gt;
gt; gt; Hi -
gt; gt;
gt; gt; How can I calculate the number of Saturday's between two dates (say
gt; gt; 4/1/2005 and 3/31/2006)?
gt; gt;
gt; gt; Thanks!
gt; gt; Erica
gt; gt;
gt; gt;
gt; gt; --
gt; gt; ecurns
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; ecurns's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29751
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=494678
gt; gt;
gt; gt;--
ecurns
------------------------------------------------------------------------
ecurns's Profile: www.excelforum.com/member.php...oamp;userid=29751
View this thread: www.excelforum.com/showthread...hreadid=494678
This one didn't work either with 4/1/1984 - 3/31/1985, should be 52
instead it returns 53. Hmm...Vito Wrote:
gt; Possibly this:
gt;
gt; =ROUNDUP(((DATEDIF(A1,A2,quot;dquot;) 1)-(NETWORKDAYS(A1,A2)))/2,0)
gt;
gt; where A1 contains start date
gt; and A2 contains end date
gt;
gt; You have to have the Analysis Toolpak Addin installed too!--
ecurns
------------------------------------------------------------------------
ecurns's Profile: www.excelforum.com/member.php...oamp;userid=29751
View this thread: www.excelforum.com/showthread...hreadid=494678
With your two dates entered,

My suggested formula returned 53 as you say you expect!--
Vito
------------------------------------------------------------------------
Vito's Profile: www.excelforum.com/member.php...oamp;userid=29182
View this thread: www.excelforum.com/showthread...hreadid=494678For the number of Saturdays between two periods, inclusive, try...

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1amp;quot;:quot;amp;B1)),2)=6))

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

Hope this helps!

In article gt;,
ecurns gt; wrote:

gt; Hi -
gt;
gt; How can I calculate the number of Saturday's between two dates (say
gt; 4/1/2005 and 3/31/2006)?
gt;
gt; Thanks!
gt; Erica


Vito Wrote:
gt; With your two dates entered,
gt;
gt; 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: www.excelforum.com/member.php...oamp;userid=29182
View this thread: www.excelforum.com/showthread...hreadid=494678Using DAYS(360) will potentially undercount your Saturdays (as well as
being less efficient by a function call). Better:

=SUMPRODUCT(--(WEEKDAY(A1 ROW(INDIRECT(quot;1:quot;amp;(A2-A1))))=7))

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

=SUMPRODUCT(--(WEEKDAY(A1 ROW(INDIRECT(quot;1:quot;amp;(A2-A1)-1)))=7))

will ignore the last day, where the first formula will include it.In article gt;,
quot;David Billigmeierquot; gt; wrote:

gt; Assume your first date is in A1 (4/1/2005) and your second date is in A2
gt; (3/31/2006), use the following formula (Note, this is an array formula so
gt; confirm it with CTRL SHIFT ENTER).
gt;
gt; =SUM(--(WEEKDAY(A1 ROW(INDIRECT(quot;1:quot;amp;DAYS360(A1,A2)))-1)=7))
gt;
gt; Change the reference of A1 and A2 to fit your data.


Awesome - this works!!

Thanks so much
Erica
Domenic Wrote:
gt; For the number of Saturdays between two periods, inclusive, try...
gt;
gt; =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1amp;quot;:quot;amp;B1)),2)=6))
gt;
gt; ....where A1 contains your start date and B1 contains your end date.
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; ecurns gt; wrote:
gt;
gt; gt; Hi -
gt; gt;
gt; gt; How can I calculate the number of Saturday's between two dates (say
gt; gt; 4/1/2005 and 3/31/2006)?
gt; gt;
gt; gt; Thanks!
gt; gt; Erica--
ecurns
------------------------------------------------------------------------
ecurns's Profile: www.excelforum.com/member.php...oamp;userid=29751
View this thread: www.excelforum.com/showthread...hreadid=494678