Determining work hours between dates / hours

[Andrew]

I am currently using the following formula to calculate work hours. As it
happens on the time of measurement not all requests are finalised and I am
getting a #NUM! calculation. This have an impact on my total calculations.
How can I eliminate #NUM!

=IF(AND(INT(O94)=INT(V94),NOT(ISNA(MATCH(INT(O94),List!A$9:A$24,0)))),0,ABS(IF(INT(O94)=INT(V94),ROUND(24*(V94-O94),2),
(24*(X94-W94)*
(MAX(NETWORKDAYS(O94+1,V94-1,List!A$9:A$24),0)+
INT(24*(((V94-INT(V94))-
(A94-INT(O94)))+(X94-W94))/(24*(X94-W94))))+
MOD(ROUND(((24*(V94-INT(V94)))-24*W94)+
(24*X94-(24*(O94-INT(O94)))),2),
ROUND((24*(X94-W94)),2))))))

 

[David Biddulph]

If you've got errors in a formula that long, the best bet for debugging it
is usually to break it down into manageable sized chunks until you find
where your problem is.

 

[David Biddulph]

You may want to start with your NETWORKDAYS function. If you have given it
a negative number as a second argument, that would return #NUM!

If that is the problem, you may want to trap for that condition, such as:
=IF(OR(V94<1,AND(INT(O94)=INT(V94),NOT(ISNA(MATCH(INT(O94),List!A$9:A$24,0))))),0,ABS(IF(INT(O94)=INT(V94),ROUND(24*(V94-O94),2),
(24*(X94-W94)*
(MAX(NETWORKDAYS(O94+1,V94-1,List!A$9:A$24),0)+
INT(24*(((V94-INT(V94))-
(A94-INT(O94)))+(X94-W94))/(24*(X94-W94))))+
MOD(ROUND(((24*(V94-INT(V94)))-24*W94)+
(24*X94-(24*(O94-INT(O94)))),2),
ROUND((24*(X94-W94)),2))))))

 

[Andrew]

Thanks David - On a glance it look if it will work - I will test further