Re: rounding the results of two times

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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. :-)
--
David Biddulph


"Mr.B" <MrB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AD71DE0C-32D6-4A0A-B98F-B95BA54E4526@xxxxxxxxxxxxxxxx
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
--
Mr.B


"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Mr.B" <MrB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1F285DC1-AB2E-4703-A8D1-BC3EF4976F41@xxxxxxxxxxxxxxxx
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...?

--
Mr.B





.

Relevant Pages

  • Re: Decimal
    ... It is even calculating the decimals ... You can see that by clicking on a cell you entered data in and looking at the formula bar... ... You can truncate off any decimal value as if they were never typed in by the user in the first place, or you can round off the typed in value, but then there are two ways to do rounding in a macro, so you have to make sure you specify the right one. ...
    (microsoft.public.excel.misc)
  • Re: Rounding a number to nearest even
    ... That's exactly how I was taught to do rounding in what-ever low-level ... If you round towards an even number instead when the ... and the average rounding error will be 0. ... the nearest whole or half SEK, which means that decimals 25 and 75 are ...
    (comp.lang.python)
  • Re: a print bug?
    ... the first version will favor the correct rounding, ... so even if you round it off, it is still represented the same way.... ... how "print" handles 1.2345 and treating it and printing it as ... If you use 4 decimals after the decimal sign, you get the same result as with a plain print statement. ...
    (comp.lang.python)
  • Re: Rounding a number to nearest even
    ... from mid-way between integers as opposed to for instance always rounding up in those cases: ... If you round towards an even number instead when the decimal is 5, then you will round up half of those times, and round down the other half, and the average rounding error will be 0. ... The standard in Swedish stores, after adding the prices of your goods, is to round the number to the nearest whole or half SEK, which means that decimals 25 and 75 are mid-way between. ...
    (comp.lang.python)
  • Re: rounding the results of two times
    ... remember that I cant round the time before the results because I need to ... binary representation of 8/24 would be, and let us know the answer. ... "Bernard Liengme" wrote: ... decimals - you could use the Increase Decimals tool ...
    (microsoft.public.excel.worksheet.functions)