Calculate the count of workdays between two dates using VBA in Microsoft Excel

In this article, we will create a User Defined Function (UDF) to count the number of working days between the specified dates, including or excluding Saturdays and Sundays as week offs.

Raw data for this example consists of a start date and an end date. We want to count the number of working days between these dates.

We have specified the holiday dates in the column A in “Holidays” sheet.

Excel has inbuilt function, NETWORKDAYS to count the number of working days between the interval.

Syntax of NETWORKDAYS function

NETWORKDAYS(StartDate, EndDate,[Holidays])

This function will exclude the date specified in the Holidays list, while counting the number of working days.

This function considers Saturdays and Sundays as Week offs by default, so we can't count the number of working days, in case we have only one Week off.

We have created “CountWorkingDays” custom function to count the number of working days between the interval. This custom function handles the problem of the NETWORKDAYS function. In this function, we can count the number of working days even if there is only one Week off either on Saturday or Sunday.

Syntax of custom function

CountWorkingDays (StartDate, EndDate, InclSaturdays, InclSundays)

InclSaturdays and InclSundays are optional parameters. By default, both have TRUE values. To change Saturdays and Sundays into working days, change the value of the respective parameter to FALSE.

Microsoft introduced NETWORKDAYS.INTL function with Excel 2010. This function handles the problem of NETWORKDAYS function. In this function, we can specify the Week off days. We can either specify one or two days as Week off.

Syntax of NETWORKDAYS.INTL function

NETWORKDAYS.INTL(StartDate, EndDate, [Weekend], [Holidays])

In the weekend parameter, we can specify Week off days.

In this example, we will use all the above three functions to count the number of working days.

Logic explanation

In the “CountWorkingDays” function, firstly we check whether given date in the parameter exists in the specified holiday list. If the date exists in the holiday list, then that day is not counted in the number of working days. If the date doesn’t exists in the holiday list, then check whether given date is Saturday or Sunday. Based on the input parameter provided, check whether to include or exclude Saturdays or Sundays as holiday.

Code explanation

Set RngFind = Worksheets("Holidays").Columns(1).Find(i)

Above code is used to find the location where the specified date exists in the holiday list.

If Not RngFind Is Nothing Then

GoTo ForLast

End If

Above code is used to check whether specified date exists in the holiday list. If condition returns TRUE, then that day is not counted in the number of working days.

Please follow below for the code

Option Explicit

Function CountWorkingDays(StartDate As Long, EndDate As Long, Optional InclSaturdays As Boolean = True, _
    Optional InclSundays As Boolean = True)

'Declaring variables
Dim RngFind As Range
Dim i As Long

For i = StartDate To EndDate
    
    On Error Resume Next
    
    'Finding the location where the specified date exist in the Holidays sheet
    Set RngFind = Worksheets("Holidays").Columns(1).Find(i)
    
    On Error GoTo 0
    
    'Checking whether it is holiday on the given date
    If Not RngFind Is Nothing Then
        GoTo ForLast
    End If
    
    'Checking whether it is Saturday on given date
    If InclSaturdays Then
        If Weekday(i, 2) = 6 Then
            GoTo ForLast
        End If
    End If
    
    'Checking whether it is Sunday on given date
    If InclSundays Then
        If Weekday(i, 2) = 7 Then
            GoTo ForLast
        End If
    End If
    
    CountWorkingDays = CountWorkingDays + 1
    
ForLast:

Next

End Function

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com