Weird Finding

[Chris]

I have a formula set up to calculate the difference between 2 times.

Cell A2 = 10:00 AM
Cell B2 = 10:25 AM

The formula in Cell A3 is =(B2-A2)*1440. This formula returns the total
minutes between the two times into Cell A3 which the value is 25.

I have conditional formatting set up to fill in the cell color as red if the
value is less than 25. For some reason, the cell is filling in red for the
calculation between 10:00 AM and 10:25 AM which is incorrect due to the value
being exactly 25. However, if I do a calculation of 11:00 AM to 11:25 AM,
the cell with the conditional formatting does not fill in with red which is
correct because the value is exactly 25.

I typed in some other ones too. I think 12 - 12:25 turned it red too. Its
definitely weird.

Can someone explain for me?

Thanks.

 

[Joe User]

The formula in Cell A3 is =(B2-A2)*1440.

Try =ROUND((B2-A2)*1440,0) instead.

I have conditional formatting set up to fill in the
cell color as red if the value is less than 25. [....]
Can someone explain for me?

If you format your original A3 with 13 decimal places, you might get some
insight: the value that __appears__ to be 25 is really 24.9...9, which is
indeed less than 25.

The reason is complicated to explain, but very common. As you may know,
time is stored as a fraction of a day. Most fractions cannot be represented
exactly by Excel (and most applications). Format A2 and B2 as Number with 15
decimal places, and you will see what I mean. These inherent inaccuracies
are often magnified when you perform arithmetic operations.

Generally, the solution is to round results of arithmetic operations. There
are several ways to make that happen. Using formatting to round only changes
the appearance of the value, not the actual value. It is best to round
explicitly, either by using the ROUND function as I did above, or by setting
the "Percision as displayed" option under Tools > Options > Calculation. I
usually deprecate PAD for a number of reasons.


----- original message -----

 

[Luke M]

I think the problem is with the way XL stores time. 10:00 is actually stored
as a decimal number, which in this case ends with 6 repeating. When you do
the subtraction, you end up with a decimal that has 1 repeating. Finally, XL
does the multiplication, and due to a 15 digit limit, displays the number has
25. However, if you were to format the cell as number with 13 decimals, you
can see that is shows:
24.9999999999999
Thus, it really is less than 25 (by a very small amount!)

While 11:00 to 11:25 comes out to:
25.0000000000000

For a solution, you could try:
=ROUND((B2-A2)*1440,0)