Networkdays Stopped Working - Again
"T. Valko" wrote...
....
How about a replacement formula that doesn't depend on the ATP?
A1 = start date
B1 = end date
C1:C5 = holiday list (if you're using the Holidays argument)
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6),
--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),C1:C5,0))))
If you're not using the Holidays argument:
=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6))
....
Yucky volatile functions!
Using placeholders,
=EndDate-StartDate
-COUNTIF(Holidays,"="&StartDate)+COUNTIF(Holidays, ""&EndDate)
-2*INT((EndDate-WEEKDAY(EndDate,2)-StartDate+WEEKDAY(StartDate,2))/7)
-SIGN(WEEKDAY(EndDate,2)-6)+(WEEKDAY(StartDate,2)=7)
which in prose is
period in days between the two dates
-holidays during the period
-full weekends during the period
-tricky correction term
|