Use of NETWORKDAYS function

Question

I have to find the number of working days between two dates which should exclude weekends and National Holidays. I am using function NETWORKDAYS in vba, this excludes weekends but I want to exclude Some National Holidays as well. How to use this function NETWORKDAYS(startDate, endDate, [holidays]) for National holidays. It says it accepts [holidays] as a list. I have all the national holidays in an array. how can I use it with this function via VBA ?? Please find the code snippet.

Public Function dataFromInputSheetHolidayDates() As Variant
Dim holidayDates As Integer
Dim holidaydatesArray(20) As Variant
holidayDates = Sheets("InputSheet").Cells(Rows.Count, "D").End(xlUp).Row
For countDate = 0 To holidayDates - 1
holidaydatesArray(countDate) = Format(Sheets("InputSheet").Cells(countDate + 2, "D").Value, "DD-MMM-YY")
Next countDate
dataFromInputSheetHolidayDates = holidaydatesArray
End Function

holidayList = dataFromInputSheetHolidayDates()

Sheets("Estimation").Range("Z" & taskcounter).Formula = "=NETWORKDAYS(X" & 
                   taskcounter & ",Y" & taskcounter &","& holidayList & ")"

Sheets("Estimation").Range("AB" & taskcounter).Formula = "=NETWORKDAYS(X" & 
                  taskcounter & ",AA" & taskcounter &"," & holidayList & ")"

Vityata Vityata · Accepted Answer · 2018-01-29T13:01:39

Change these 2 lines in your code:

Dim holidayDates As Long
holidaydatesArray(countDate) = Sheets("InputSheet").Cells(countDate + 2, "D")

In VBA Integer is up to 32767, which is not quite enough for dates. Furthermore, holidaydatesArray should have a numeric value and not some text format.

Pretty much similar problem as this one - workday holiday argument doesn't accept array

If you are trying to create a flexible formula through VBA, where the holidays are on different worksheet, try this solution:

Public Sub TestMe()
    Dim holidayLists    As Range
    Set holidayLists = Worksheets(2).Range("D1:D10")
    With Worksheets(1)
        .Range("A1").Formula = "=NETWORKDAYS(B1, C1," & holidayLists.Parent.Name & "!" _
                                                      & holidayLists.Address & ")"
    End With
End Sub

There the holidayLists.Parent.Name & "!" & holidayLists.Address would refer correctly to the worksheet's name of the holidays.

Use of NETWORKDAYS function

1 Answers