A Better NETWORKDAYS
This page describes a formula you can use in place of NETWORKDAYS.
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.
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
This 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.
=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.
NOTE: Both of these formulas are Array Formulas. This means that 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 in the formula bar enclosed in curly braces { }. If you do not enter the formula with
CTRL SHIFT ENTER, the formula will not work. See Array Formulas In Excel for much
more information about array formulas.
This page last updated: 7-May-2008