Workday() and Networkdays()

  • Thread starter Thread starter amadeus068
  • Start date Start date
A

amadeus068

I have a worksheet that uses both functions. The problem I am having i
when I distribute the worksheet, those that receive it either does no
have the ATP installed. Are there alternate functions that can b
used? Thank you in advance
 
Definitely there are alternate functions that can be used, they will
probably be longer than the formulas used with the ATP functions though.
 
amadeus068 > said:
I have a worksheet that uses both functions. The problem I am having
is when I distribute the worksheet, those that receive it either does
not have the ATP installed. Are there alternate functions that can
be used? Thank you in advance.
Click to expand...

Easier to include a macro that checks that the ATP exists and installs it
(or complains that it can't install it). However, if you must do this with
built-ins,

=NETWORKDAYS(BeginDate,EndDate)

can be faked with

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(BeginDate&":"&EndDate)),2)<=5))

and

=NETWORKDAYS(BeginDate,EndDate,Holidays)

can be faked with

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(BeginDate&":"&EndDate)),2)<=5),
1-COUNTIF(Holidays,ROW(INDIRECT(BeginDate&":"&EndDate))))


Much more cumbersome for WORKDAY.

=WORKDAY(BeginDate,Days)

can be faked with the *array* formula

=BeginDate+MATCH(C3,MMULT(--(ROW(INDIRECT("1:"&INT(1+C3/5)*7))
=TRANSPOSE(ROW(INDIRECT("1:"&INT(1+C3/5)*7)))),
Click to expand...
--(WEEKDAY(BeginDate+ROW(INDIRECT("1:"&INT(1+C3/5)*7)),2)<=5)),0)

and

=WORKDAY(BeginDate,Days,Holidays)

can be faked with the *array* formula

=BeginDate+MATCH(Days,MMULT(--(ROW(INDIRECT("1:"&INT(3+Days/5)*7))
=TRANSPOSE(ROW(INDIRECT("1:"&INT(3+Days/5)*7)))),
Click to expand...
(WEEKDAY(BeginDate+ROW(INDIRECT("1:"&INT(3+Days/5)*7)),2)<=5)
*(1-COUNTIF(Holidays,BeginDate+ROW(INDIRECT("1:"&INT(3+Days/5)
*7))))),0)

where the '3+' term should be selected so that 3*7=21 is more than the
number of dates in the Holidays range. Note that these last two can REALLY
slow down recalculation.
 
Back
Top