NETWORKDAYS - Non Add-in Alternative - Cannot get it working

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

Hi All,

I have a general aversion to using functions that rely upon add-ins
(mainly due to the number of less experienced users who aren't even
aware of add-ins).

Therefore, when I needed to use the NETWORKDAYS function, I turned to
the Excel discussion group archives.

I found the following post by Philip Coxson that gave an example of
an alternative function that uses only standard native Excel
functions:

http://groups.google.co.nz/[email protected]

Note: It may wrap in your news client / browser and may require
re-pasting together.


However, when I tried using it, I could not get the holidays to work
properly.

This is the function as posted (array entered):

=SUM(IF((WEEKDAY(A10+ROW(INDIRECT("1:"&(1+B10-A10)))-1,2)>5)+NOT(ISERR
OR(MATCH(A10+ROW(INDIRECT("1:"&(1+B10-A10)))-1,Holidays,0))),"X",1))

This is my version, replacing A10 with VALUE("3 Jan 2003") being an
example start_date, and B10 with VALUE("6 Jan 2003") being an example
end_date.

{=SUM(IF((WEEKDAY(VALUE("3 Jan 2003")+ROW(INDIRECT("1:"&(1+VALUE("6
Jan 2003")-VALUE("3 Jan 2003"))))-1,2)>5)+NOT(ISERROR(MATCH(VALUE("3
Jan 2003")+ROW(INDIRECT("1:"&(1+VALUE("6 Jan 2003")-VALUE("3 Jan
2003"))))-1,Holidays,0))),"X",1))}}

Notes: 3 Jan 2003 is a Friday, and 6 Jan 2003 is a Monday, so the
answer is 2 (days).

This works perfectly - so far so good.

However, when I bring in a holiday, it falls over. Let's assume 3 Jan
2003 is a holiday:

{=SUM(IF((WEEKDAY(VALUE("3 Jan 2003")+ROW(INDIRECT("1:"&(1+VALUE("6
Jan 2003")-VALUE("3 Jan 2003"))))-1,2)>5)+NOT(ISERROR(MATCH(VALUE("3
Jan 2003")+ROW(INDIRECT("1:"&(1+VALUE("6 Jan 2003")-VALUE("3 Jan
2003"))))-1,VALUE("3 Jan 2003"),0))),"X",1))}}

This still returns the same answer (2 days) despite having replaced
the 'holiday' range with the date value for 3 Jan 2003.

I have tried to reverse engineer the function, but it seems like it
should work.

Can anyone offer any suggestions?

Thanks,

Alan.
 
I have a general aversion to using functions that rely upon add-ins
(mainly due to the number of less experienced users who aren't even
aware of add-ins).
Click to expand...
...

Try this instead.

=SUMPRODUCT(--(COUNTIF(Holidays,ROW(INDIRECT(EarlierDate&":"&LaterDate)))=0),
--(WEEKDAY(ROW(INDIRECT(EarlierDate&":"&LaterDate)),3)<5))

This assumes your holidays are entered in a range named Holidays. If Holidays is
either a horizontal or vertical 1D array, use

=SUMPRODUCT(--ISERROR(MATCH(ROW(INDIRECT(EarlierDate&":"&LaterDate)),
Holidays,0)),--(WEEKDAY(ROW(INDIRECT(EarlierDate&":"&LaterDate)),3)<5))
 
Back
Top