This Page: www.cpearson.com/excel/BetterWorkday.aspx

Last Updated: 06-Nov-2013

Copyright 1997 - 2014, Charles H. Pearson

Site Last Updated: 13-Nov-2016

Advanced Software

Design And Development

Design And Development

Office Integration Projects
NET Programming
XML Development

Search The Site:

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. |