Sundays

[Paul W Smith]

Can anyone give a formula which will calculate the number of days between
two dates, not counting Sundays.

Paul Smith

 

[Peo Sjoblom]

One way

=1+A2-A1-INT((A2-WEEKDAY(A2)-A1+8)/7)-SUMPRODUCT((F2:F12>=A1)*(F2:F12<=A2)*(
WEEKDAY(F2:F12)<>1))

starts date in A1 and end date in A2, public holidays listed in F2:F12, if
holidays are of no importance you can use

=1+A2-A1-INT((A2-WEEKDAY(A2)-A1+8)/7)

 

[Jason Morin]

Just another way:

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

where B1 = end date and A1 = start date. To include
holidays (listed in C1:C10)

=1+B1-A1-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))
=1))-COUNT(C1:C10)

HTH
Jason
Atlanta, GA

 

[Bob Phillips]

Jason ,

Unfortunately, that counts (or discounts) a holiday date even if it is not
between the two dates. For that you need

=1+B1-A1-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=1))-SUMPRODUCT((C1:
C10>=A1)*(C1:C10<=B1)*(WEEKDAY(C1:C10)<>1))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

Just another way:

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

Isn't this equivalent to

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))<>1))

?

where B1 = end date and A1 = start date. To include
holidays (listed in C1:C10)

=1+B1-A1-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=1))
-COUNT(C1:C10)

Bob Phillips has already given one fix, but another would be

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT($A$1&":"&B1)))<>1)
*(COUNTIF(Holidays,ROW(INDIRECT($A$1&":"&B1)))=0))