I want 1 not 0.99999999!!

[I heart Excel]

Hello,

My colleagues can select hours off work that then pull through to an index and prints to a virtual printer, using the details in the index.

I seem to have a problem I can't resolve or figure out why!!!

When someone requests -

7.30-8.30 off the total is 1 hour
9.30-10.30 off the total is 1 hour
IF 8.30-9.30 off the total is 0.999999999?!

WHY?! And is there anyway around this problem? I have checked the formatting and it's the same as all the other cells which work absolutly fine.

Thanks,

 

[T. Valko]

Hello,

My colleagues can select hours off work that then pull through to an index and prints to a virtual printer, using the details in the index.

I seem to have a problem I can't resolve or figure out why!!!

When someone requests -

7.30-8.30 off the total is 1 hour
9.30-10.30 off the total is 1 hour
IF 8.30-9.30 off the total is 0.999999999?!

WHY?! And is there anyway around this problem? I have checked the formatting and it's the same as all the other cells which work absolutly fine.

Thanks,

This is due to what's known as a floating point error.

Understanding IEEE floating point errors...

http://support.microsoft.com/kb/42980

How to correct rounding errors in floating point arithmetic...

http://support.microsoft.com/kb/214118

The solution is to round the result. To round to the nearest minute:

A2 = 8:30
B2 = 9:30

=ROUND((B2-A2)*1440,0)/1440

Format as h:mm

 

[I heart Excel]

Thank you very much, worked perfectly!

Cheers

 

[T. Valko]

Thank you very much, worked perfectly!

Cheers

You're welcome. Thanks for the feedback!