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.

On Thu, 7 Feb 2008 05:54:02 -0800, Angelo1803
wrote:

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