Calculating number of Saturday's in a Year

E

ecurns

Hi -

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

Thanks!
Eric
 
D

David Billigmeier

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.
 
V

Vito

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
 
E

ecurns

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

ecurns

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

Domenic

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!
 
V

Vito

Vito said:
With your two dates entered,

My suggested formula returned 53 as you say you expect!
Click to expand...

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

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

JE McGimpsey

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.
 
E

ecurns

Awesome - this works!!

Thanks so much
Erica


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