Vba duration workingdays between two dates in Excel

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub WorkingDays()
  3. Dim dtStartDate As Date, dtLastDate As Date
  4. Dim lngNoofWorkingDays As Long
  5. ''
  6. dtStartDate = "14/11/2016"
  7. dtLastDate = "14/03/2017"
  8. ''
  9. lngNoofWorkingDays = Application.WorkSheetFunction.NetworkDays(dtStartDate, dtLastDate)
  10. ''
  11. 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"))


You can find similar Excel Questions and Answer hereunder

1) How can I set non-contiguous print area using VBA?

2) Introduction to tables in Excel VBA and how to use tables, create tables in VBA

3) The scenario manager in Excel VBA allows to explore various scenarios in a very easy way

4) How do I add a shape using VBA?

5) How can I set FreezePanes in a certain range using VBA?

6) Here a explanation about the global seek function in VBA. Goal Seek is another tool under What If analysis that does a unique function as Scenario Manager.

7) How can I export a WorkSheet as a PDF using VBA?

8) How to create Pivot table in excel VBA

9) How can I find the last used cell in a Column in VBA?

10) Write to text file without quotes in vba in Excel


Here the previous and next chapter