work hours calculation Excel 2003

[Andrew]

I am currently using the following formula to calculate work hours. However
when I do not have an end date / time it very large number is calculated

In the example below the start date is 2008/08/11 11:17 with no end date and
the calculation is 711311.22

How can I change the foluma to have a zero if no end date is completed?

IF(OR(O105<1,AND(INT(V105)=INT(O105),NOT(ISNA(MATCH(INT(V105),List!A$9:A$24,0))))),0,ABS(IF(INT(V105)=INT(O105),ROUND(24*(O105-V105),2),
(24*(X105-W105)*
(MAX(NETWORKDAYS(V105+1,O105-1,List!A$9:A$24),0)+
INT(24*(((O105-INT(O105))-
(A105-INT(V105)))+(X105-W105))/(24*(X105-W105))))+
MOD(ROUND(((24*(O105-INT(O105)))-24*W105)+
(24*X105-(24*(V105-INT(V105)))),2),
ROUND((24*(X105-W105)),2))))))

 

[Andrew]

I found the problem - sorted

 

[Bob Bridges]

Yeah, but I already put this work into understanding that formula, so in all
decency you gotta read my answer anyway --

There are a few ways to simplify this formula:

1) In the subformula
24*(((O105-INT(O105))-(A105-INT(V105)))+(X105-W105))/(24*(X105-W105)), you're
unnecessarily multiplying both terms by 24. 24xA/24xB is the same as A/B,
you see. So you can make it
((O105-INT(O105)-(A105-INT(V105))+(X105-W105))/(X105-W105).

2) Then again, if you're diving (A-B+C)/C, you can just as well make it
(A-B)/C + 1, thus making Excel calculate X105-W105 half as often:
((O105-INT(O105)-(A105-INT(V105)))/(X105-W105)+1

3) There are one or two other places where you multiply a few additive terms
by 24, where you could have added and subtracted them all and THEN multiplied
by 24, just to save work for Excel and make the formula easier for a human to
read. All in all, I get this:

IF(
OR(
O105<1,
AND(
INT(V105)=INT(O105),
NOT(ISNA(
MATCH(INT(V105),List!A$9:A$24,0)
)))),
0,
ABS(
IF(
INT(V105)=INT(O105),
ROUND((O105-V105)*24,2),
((X105-W105)*24*
(MAX(NETWORKDAYS(V105+1,O105-1,List!A$9:A$24),0)
+INT(((O105-INT(O105))-(A105-INT(V105)))/(X105-W105))+1
)
+MOD(
ROUND((O105-INT(O105)-W105+X105-(V105-INT(V105)))*24,2),
ROUND((X105-W105)*24,2)
)
)
)
)
)

Now, let's see about your question...well, you didn't say which columns are
your start and end dates (or rather timestamps), but I suppose they're W and
X, respectively. I think the solution is to have a helping column, say AA,
with the formula =IF(X105="",0,X105-W105); after that replace every
occurrence of "X105-A105" in your main formula (I see four of them) with
"AA105", like this:

IF(
OR(
O105<1,
AND(
INT(V105)=INT(O105),
NOT(ISNA(
MATCH(INT(V105),List!A$9:A$24,0)
)))),
0,
ABS(
IF(
INT(V105)=INT(O105),
ROUND((O105-V105)*24,2),
(AA105*24*
(MAX(NETWORKDAYS(V105+1,O105-1,List!A$9:A$24),0)
+INT(((O105-INT(O105))-(A105-INT(V105)))/AA105)+1
)
+MOD(
ROUND((O105-INT(O105)+AA105-(V105-INT(V105)))*24,2),
ROUND(AA105*24,2)
)
)
)
)
)

That way it calculates the days worked today (or wherever a day still isn't
finished) as 0 time put in. Although if you wanted to you could make it show
the hours put in SO FAR, ie =IF(X105="",NOW(),X105)-W105.

A few other helping columns wouldn't hurt, either. If AB105 were set to
O105-INT(O105), and perhaps AC105 to INT(V105), and AD105 to AC105=INT(O105),
you could have

IF(
OR(
O105<1,
AND(
AD105,
NOT(ISNA(
MATCH(AC105,List!A$9:A$24,0)
)))),
0,
ABS(
IF(
AD105,
ROUND((O105-V105)*24,2),
(AA105*24*
(MAX(NETWORKDAYS(V105+1,O105-1,List!A$9:A$24),0)
+INT((AB105-(A105-AC105))/AA105)+1
)
+MOD(
ROUND((AB105+AA105-(V105-AC105))*24,2),
ROUND(AA105*24,2)
)
)
)
)
)

....which is still tolerably complex but at least better.