A Better NETWORKDAYS

This page describes a formula you can use in place of NETWORKDAYS.

Introduction

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.

A Better NETWORKDAYS

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.

BetterNetWorkdays As VBA

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
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' NetWorkdays2
' This function calcluates the number of days between StartDate and EndDate
' excluding those days of the week specified by ExcludeDaysOfWeek and
' optionally excluding dates in Holidays. ExcludeDaysOfWeek is a
' value from the table below.
'       1  = Sunday     = 2 ^ (vbSunday - 1)
'       2  = Monday     = 2 ^ (vbMonday - 1)
'       4  = Tuesday    = 2 ^ (vbTuesday - 1)
'       8  = Wednesday  = 2 ^ (vbWednesday - 1)
'       16 = Thursday   = 2 ^ (vbThursday - 1)
'       32 = Friday     = 2 ^ (vbFriday - 1)
'       64 = Saturday   = 2 ^ (vbSaturday - 1)
' To exclude multiple days, add the values in the table together. For example,
' to exclude Mondays and Wednesdays, set ExcludeDaysOfWeek to 10 = 8 + 2 =
' Monday + Wednesday.
' If StartDate is less than or equal to EndDate, the result is positive. If
' StartDate is greater than EndDate, the result is negative. If either
' StartDate or EndDate is less than or equal to 0, the result is a
' #NUM error. If ExcludeDaysOfWeek is less than 0 or greater than or
' equal to 127 (all days excluded), the result is a #NUM error.
' Holidays is optional and may be a single constant value, an array of values,
' or a worksheet range of cells.
' This function can be used as a replacement for the NETWORKDAYS worksheet
' function. With NETWORKDAYS, the excluded days of week are hard coded
' as Saturday and Sunday. You cannot exlcude other days of the week. This
' function allows you to exclude any number of days of the week (with the
' exception of excluding all days of week), from 0 to 6 days. If
' ExcludeDaysOfWeek = 65 (Sunday + Saturday), the result is the same as
' NETWORKDAYS.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
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
    ' invalid value for ExcludeDaysOfWeek. get out with error.
    NetWorkdays2 = CVErr(xlErrNum)
    Exit Function
End If

If StartDate <= 0 Or EndDate <= 0 Then
    ' invalid date. get out with error.
    NetWorkdays2 = CVErr(xlErrNum)
    Exit Function
End If

' set the value used for the Step in
' the For loop.
If StartDate <= EndDate Then
    Stp = 1
Else
    Stp = -1
End If

For TestDate = StartDate To EndDate Step Stp
    ' get the bit pattern of the weekday of TestDate
    TestDayOfWeek = 2 ^ (Weekday(TestDate, vbSunday) - 1)
    If (TestDayOfWeek And ExcludeDaysOfWeek) = 0 Then
        ' do not exclude this day of week
        If IsMissing(Holidays) = True Then
            ' count day
            Count = Count + 1
        Else
            Exclude = False
            ' holidays provided. test date for holiday.
            If IsObject(Holidays) = True Then
                ' assume Excel.Range
                For Each Holiday In Holidays
                    If Holiday.Value = TestDate Then
                        Exclude = True
                        Exit For
                    End If
                Next Holiday
            Else
                ' not an Excel.Range
                If IsArray(Holidays) = True Then
                    For Each Holiday In Holidays
                        If Int(Holiday) = TestDate Then
                            Exclude = True
                            Exit For
                        End If
                    Next Holiday
                Else
                    ' not an array or range, assume single value
                    If TestDate = Holidays Then
                        Exclude = True
                    End If
                End If
            End If
            If Exclude = False Then
                Count = Count + 1
            End If
        End If
    Else
        ' excluded day of week. do nothing
    End If
Next TestDate
' return the result, positive or negative based on Stp.
NetWorkdays2 = Count * Stp

End Function

This page last updated: 13-March-2011.