Formula result not as expected. Elapsed Time Comparison.

[zcavaricci]

I have the following columns and need to check if the elapsed time between 2 times are within 15 minutes +/- 2 minutes.

Please see the attached spreadsheet.

[MrShorty]

This looks like floating point error. When I use the Evaluate formula tool on D3, (https://support.office.com/en-us/art...6-a70aa409b8a7 ) I see:

1) The value in C3 is 0.00902777777777763.
2) The value from the TIME() function is 0.00902777777777778 (a difference of about 12 picoseconds).
3) 63 is not >= 78, so this boolean expression returns FALSE. Of course, once one argument in and AND() function returns FALSE, the whole thing will return FALSE.

Solutions to most floating point errors usually involve another rounding function. Decide what precision you need to actually test for, and then nest your values inside of an appropriate ROUND() or MROUND() function. Your entries are all to the nearest minute, so, if I assume that 1 minute is sufficient precision, I might nest the current formula in C inside of an MROUND() function =MROUND(current_formula,TIME(0,1,0)), which will round the current value to the nearest minute. This changes the result in D4 to TRUE.

[joeu2004]

=MROUND(current_formula,TIME(0,1,0)), which will round the current value to the nearest minute

Although =MROUND(MOD(B3-A3,1),TIME(0,1,0)) seems to work for all times in A3 and a difference of 0:13 or 0:17 in B3, it does not work, for example, when A3 is 1:01, B3 is 2:14, and the formula in D3 is =AND(C3>=TIME(1,13,0),C3<=TIME(1,17,0)).

In general, MROUND is unreliable when the second parameter is not an integer, for the same reason that MOD(B3-A3,1) did not work, namely: binary floating-point anomalies.

IMHO, it is more reliable to use the following to round time to the minute, when the entered time is accurate to the minute: =--TEXT(MOD(B3-A3,1),"h:m") . The double-negate converts text to a numeric value.

-----

BTW, even though the comparison in C2 seems to work, it is only because Excel comparison operators (e.g. "<=") round the left and right operands to 15 significant digits.

If the formula had been written in the form =AND(C2-TIME(0,13,0)>=0,C2-TIME(0,17,0)<=0) , which is mathematically equivalent, the formula would return FALSE.

Even though both C2 and TIME(0,17,0) appear to be 0.0118055555555556 because Excel formats only the first 15 significant digits (rounded), in fact, C2 is infinitesimally larger; namely about 6.94E-17 larger.

In this case, we can see that difference with the formula =C2-TIME(0,17,0) formatted as General or (better) Scientific.

But often, we need to use a formula of the form =C2-TIME(0,17,0)-0 . The redundant -0 avoids another anomaly that is specific to Excel arithmetic, namely: sometimes, if the last difference of a formula is "close to zero" (a misnomer), Excel replaces the actual difference with exactly zero.

The point is: we cannot always rely on a visual comparison of the formatted values in order to understand why arithmetic and comparison operations do not give us expected results.

[zcavaricci]

Thank you both for the valuable replies!!!

I have used =--TEXT(MOD(B3-A3,1),"h:m") in my formula and all seems well. 1500 calculations and all were as expected!! Thank you so much for the quick replies!!!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.