P Paul W Smith May 10, 2004 #1 Can anyone give a formula which will calculate the number of days between two dates, not counting Sundays. Paul Smith
Can anyone give a formula which will calculate the number of days between two dates, not counting Sundays. Paul Smith
P Peo Sjoblom May 10, 2004 #2 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)
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)
J Jason Morin May 10, 2004 #3 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
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
B Bob Phillips May 10, 2004 #4 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)
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)
H Harlan Grove May 10, 2004 #5 Just another way: =1+B1-A1-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=1)) Click to expand... 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) Click to expand... 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))
Just another way: =1+B1-A1-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)))=1)) Click to expand... 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) Click to expand... 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))
