Excel: Using NETWORKDAYS() With Multiple Holidays

• Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
• Microsoft Excel for the Macintosh, versions 4.0, 5.0

SUMMARY

When using the NETWORKDAYS() function with multiple holidays, the holidays may be entered as cell references or in the serial number format and the dates must be enclosed in braces ({}).

In the NETWORKDAYS() function, dates in the form of text are ignored if your worksheet is in the 1904 date system (Options Calculation). This problem is more likely to occur on the Macintosh platform where the 1904 date system is the default. Dates represented as serial numbers will be accepted.

MORE INFORMATION

The NETWORKDAYS() function returns the number of whole working days between two dates. The Holidays parameter is used to exclude specific dates from the calculation. Starting and Ending dates can be entered directly as serial numbers, as text, through a cell reference, or by using the DATEVALUE() function. Single holidays can also be entered this way. However, multiple holidays must be entered in the serial number format only, and the set of holiday dates must be enclosed in braces ({}).

The correct way to indicate dates in the form of text is to use the DATEVALUE() function. Also, if you are using an array constant to specify multiple "text" dates for the holidays argument, you must enclose the array constant within the DATEVALUE function. An example is provided in the "Function Reference," page 285.

Examples listing dates in the function

=NETWORKDAYS(DATEVALUE("1/1/92"),DATEVALUE("12/31/92"),

   DATEVALUE({"12/24/92","12/25/92"}))

=NETWORKDAYS(DATEVALUE("1/1/92"),DATEVALUE("12/31/92"),

   {33962,33963})

Examples using cell references

A1: 12/24/92 A2: 12/25/92

=NETWORKDAYS(DATEVALUE("1/1/92"),DATEVALUE("12/31/92"),A1:A2)

-or-

A1: 1/1/92 A2: 12/31/92 A3: 12/24/92 A4: 12/25/92

=NETWORKDAYS(A1,A2,A3:A4)

You cannot, however use the following:

A1: 12/24/92 C1: 12/25/92

=NETWORKDAYS(DATEVALUE("1/1/92"),DATEVALUE("12/31/92"),{A1,C1})

REFERENCES

"Function Reference," version 4.0, pages 94, 284-285