Networdays

[Dave Bonallack]

Hi,
I have 2 lists of dates that I want to enter into the Networkdays function.
The first is a list of public holidays and factory-closed dates that are common to all employees.
The second is a list specific to each employee, holding that employee's booked leave dates.
I found a thread that suggested using NETWORKDAYS twice, then doing some math on the 2 results, but that produced errors if there were duplicates in the 2 lists.
For example:
Here in Australia we have a national holiday on April 25, so that date will occur in the common list.
John Smith wants 2 weeks off from April 21 thru May 2, so those dates get entered into John's personal list.
When I use NETWORKDAYS on each list, April 25 gets considered twice, which results in an error.

So, how can I ask NETWORKDAYS to consider 2 lists of dates, and not get errors when the same date appears in both lists?

Regards - Dave Bonallack

 

[Aladin Akyurek]

Add the following code to your workbook, using Alt+F11...

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

Now you can invoke:

=NETWORKDAYS(date1,date2,ARRAYUNION(CommonList,IndividualList))

 

[Dave Bonallack]

Thanks Aladin. That works great! Gives me much more flexibility than I had before.
The UDF seems to create a union of the two ranges. Even though the new (unionized) range contains duplicates, NETWORDAYS ignores for them.
As a matter of interest, I tried that with just with the standard spreadsheet function, putting duplicates in the holiday dates list. NETWORDAYS ignores them there too.
Very good solution. Thanks again.
Dave.

 

[Aladin Akyurek]

Thanks Aladin. That works great! Gives me much more flexibility than I had before.

You are welcome.

The UDF seems to create a union of the two ranges. Even though the new (unionized) range contains duplicates, NETWORDAYS ignores for them.

Yep.

As a matter of interest, I tried that with just with the standard spreadsheet function, putting duplicates in the holiday dates list. NETWORDAYS ignores them there too.
Very good solution. Thanks again.
Dave.

Good thinking. I tested that too and observed that result when I thought of ARRAYUNION. Thanks for the explanatory feedback.