I have an worksheet with cell C9=8:30 and D9=17.30 . Then in another cell i
compute the number of hours above 9 that i have been working by
=HOUR((D9-C9)-TIME(9,0,0)). For the above combination i get an error #NUM!.
If i do the same in 2 steps say D10=(D9-C9)-(TIME(9,0,0)) and D11=HOUR(D10)
evrything is fine. Can you tell me why and if there is any way to avoid it?
It's a rounding error due to the manner in which Excel (and other SS's) handle
the IEEE double precision specifications.
If, in D10, instead of entering:
=(D9-C9)-(TIME(9,0,0))
you were to enclose that in parentheses:
=((D9-C9)-(TIME(9,0,0)))
as you would before adding the HOUR function, you would see the same error as
that calculation is returning: -5.5511151231257800E-17
The HOUR function then returns an error since the value it is looking at is
negative.
You can work around it by ROUNDing your result.
=HOUR(ROUND(D9-C9-TIME(9,0,0),5))
But there's no real need to use the HOUR function. As a matter of fact, you
can probably eliminate both the HOUR and ROUND functions, and also not have to
worry about the fact that the HOUR function will only return numbers 0-23 by
using:
=24*(D9-C9-TIME(9,0,0))
or, if you must have an integer result:
=TRUNC(24*(D9-C9-TIME(9,0,0)))
The TRUNC function will give a zero for the very small negative numbers that an
apparent equality sometimes returns.
--ron