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 usercreated 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 flextime 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 dayofweek 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 hardcoded 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 hardcoded into the function. See a better WORKDAY function page for a VBA
replacement for WORKDAY.
The revised net workdays formulas above can be implemented as a VBA function. The code for such a function is listed below. You can
download the bas module file containing the code. Documentation for the function is
included in the comments box in the code.
Public Function NetWorkdays2(StartDate As Date, EndDate As Date, _
ExcludeDaysOfWeek As Long, Optional Holidays As Variant) As Variant
Dim TestDayOfWeek As Long
Dim TestDate As Date
Dim Count As Long
Dim Stp As Long
Dim Holiday As Variant
Dim Exclude As Boolean
If ExcludeDaysOfWeek < 0 Or ExcludeDaysOfWeek >= 127 Then
NetWorkdays2 = CVErr(xlErrNum)
Exit Function
End If
If StartDate <= 0 Or EndDate <= 0 Then
NetWorkdays2 = CVErr(xlErrNum)
Exit Function
End If
If StartDate <= EndDate Then
Stp = 1
Else
Stp = 1
End If
For TestDate = StartDate To EndDate Step Stp
TestDayOfWeek = 2 ^ (Weekday(TestDate, vbSunday)  1)
If (TestDayOfWeek And ExcludeDaysOfWeek) = 0 Then
If IsMissing(Holidays) = True Then
Count = Count + 1
Else
Exclude = False
If IsObject(Holidays) = True Then
For Each Holiday In Holidays
If Holiday.Value = TestDate Then
Exclude = True
Exit For
End If
Next Holiday
Else
If IsArray(Holidays) = True Then
For Each Holiday In Holidays
If Int(Holiday) = TestDate Then
Exclude = True
Exit For
End If
Next Holiday
Else
If TestDate = Holidays Then
Exclude = True
End If
End If
End If
If Exclude = False Then
Count = Count + 1
End If
End If
Else
End If
Next TestDate
NetWorkdays2 = Count * Stp
End Function

This page last updated: 13March2011. 