Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formula
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((BegDateTestD1)+(FinDateTest<=C1)0,0,(ID Test=A2)*NETWORKDAYS(MAX(C1,BegDateTest),MIN(D1,Fi nDateTest)))) Any suggestions would be appreciated. Thanks. Erin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formula
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((-(BegDateTestD1)-(FinDateTest<=C1)+1)*(AttyIDTest=A2)*NETWORKDAYS(M AX(C1,BegDateTest),MIN(D1,FinDateTest))) "Erin Searfoss" wrote: 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((BegDateTestD1)+(FinDateTest<=C1)0,0,(ID Test=A2)*NETWORKDAYS(MAX(C1,BegDateTest),MIN(D1,Fi nDateTest)))) Any suggestions would be appreciated. Thanks. Erin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formula
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 "Erin Searfoss" wrote: 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((-(BegDateTestD1)-(FinDateTest<=C1)+1)*(AttyIDTest=A2)*NETWORKDAYS(M AX(C1,BegDateTest),MIN(D1,FinDateTest))) "Erin Searfoss" wrote: 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((BegDateTestD1)+(FinDateTest<=C1)0,0,(ID Test=A2)*NETWORKDAYS(MAX(C1,BegDateTest),MIN(D1,Fi nDateTest)))) Any suggestions would be appreciated. Thanks. Erin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array formula | Excel Worksheet Functions | |||
Help with Array formula? | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Need Help With Array Formula | Excel Discussion (Misc queries) | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions |