rounding the results of two times

[Mr.B]

I recently asked how to round a date and time and I got what I was looking
for but I think there may be a flaw in my formula or excel.....
A1 12/5/2008 8:00
A2 12/5/2008 15:45
using the formula
=round((A2-A1)*24*2,0)/2

gives me a result of 7.5 and it should be 8.0 so it is not rounding to he
half hour correctly ..........using the same if I change A1 to 7:00 it shows
9.0 hours and is correct.........so why is the 8 oclock hour rounding
differently......can I fix this...?

 

[Bernard Liengme]

The formula works for me giving 7.5. Try formatting the cell to show more
decimals - you could use the Increase Decimals tool

If I want to see 7:30 I need =round((A2-A1)*24*2,0)/(2*24) and format the
cell as Time

best wishes

 

[Mr.B]

The format is right I need this to figure out the amount of time a product
has been out of cold storage
but 8 til 1545 is 7 hours and 45 minutes this should be rounded to the next
full hour showing 8.0 as the result
using the same format try adding another hour on and then it will round to
the next whole hour
this is confusing me

 

[David Biddulph]

If you want always to round UP, you'd be better off using CELING. If you
want to know why it isn't rounding up when you think you are exactly on the
quarter hour, look at what =(A2-A1)*24 shows when formatted to 15 or so
decimal places.
If you don't understand why it's doing that, work out what the *exact*
binary representation of 8/24 would be, and let us know the answer.

 

[Luke M]

You should be able to just add another ROUND
=ROUND(ROUND((A2-A1)*24*2,0)/2,0)

 

[Mr.B]

I'm not sure what you mean with the binary representation, but try this and
see what you guys think.
A1 12/10/2008 8:00
A2 12/10/2008 11:45

using the formula
=ROUND((A2-A1)*24*2,0)/2
returns a result of 4.0 ................this is correct........I want to
round to the nearest half hour, if I shorten A2 by 1 hour to read 10:45 the
result is 2.5 (not correct)
remember that I cant round the time before the results because I need to
know how long this has been out. so the result of how much time is used
should round like this
00-14 minutes rounds down to the whole hour
15-29 minutes rounds up to the half hour
30-44 minutes rounds down to the half hour
45-59 minutes rounds up to the whole hour

I'm still confused why it does this.
thanks for any and all help.