"Claus Busch" wrote:
Am Tue, 28 Jan 2014 08:55:31 -0800 schrieb joeu2004:
For example, for "6:15 - 7:15" in A1, the MOD result in B1
is displayed as 1:00, but =B1=TIME(1,0,0) returns FALSE(!).

that is correct. But if I calculate the time with your formula in C1 and
with mine in B1 and then try C1-B1 I get 0. If I change the format to 30
digits behind the decimal separator all digits are 0

Yet if you format B1 and C1 as Number with 16 decimal places, we see a
difference in the last decimal place. (Generally, it is better to format as
Scientific with 14 decimal places.)

But that is not always the case.

Yes, =C1-B1 is exactly zero. But =C1-B1-0 formatted as Scientific is about
3.47E-17.

The difference is due the dubious "close to zero" heuristic that is vaguely
and poorly described in http://support.microsoft.com/kb/78113.

Excel arbitrarily replaces the arithmetic result with exactly zero when the
arithmetic result is "close to zero".

But even the title "close to zero" is incorrect. For example,
=1E30+1E14-1E30 results in exactly zero, even though 1E14 is not "close to
zero".

Moreover, the application and implementation of the heuristic is
inconsistent. For example, =1E30+1E14-1E30+0, just adding zero, and
=SUM(1E30,1E14,-1E30) result in about 1.40737E+14.

For similar reasons, sometimes =C1=B1 returns TRUE, but =C1-B1=0 returns
FALSE(!).

That is true in your case; even =C1=B1 returns FALSE.

But consider =MOD("6:45"-"6:15",1) in B1 and
=--TEXT(MOD("6:45"-"6:15",1),"hh:mm") in C1.

In that case, =C1=B1 is TRUE, but =C1-B1=0 is FALSE(!). Also, =C1-B1 is
exactly zero, but =C1-B1+0 is about 1.73E-17.