On Fri, 15 Apr 2005 10:51:20 +0300, "Arvi Laanemets" wrote:

Hi

NETWORKDAYS counts working days in given date interval, assuming that in
week are 5 working days, and that free days are Saturday and Sunday. This
assumption is definitely wrong for earlyer periods, and probably also
currently in some countries. Has someone an UDF available, which is an
analog for NETWORKDAYS, but with free days (or workdays) numbers as an
additional array parameter - like holidays.

Thanks in advance.

Arvi,

If your weekend days are two, and contiguous, you can modify the NETWORKDAYS
formula by adding/subtracting the appropriate number of days from StartDt and
EndDt.

Here is a UDF, which allows zero to three weekend days which are not
necessarily contiguous. The input arguments should be obvious:

=============================================
Function NWrkDays(StartDate As Date, EndDate As Date, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 0, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0) As Long
' Sunday = 1; Monday = 2; ... Saturday = 7


Dim i As Long
Dim Count As Long
Dim H As Variant
Dim w As Long
Dim SD As Date, ED As Date
Dim DoHolidays As Boolean
Dim NegCount As Boolean

DoHolidays = Not (Holidays Is Nothing)

SD = StartDate: ED = EndDate
If SD ED Then
SD = EndDate: ED = StartDate
NegCount = True
End If

w = Weekday(SD - 1)
For i = SD To ED
Count = Count + 1
w = (w Mod 7) + 1
Select Case w
Case WeekendDay_1, WeekendDay_2, WeekendDay_3
Count = Count - 1
Case Else
If DoHolidays Then
If IsNumeric(Application.Match(i, Holidays, 0)) Then _
Count = Count - 1
End If
End Select
Next i
If NegCount = True Then Count = -Count
NWrkDays = Count
End Function
================================
--ron