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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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.