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:

  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

Description:

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 worksheet functions be accessed in VBA?

2) How can I protect / unprotect WorkSheet using VBA?

3) How can I copy and rename a WorkSheet using VBA?

4) How can I get input from user through a prompt and assign user's input to a cell?

5) How can I hide Formula Bar and Headings using VBA?

6) Import txt file in Excel

7) How can I update a listbox based on data in a list using VBA?

8) How can I set up ListBox using VBA to allow users to select multiple values?

9) How to add a link in a sheet to another sheet

10) How can I clear cell after activating a routine when there is a change in value of a cell?

11) How can I get the count of number of series in a Chart using VBA?

12) How can I add a Timestamp after macro execution?

13) How can I loop through all WorkSheets and get their names using VBA?

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

15) How can I check if a file exists in a folder using VBA?