About nesting the hour function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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?
Click to expand...


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
 
=INT((D9-C9)*24)-9
or
=HOUR(24+D9-C9-TIME(9,0,0))

Try these formulas instead. The problem has to with the fact that a formula
that has a result of less than 0 can't be converted to a serial code for
dates and times.

If you take this formula
=VALUE(D9-C9-TIME(9,0,0))
you get
-5.55111512312578E-17
as a result (because of rounding error)
this number has no meaning when thinking in terms of dates and times as far
as excel is concerned.
 
Back
Top