Calculate Working Days Minus Holidays in VBA

I was looking for a function that calculates working days, minus holidays, for a small project I've been working on. I decided to write one myself, but below I've also demonstrated an alternate way that uses the ATP (Analysis ToolPak) functions for VBA.

Function WorkDays(StartDate As Variant, EndDate As Variant) As Long
' adapted from:
' http://www.exceltip.com/st/Calculate_the_count_of_workdays_between_two_dates_using_VBA_in_Microsoft_Excel/523.html
' and http://support.microsoft.com/kb/97757
'
' returns the count of days between StartDate - EndDate minus Saturdays and Sundays
' also checks for holidays (added by Jimmy Pena 6/17/2008)

Dim lCounter As Long
Dim DaysCount As Long
Dim HolidaysList()
Dim ArrMember
Dim bWasFound As Boolean

bWasFound = False

StartDate = DateValue(StartDate)
EndDate = DateValue(EndDate)

' define holiday list as an array which we will check against
' each day; if match is found, do not increase workday count
'
' edit/add/delete as needed
' these are US Federal Holidays
HolidaysList = Array("1/1/2007", "1/15/2007", "2/19/2007", "5/28/2007", "7/4/2007", "9/3/2007", "10/8/2007", "11/12/2007", _
"11/22/2007", "12/25/2007", "1/1/2008", "1/21/2008", "2/18/2008", "5/26/2008", "7/4/2008", "9/1/2008", "10/13/2008", _
"11/11/2008", "11/27/2008", "12/25/2008")

For lCounter = StartDate To EndDate
    If Weekday(lCounter, vbMonday) < 6 Then
        For Each ArrMember In HolidaysList
            If Format(ArrMember, "mm/dd/yyyy") = Format(lCounter, "mm/dd/yyyy") Then
                bWasFound = True
                Exit For
            Else
                bWasFound = False
            End If
        Next ArrMember

        If bWasFound = False Then
            DaysCount = DaysCount + 1
        End If
    End If
Next lCounter
WorkDays = DaysCount
End Function

Here we create an array of holidays, then loop through the dates passed as arguments to the function to see if they are not weekend days. The inner loop checks to see if the date matches one of the holidays in the array variable. If both conditions are met (i.e. a weekday and not a holiday) we add to the count (DaysCount variable).

The Boolean variable is used as a flag inside the loop — if the date matches a holiday date, the flag is raised and the loop immediately exits. We check the flag after the loop is complete and add to the count if it was left False.

To use:

MsgBox WorkDays("1/1/2008", "1/31/2008")

Or since the function returns a Long value, assign the result to a variable

Dim lCountOfDays As Long
lCountOfDays = WorkDays("1/1/2008", "1/31/2008")

Here's another way, using the ATP library for VBA. To set a reference, open the VBE and go to Tools > References and set a reference to "atpvbaen.xls".

You may also need to go back to Excel and go to Tools > Add-Ins and select "Analysis ToolPak – VBA".

Now you can use the NETWORKDAYS function directly in VBA like this:

Dim HolidaysList()

HolidaysList = Array("1/1/2007", "1/15/2007", "2/19/2007", "5/28/2007", "7/4/2007", "9/3/2007", "10/8/2007", "11/12/2007", _
"11/22/2007", "12/25/2007", "1/1/2008", "1/21/2008", "2/18/2008", "5/26/2008", "7/4/2008", "9/1/2008", "10/13/2008", _
"11/11/2008", "11/27/2008", "12/25/2008")

MsgBox networkdays("1/1/2008", "1/31/2008", HolidaysList)

Which should give you the same answer, and in my tests, at approximately the same speed.

Personally I like my way better, but that's only because I worked on it and I hate throwing away old code. Plus it uses built-in functions in case you don't want to add additional object libraries to your VBE. As you can see above, I already have a lot, so anything already built-in to VBA I can use is something I will prefer.

Enjoy,
JP

Related Articles:

• February 9, 2009 -- Calculate working days from start date
• January 9, 2009 -- Do you need to update your Outlook calendar holidays?
• June 24, 2008 -- Calculate Working Hours in VBA