NETWORKDAYS question

[Guest]

Does NETWORKDAYS work with arrays?

The third column uses NETWOKDAYS(Q3,P3). The fourth column uses the ranges
(either as named ranges or Q3:Q6, P36) and the array command
(Ctrl+Shift+Enter). I cannot get any permutation of the fourth column to
work and I suspect NETWORKDAYS does not work with arrays. Please, someone
tell me I'm wrong!

5/10/2005 5/12/2005 3 #VALUE!
4/28/2005 6/10/2005 32 #VALUE!
4/25/2005 6/15/2005 38 #VALUE!
4/8/2005 8/8/2005 87 #VALUE!

 

[Domenic]

As far as I know, NETWORKDAYS doesn't work with arrays...

 

[Guest]

I am trying to calculate a monthly average of workdays between the dates in
these columns. I'd like to do all calculations on another (protected)
worksheet as multiple users are entering the dates. For most of the other
data collected I accomplished this with array formulae. Do you have any idea
how I might do it with this one?
Thanks,
Cassandra

 

[Domenic]

Can you provide the expected results for that fourth column?

 

[Guest]

Expected results are in the third column. They are the result of
=Networkdays(col1row1, col2row2).

 

[Ron Rosenfeld]

Does NETWORKDAYS work with arrays?

The third column uses NETWOKDAYS(Q3,P3). The fourth column uses the ranges
(either as named ranges or Q3:Q6, P36) and the array command
(Ctrl+Shift+Enter). I cannot get any permutation of the fourth column to
work and I suspect NETWORKDAYS does not work with arrays. Please, someone
tell me I'm wrong!

5/10/2005 5/12/2005 3 #VALUE!
4/28/2005 6/10/2005 32 #VALUE!
4/25/2005 6/15/2005 38 #VALUE!
4/8/2005 8/8/2005 87 #VALUE!

I don't believe NETWORKDAYS will accept arrays as arguments for start_date or
end_date; nor will it output arrays as a result.

However, you can use this VBA routine which will take the arrays and feed them
"one at a time" to the networkdays function, and then output the result as an
array. You can then "AVERAGE" or "SUM" the result.

Naming your ranges StartDates and EndDates, the formula:

=AVERAGE(NWDarray(StartDates,EndDates)) will return 40

To use this, you must enter VBA code and also set a Reference.

1. <alt-F11> opens the VB Editor.
2. Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.
4. On the top menu bar, select Tools/References and check atpvbaen.xls (this
is the Analysis Tool Pak.

Now you can use formulas like the above to work on arrays.

Note that the way this UDF is written, StartDate and EndDate must be in single
column ranges. I did not write to handle array constants, or ranges set out in
rows, although it could certainly be modified to do so, if that is a
requirement.

The Holidays argument can be used, optionally.

==================================
Option Explicit

Function NWDArray(StartDate As Range, EndDate As Range, Optional Holidays As
Range)
Dim i As Long
Dim Temp()

'List of start and end dates must be entered in arrays of _
one column each

If StartDate.Count <> EndDate.Count Or _
StartDate.Columns.Count <> 1 Or _
EndDate.Columns.Count <> 1 Then
NWDArray = CVErr(xlErrValue)
End If

If Holidays Is Nothing Then
For i = 1 To StartDate.Count
If i > StartDate.Count Then Exit For
ReDim Preserve Temp(i - 1)
Temp(UBound(Temp)) = networkdays(StartDate.Cells(i, 1),
EndDate.Cells(i, 1))
Next i
Else
For i = 1 To StartDate.Count
If i > StartDate.Count Then Exit For
ReDim Preserve Temp(i - 1)
Temp(UBound(Temp)) = networkdays(StartDate.Cells(i, 1),
EndDate.Cells(i, 1), Holidays)
Next i
End If

NWDArray = Temp
End Function
===================================


--ron