Calculate working days in Excel with VBA code Example

NetworkDays

Is a Excel inbuilt function which calculates working days between two dates and returns an Integer value as a result. In this article we will extend the functionality of NetworkDays function to calculate working days by writing VBA code which will work like NetworkDays but will take an additional parameter to exclude holidays as a range.

Syntax

=NETWORKDAYS(startDate, endDate,[holidays])

Example

VBA code

Public Function NetworkDaysWithHolidays(startDate, endDate, Optional oHolidayRange As Range = Nothing) As Long
    NetworkDaysWithHolidays = 0
    'Check start date
    If IsDate(startDate) = False Then
        Exit Function
    End If
    
    'Check end date
    If IsDate(endDate) = False Then
        Exit Function
    End If
    
    'Validate range
    If Not oHolidayRange Is Nothing Then
        'a local variable to cound holidays
        Dim holidaysCount As Integer
        holidaysCount = oHolidayRange.Cells.Count
        
        'Return result
        NetworkDaysWithHolidays = WorksheetFunction.NetworkDays(startDate, endDate) - holidaysCount
    Else
        NetworkDaysWithHolidays = WorksheetFunction.NetworkDays(startDate, endDate)
    End If
End Function

Output

If like please do subscribe to our blogs to keep your self upto date.

Share on Facebook

Tweet