Array Formula

[Erin Searfoss]

Can anyone tell what I'm doing wrong here? If (on another sheet) IDTest =
$A$2:$A2, BegDateTest = $B$2:$B$2, and FinDateTest = $C$2:$C$2 the following
formula evaluates to 23. If IDTest = $A$3:$A3, BegDateTest = $B$3:$B$3, and
FinDateTest = $C$3:$C$3 the formula evaluates to 0. However, if IDTest =
$A$2:$A3, BegDateTest = $B$2:$B$3, and FinDateTest = $C$2:$C$3 the formula
evaluates to -68. I would expect 0 + 23. Anyone know why it isn't
evaluating to 23?

=SUM(IF((BegDateTest>D1)+(FinDateTest<=C1)>0,0,(IDTest=A2)*NETWORKDAYS(MAX(C1,BegDateTest),MIN(D1,FinDateTest))))

Any suggestions would be appreciated. Thanks.

Erin

 

[Erin Searfoss]

I tried getting rid of the IF as well with the same result. The values in
A2, C1 and D1 are 02548, 12/31/07 and 1/31/08, repectively while the second
sheet looks like this.

ID Beg Date Fin Date
02548 01/01/08 05/04/08
02548 05/05/08 08/31/08


=SUM((-(BegDateTest>D1)-(FinDateTest<=C1)+1)*(AttyIDTest=A2)*NETWORKDAYS(MAX(C1,BegDateTest),MIN(D1,FinDateTest)))

 

[Erin Searfoss]

Nevermind. I figured out that the Max and Min were not working the way I was
thinking and the Networkdays doesn't work with array formulas at all.
Instead I wrote a custom function (see below). I try to avoid custom
function so If anyone has an excel formula to calculate work days in an array
formula please let me know. Thanks.

Function WorkDays(BegDates, EndDates, AttyID, BegPer, EndPer, ID, Holidays)
As Single

Dim FirstDate As Date
Dim LastDate As Date
Dim TestDate As Date
Dim SumDays As Single

WorkDays = 0

If TypeName(BegDates) <> "Range" Or TypeName(EndDates) _
<> "Range" Or TypeName(AttyID) <> "Range" Or TypeName(Holidays) <>
"Range" Then GoTo ErrRtn
If BegDates.Cells.Count <> EndDates.Cells.Count Or EndDates.Cells.Count <>
AttyID.Cells.Count _
Then GoTo ErrRtn

For i = 0 To BegDates.Cells.Count
If Not (BegDates(i) > EndPer Or EndDates(i) < EndPer) Then
If AttyID(i) = ID Then
FirstDate = Application.WorksheetFunction.Max(BegPer, BegDates(i))
LastDate = Application.WorksheetFunction.Min(EndPer, EndDates(i))
SumDays = 0
TestDate = FirstDate
Do While TestDate <= LastDate
If Weekday(TestDate) <> 1 And Weekday(TestDate) <> 7 Then
SumDays = SumDays + 1
For j = 0 To Holidays.Cells.Count
If TestDate = Holidays(j) Then SumDays = SumDays
- 1
Next j
End If
TestDate = TestDate + 1
Loop
WorkDays = WorkDays + SumDays
End If
End If
Next i

Exit Function

ErrRtn:
WorkDays = CVErr(xlErrValue)

End Function