I would like seek assistance with the NetworkDays function.
I am interested in the last parameter containing holidays. So far I managed to get it working with ranges of cells in a worksheet, however, I would like to avoid referencing cells.
Is there a possibility to use arrays? So far all my attempts have been unsuccessful.
I tried to define:
Dim holidayDates() as Variant
holidayDates = Array(DateSerial(2023,1,1),DateSerial(2023,12,25))
and so on with many dates in between
Then I would call
WorksheetFunction.NetworkDays(.Cells(3,3).Value, .Cells(3,3).Value, holidayDates)
to check for a single day if it is either a weekend or a public holiday. However, this approach does not work. The cell (3,3) contains a date in a non-serial format which works fine without the holiday parameter.
I have also tried using a string with serial date values separated by commas instead but it did not work either. E.g. DateSerial(2023,1,1) & "," & DateSerial(2023,12,25) and so on.
Both approaches have been tested with a normal date format as well as with the DateSerial function.
DateSerial return value (this is a date type) has to be converted to a double type.
Dim holidayDates() As Variant
holidayDates = Array(CDbl(DateSerial(2023, 1, 1)), CDbl(DateSerial(2023, 12, 25)))
With ActiveSheet
WorksheetFunction.NetworkDays .Cells(3, 3).Value, .Cells(3, 3).Value, holidayDates
End With