A Better WORKDAY Function

This page describes a VBA function that can replace Excel's WORKDAY function.

Introduction

Excel provides a function named WORKDAY that returns a date that is some given number of weekdays, optionally excluding holidays, after a given date. In Excel 2003 and earlier, this function is part of the Analysis ToolPak add-in. In Excel 2007 and later, it is, like all ATP functions, native to Excel.

Excel's WORKDAY function suffers from a significant shortcoming. It is hard-coded to exclude Saturdays and Sundays. You cannot specify other or more days of the week to exclude from the calculation. This page describes a VBA function named Workday2 that allows you to specify any days of week to exclude from the calculation.

You can download an example workbook or just the VBA bas module file containing the code.

The function declaration for Workday2 is:

    Public Function Workday2(StartDate As Date, DaysRequired As Long, _
            ExcludeDOW As EDaysOfWeek, Optional Holidays As Variant) As Variant

StartDate is the date from which the counting of days begins. DaysRequired is the number of work days after StartDate whose is to be returned. ExcludeDOW is a value that indicates which days of the week to exclude. This is explained below. Holidays is an array or range contains the dates of holidays to exclude from the calculation.

The EDaysOfWeek is an Enum data type assigning values to each day of the week. It is defined as:

Enum EDaysOfWeek
    Sunday = 1      ' 2 ^ (vbSunday - 1)
    Monday = 2      ' 2 ^ (vbMonday - 1)
    Tuesday = 4     ' 2 ^ (vbTuesday - 1)
    Wednesday = 8   ' 2 ^ (vbWednesday - 1)
    Thursday = 16   ' 2 ^ (vbThursday - 1)
    Friday = 32     ' 2 ^ (vbFriday - 1)
    Saturday = 64   ' 2 ^ (vbSaturday - 1)
End Enum

As you can see, each value assigned to a day of week is a power of 2. Each day of week turns on one bit of the Enum value. This allows you to specify more than one day of week by simply adding the corresponding values together. For example, to exclude Tuesdays and Fridays, you would use Tuesday + Friday. Since Tuesday is equal to 4, it has a binary representation of 00000100. In binary, Friday, which equals 32, is 00100000. When these are added together, the result is 00100100. This shows that the bits for Tuesday and Friday are turned on, and all the other day's bits are off. Note that the values used for the weekdays are not the same as the constants used by Excel and by VBA (the relationship between the Enum's values and the VBA values is shown in the comments within the Enum). If you specify that all days of week are to be excluded (ExcludeDOW >= 127), the function will return a #VALUE error.

For example, to find the date that is 15 days past 5-January-2009, excluding Tuesdays and Fridays, you can use =WORKDAY2(DATE(2009,1,5),15,4+32). The result is 26-January-2009. To exclude holidays, put the dates to exclude in some range of cells, say K1:K10, and pass that range as the final parameter to Workday2:
=WORKDAY2(DATE(2009,1,5),15,4+32,K1:K10).

The complete code for the Workday2 function is shown below:

'''''''''''''''''''''''''''''''''''''''''''''''''''''
' EDaysOfWeek
' Days of the week to exclude. This is a bit-field
' enum, so that its values can be added or OR'd
' together to specify more than one day. E.g,.
' to exclude Tuesday and Saturday, use
' (Tuesday+Saturday), or (Tuesday OR Saturday)
'''''''''''''''''''''''''''''''''''''''''''''''''''''
Enum EDaysOfWeek
    Sunday = 1      ' 2 ^ (vbSunday - 1)
    Monday = 2      ' 2 ^ (vbMonday - 1)
    Tuesday = 4     ' 2 ^ (vbTuesday - 1)
    Wednesday = 8   ' 2 ^ (vbWednesday - 1)
    Thursday = 16   ' 2 ^ (vbThursday - 1)
    Friday = 32     ' 2 ^ (vbFriday - 1)
    Saturday = 64   ' 2 ^ (vbSaturday - 1)
End Enum

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Workday2
' This is a replacement for the ATP WORKDAY function. It
' expands on WORKDAY by allowing you to specify any number
' of days of the week to exclude.
'   StartDate       The date on which the period starts.
'   DaysRequired    The number of workdays to include
'                   in the period.
'   ExcludeDOW      The sum of the values in EDaysOfWeek
'                   to exclude. E..g, to exclude Tuesday
'                   and Saturday, pass Tuesday+Saturday in
'                   this parameter.
'   Holidays        an array or range of dates to exclude
'                   from the period.
' RESULT:           A date that is DaysRequired past
'                   StartDate, excluding holidays and
'                   excluded days of the week.
' Because it is possible that combinations of holidays and
' excluded days of the week could make an end date impossible
' to determine (e.g., exclude all days of the week), the latest
' date that will be calculated is StartDate + (10 * DaysRequired).
' This limit is controlled by the RunawayLoopControl variable.
' If DaysRequired is less than zero, the result is #VALUE. If
' the RunawayLoopControl value is exceeded, the result is #VALUE.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function Workday2(StartDate As Date, DaysRequired As Long, _
    ExcludeDOW As EDaysOfWeek, Optional Holidays As Variant) As Variant

Dim N As Long ' generic counter
Dim C As Long ' days actually worked
Dim TestDate As Date ' incrementing date
Dim HNdx As Long ' holidays index
Dim CurDOW As EDaysOfWeek ' day of week of TestDate
Dim IsHoliday As Boolean ' is TestDate a holiday?
Dim RunawayLoopControl As Long ' prevent infinite looping
Dim V As Variant    ' For Each loop variable for Holidays.

If DaysRequired < 0 Then
    ' day required must be greater than or equal
    ' to zero.
    Workday2 = CVErr(xlErrValue)
    Exit Function
ElseIf DaysRequired = 0 Then
    Workday2 = StartDate
    Exit Function
End If

If ExcludeDOW >= (Sunday + Monday + Tuesday + Wednesday + _
            Thursday + Friday + Saturday) Then
    ' all days of week excluded. get out with error.
    Workday2 = CVErr(xlErrValue)
    Exit Function
End If

' this prevents an infinite loop which is possible
' under certain circumstances.
RunawayLoopControl = DaysRequired * 10000
N = 0
C = 0
' loop until the number of actual days worked (C)
' is equal to the specified DaysRequired.
Do Until C = DaysRequired
    N = N + 1
    TestDate = StartDate + N
    CurDOW = 2 ^ (Weekday(TestDate) - 1)
    If (CurDOW And ExcludeDOW) = 0 Then
        ' not excluded day of week. continue.
        IsHoliday = False
        ' test for holidays
        If IsMissing(Holidays) = False Then
            For Each V In Holidays
                If V = TestDate Then
                    IsHoliday = True
                    ' TestDate is a holiday. get out and
                    ' don't count it.
                    Exit For
                End If
            Next V
        End If
        If IsHoliday = False Then
            ' TestDate is not a holiday. Include the date.
            C = C + 1
        End If
    End If
    If N > RunawayLoopControl Then
        ' out of control loop. get out with #VALUE
        Workday2 = CVErr(xlErrValue)
        Exit Function
    End If
Loop
' return the result
Workday2 = StartDate + N

End Function

The NETWORKDAYS Function

The NETWORKDAYS function, which returns the number of weekdays between two dates, optionally excluding holidays, suffers from the same shortcoming as WORKDAY; that is, Saturday and Sunday are hard coded into the function and cannot be changed. For a solution to this problem that doesn't rely on WORKDAY or the Analysis ToolPak add-in, see the better NETWORKDSAYS page.

This page last updated: 18-August-2009.