Table of Contents
Do you want to calculate the number of working days between two dates with VBA? If so, one of the most useful functions you can use is the NetworkDays function. This article will focus on how to use the NetworkDays function in VBA, with a step-by-step example of how to use it in a macro. Read on to learn more about this useful function and how to use it in your own VBA code.
You can use the NetworkDays method in VBA to find the number of whole working days between specific start and end dates.
Note that working days exclude weekends and holidays.
Here is one common way to use this method in practice:
Sub CalculateNetworkDays()
Dim i As Integer
For i = 2 To 9
Range("C" & i) = WorksheetFunction.NetworkDays(Range("A" & i), Range("B" & i))
Next i
End Sub
This particular example calculates the number of working days between the start dates in the range A2:A9 and the end dates in the range B2:B9 and displays the results in the range C2:C9.
The following example shows how to use this syntax in practice.
Example: How to Use NetworkDays in VBA
Suppose we have the following list of start dates and end dates in Excel:
Suppose we would like to use the NetworkDays method in VBA to calculate the number of whole working days between the start and end dates in each row.
We can create the following macro to do so:
Sub CalculateNetworkDays()
Dim i As Integer
For i = 2 To 9
Range("C" & i) = WorksheetFunction.NetworkDays(Range("A" & i), Range("B" & i))
Next i
End Sub
When we run this macro, we receive the following output:
Column C shows the number of whole working days between the start and end dates in each row.
For example:
- The number of working days between 1/2/2023 and 1/3/2023 is 2. (since both of these dates are weekdays).
- The number of working days between 1/5/2023 and 1/8/2023 is 2.
- The number of working days between 1/10/2023 and 1/20/2023 is 9.
Note: You can find the complete documentation for the NetworkDays method in VBA .