A Better NETWORKDAYS
This page describes a formula you can use in place of NETWORKDAYS.
 |
All the formula presented on this page are array formulas, so you must press
CTRL SHIFT ENTER rather than just ENTER when you first
enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula
enclosed in curly braces { }. See the array formulas page for complete information
and examples of array formulas.
|
The NETWORKDAYS function (available via the Analysis Tool Pack in Excel 2003 and earlier and natively in Excel 2007 and later)
is a nice function that returns the number of work days between two dates, excluding Saturdays, Sundays, and holidays
in a user-created list. However, NETWORKDAYS is limited because the days Saturday and Sunday
are hard coded into the function. There is no way via NETWORKDAYS to include Saturdays but exclude
Tuesdays. This page describes a formula that improves on the traditional NETWORKDAYS
function by allowing a list of any number of days to exclude from the count. In some countries, the work week is not
from Monday through Friday. Also, an employee may be on a flex-time schedule working 4 days each week, excluding Fridays,
Saturdays, and Sundays. In these circumstances, NETWORKDAYS is not at all useful.
The following formula does everything that the traditional NETWORKDAYS function does, plus it
allows you to select as many days of the week as you want to exclude from the calculations. There are two flavors of the
formula. The first version does not allow a list of holidays to exclude from the count. The second version does allow a list
of holidays to exclude. Both formulas require a range named ExcludeDaysOfWeek that lists the day
of week numbers (1 = Sunday, 2 = Monday, ... 7 = Saturday) to exclude from the calculation.
Formula Without Holidays
=SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),ExcludeDaysOfWeek,0)),1,0))
In this formula, StartDate is the data at which counting will begin.
EndDate is the last date of the period to count. ExcludeDaysOfWeek
is a range of up to 7 cells indicating the day-of-week numbers (1 = Sunday, 2 = Monday, ... 7 = Saturday) to exclude
from the count. You may, if you choose to, replace the range reference of ExcludeDaysOfWeek
to a hard-coded list of day numbers. For example,
=SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),{1,6,7},0)),1,0))
Note that the days of the week are enclosed in curly braces { }, not parentheses.
Formula Supports Holidays
The formula below works must like the previous formula except that it allows you
to enter holidays in a range of cells, and those holidays will be excluded from the
count. You must create a named range of cells named Holidays that lists the
list of holidays to exclude from the result.
=IF(OR(StartDate<=0,EndDate<=0,StartDate>EndDate,ISNUMBER(StartDate)=FALSE,
ISNUMBER(EndDate)=FALSE),NA(),SUM(IF(ISERROR(MATCH(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate))),
ExcludeDaysOfWeek,0)),IF(ISERROR(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,0)),1,0)),0))
The formula is split into multiple lines here for the sake of readability. In practice, though, the entire formula
should be on a single line. The parameters in this formula have the same meaning as they did in the previous formula,
with the addition of the Holidays list. Holidays should
be a range of cells that contains dates to exclude from the count. It does not matter if a holiday falls on a day of week
that is listed in the ExcludeDaysOfWeek.
|
All the formula presented on this page are array formulas, so you must press
CTRL SHIFT ENTER rather than just ENTER when you first
enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula
enclosed in curly braces { }. See the array formulas page for complete information
and examples of array formulas.
|
The WORKDAY function suffers from the same problem as NETWORKDAYS; that is,
Saturday and Sunday are hard-coded into the function. See a better WORKDAY function page for a VBA
replacement for WORKDAY.
 |
This page last updated: 18-August-2009. |