View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default 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