vba duration workingdays between two dates in Excel
Q160. In Excel, how can I find the number of working days between 2 dates using VBA?
To do it in Excel, here is the answer:
- Option Explicit
- Sub WorkingDays()
- Dim dtStartDate As Date, dtLastDate As Date
- Dim lngNoofWorkingDays As Long
- dtStartDate = "14/11/2016"
- dtLastDate = "14/03/2017"
- lngNoofWorkingDays = Application.WorkSheetFunction.NetworkDays(dtStartDate, dtLastDate)
- End Sub
a) NetworkDays is a WorkSheet function and not a VBA function. To invoke the function in VBA, "Application.WorkSheetFunction." has to be prefixed to the function.
b) The function also supports a 3rd argument for taking into account holidays while doing the working days computation. For instance, if Holidays list is available in range "H1:H15" in ActiveSheet, then the code becomes
lngNoofWorkingDays = Application.WorkSheetFunction.NetworkDays(dtStartDate, dtLastDate, ActiveSheet.Range("H1:H15"))