rounding is not consistant

[Mr.B]

I have asked this before, but I'm still having trouble understanding this.
For example I work in Pharmacuticles and I need to know how long a drug has
been out of the cold room. (I need to record this in hours rounding to the
nearest half hour) the exact rounding method is as follows.
00-14 minutes rounds down to 0.0
15-29 minutes rounds up to 0.5
30-44 minutes rounds down to 0.5
45-59 minutes rounds up to 1.0

this is the problem
A24= 12/10/08 7:00 (out time)
A23= 12/10/08 15:15 (in time)

=ROUND((A24-A23)*24*2,0)/2
results in 8.5 hours (this is correct)
so why is it if A24 gets moved 1 hour (8:00) the result moves 1.5 hours
(7.0)
SOMEONE PLEASE HELP!!!!
Thanks

 

[Stefi]

I think you mixed in and out times. If out time is really in A24 and in time
is in A23 then time out of the cold room is not A24-A23 but A23-A24.

=ROUND((A23-A24)*24*2,0)/2

returned for me the right 7.0 with 8:00 in A24.

Regards,
Stefi

„Mr.B†ezt írta:

 

[Mr.B]

I'm sorry I did mix up the cells A24 is the in time and A23 is the out time
but am I doing something wrong. using the formula below I don't understand
why if cell A23 is 07:00 the results are 8.5
and just moving the the time 1 hour to 08:00 it results in 7.0 (1 and a half
hour)
Am I just confused or is something not right here.
it seems that by moving just the hour it would only move the amount of hours
in the results.
thanks

 

[Shane Devenshire]

Hi,

Computers work in binary, we work in decimals

The difference between 7 and 15:15 is exactly 0.34375
but the difference between 8 and 15:15 is approximently
0.302083333328483

Try:
ROUND((ROUND(A2,3)-ROUND(A1,3))*24,0)
where A2 is the time in and A1 is the time out

Here is everything you need to know about this issue (and more):

http://support.microsoft.com/kb/78113/en-us
http://support.microsoft.com/kb/42980
http://support.microsoft.com/kb/214118
http://www.cpearson.com/excel/rounding.htm
http://docs.sun.com/source/806-3568/ncg_goldberg.html

If this helps, please cliick the Yes button.

Cheers,
Shane Devenshire