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

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" wrote in message
...
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

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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

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

Luke M
*Remember to click "yes" if this post helped you!*


"Mr.B" wrote:

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" wrote in message
...
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

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.

--
Mr.B


"David Biddulph" wrote:

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" wrote in message
...
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" wrote in message
...
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