I have a list of date ranges, start dates to finish dates, they overlap and
sometimes there are date gaps in between.
My question is how do i find out the network days.
example:
Start Date End date ...... start date end date..... start date end date
4/2/08 5/2/08 4/2/08 6/2/08 7/2/08 8/2/08
the dates are in rows and not columns so i need a formula to tell me total
networkdays between all them days.
Networkdays won't work on array arguments.
One solution would be to use a UDF that would use NetWorkdays and apply it
sequentially to each range.
To enter this, <alt-F11> opens the VBEditor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.
THEN you MUST select Tools/References and check atpvbaen.xls from the list of
available references.
========================================
Function NWD(StartDt As Range, EndDt As Range, Optional Holidays As Range)
Dim lTemp As Long
Dim i As Long
Dim dStrtTemp() As Date
Dim dEndTemp() As Date
Dim c As Range
If StartDt.Count <> EndDt.Count Then
NWD = CVErr(xlErrValue)
Exit Function
End If
ReDim dStrtTemp(1 To StartDt.Count)
ReDim dEndTemp(1 To StartDt.Count)
i = 1
For Each c In StartDt
dStrtTemp(i) = c.Value
i = i + 1
Next c
i = 1
For Each c In EndDt
dEndTemp(i) = c.Value
i = i + 1
Next c
If Holidays Is Nothing Then
For i = 1 To UBound(dStrtTemp)
lTemp = lTemp + networkdays(dStrtTemp(i), dEndTemp(i))
Next i
Else
For i = 1 To UBound(dStrtTemp)
lTemp = lTemp + networkdays(dStrtTemp(i), dEndTemp(i), Holidays)
Next i
End If
NWD = lTemp
End Function
====================================
To use this, enter a formula in the form of:
=NWD(StartDt,EndDt,[Holidays])
StartDt can be a discontiguous range, as can EndDt. They could also be named
ranges. Holidays is an optional range.
Example:
=NWD((A1,D1,G1),(B1,E1,H1))
or =NWD(StartDt, EndDt, Holidays)
where
StartDt Refers To: A1,D1,G1
etc.
--ron
