Determining work hours between dates / hours

  • Thread starter Thread starter Andrew
  • Start date Start date
A

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))))))
 
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.
 
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))))))
 
Back
Top