Excel VBA – NetworkDays Formula

In Excel, there are plenty of ways to calculate the differences between days. However, the NetworkDays Formula includes weekends and may include the holidays, which would be different for each country/province. Thus, it is useful!

Here is how to use the NetworkDays formula in a new spreadsheet in Excel with VBA:

Option Explicit

Public Sub PrintNetworkDays()

    Dim dtStartDate     As Date
    Dim dtEndDate       As Date
    Dim rngHolidays     As Range

    dtStartDate = DateSerial(2017, 7, 1)
    dtEndDate = DateSerial(2017, 8, 1)

    Set rngHolidays = ActiveSheet.Range("A:A")

    rngHolidays(1, 1) = DateSerial(2017, 7, 5)
    rngHolidays(2, 1) = DateSerial(2017, 7, 6)
    rngHolidays(3, 1) = DateSerial(2017, 7, 7)
    rngHolidays(4, 1) = DateSerial(2017, 7, 8)
    rngHolidays(5, 1) = DateSerial(2017, 7, 9)

    Debug.Print WorksheetFunction.NetworkDays(dtStartDate, dtEndDate, rngHolidays)

End Sub

In general it would take only the newly populated values in column A and thus it would give you a nice result. The Microsoft documentation is here.