On Mon, 28 Feb 2005 20:30:52 -0000, "Bob Phillips"
wrote:

Hi Ron,

Good spot. I add 1 to the date in E1 and therein lies the problem. If E1
C1 I can add 1, else I need to subtract 1, to maintain the integrity.
Revised version

=SUMPRODUCT(INT((E1-WEEKDAY(E1+IF(E1C1,1,-1)-{2;3;4;5;6})-C1+8)/7))-SUMPROD
UCT(ISNUMBER(MATCH(WEEKDAY(holidays),{2;3;4;5;6}, 0))*(holidays=C1)*(holiday
s<=E1))

--

Well, I can still get it to screw up. For example:

StartDate Saturday, January 01, 2005
EndDate Thursday, December 16, 2004

Holidays
Friday, December 24, 2004
Friday, December 24, 2004
Friday, December 31, 2004
Saturday, January 01, 2005
Sunday, January 02, 2005

Your Formula: -11
NetWorkDays : -10

================================
I've been working on a VB Wrkday (and NetWrkDay)routine that allows, as
written, a maximum of three defined weekend days. It seems to be giving
answers consistent with Excel's Networkday and Workday function.

Myrna helped with a routine that really sped up the NetWrkday routine compared
with my initial attempt. Of course, it does not run as fast as the native
routines, but for reasonable ranges, it might be adequate.

----------------------------------------------

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

'credits to Myrna

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

Function WrkDay(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing, _
Optional WeekendDay_1 As Integer = 0, _
Optional WeekendDay_2 As Integer = 0, _
Optional WeekendDay_3 As Integer = 0) As Date

' Sunday = 1; Monday = 2; ... Saturday = 7

Dim i As Long
Dim TempDate As Date
Dim Stp As Integer
Dim NonWrkDays As Long
Dim Temp As Long, SD As Date, ED As Date

Stp = Sgn(NumDays)

'Add NumDays
TempDate = StartDate + NumDays

'Add Non-Workdays

Do While Abs(NumDays) < Temp
SD = Application.WorksheetFunction.Min(StartDate + Stp, TempDate)
ED = Application.WorksheetFunction.Max(StartDate + Stp, TempDate)

Temp = NWrkDays(SD, ED, Holidays, WeekendDay_1, WeekendDay_2, WeekendDay_3)
TempDate = TempDate + NumDays - Stp * (Temp)
Loop

WrkDay = TempDate
End Function
-------------------------------------


--ron