P
Paul W Smith
Can anyone give a formula which will calculate the number of days between
two dates, not counting Sundays.
Paul Smith
two dates, not counting Sundays.
Paul Smith
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
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)
=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)
J
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
=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
B
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)
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)
H
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))