Unexpected results when comparing time values

[SLARRA]

I have an employee timesheet in Excel that includes a number of error checks. One test looks to see whether the sum of the amount of times claimed on various project codes (e.g. 2 hours and 30 minutes attributed to meetings and 5 hours and 30 minutes attributed to planning) for a given day is equal to the attendance reported (i.e. end of day clock-out time minus beginning of day clock-in time). Employees are expected to enter their clock times and project duration times in hh:mm format.

While the error check performs as expected in the template spreadsheet on my computer, a co-worker had problems and sent me a copy of his timesheet, in which the error check sometimes generates an error even when a cursory visual inspection reveals that everything should be OK. Despite the fact that the employees enter their times as discrete values, precise to the minute, the values compared in this guy’s timesheet don’t match exactly – tho the variance is out around the 15th decimal place if the time values are displayed as decimal numbers. Of course, the numbers being compared for the error check are not the user-entered values, but rather the results of some very basic formulas (sum of project time, difference between clock-out and clock-in time). Based on other posts in this forum, I have concluded that this unexpected variance is due to the floating point math problem noted here --> (http://support.microsoft.com/kb/78113/en-us), but I find it odd that this problem is not native to my computer as well (tho I do get the “faulty” error check when I open his spreadsheet on my computer, I cannot replicate the problem if I type in his time values in the template spreadsheet on my computer… and, FYI, neither of us had “precision as displayed” enabled).

So… I’m not very confident in this conclusion. I found suspiciously few postings on MrExcel.com regarding this issue for time values; yet, it seems it would be a commonly encountered problem for users who apply formulas to times, then compare the results, expecting to find mismatches only when the user-entered values differed. So tell me, gurus, does my diagnosis sound correct?

I’m also looking some advice for compensating for these unexpected mismatches. Should I have a preference in using mround(a1,”0:01”) vs round(a1*1440,1)/1440 to force those formulas results to be discrete, nearest minute values?

 

[Damon Ostrander]

Hi SLARRA,

Hopefully this explanation will shed some light rather than further confuse.

Date-time values in Excel are represented in days and fractions thereof. They are not in floating point, but rather in fixed point as an 8-Byte value. The date part of the value is the higher-order 4 Bytes, and the time part is the lower-order 4 Bytes, so the decimal point (actually the binary point) is assumed to be between the two 4-Byte parts.

The trouble comes in because, just as with other types of numbers, Excel must represent these numbers internally in binary arithmetic. A consequence of this is that certain decimal values cannot be represented exactly in binary with a finite number of digits. Just as 1/3 cannot be represented exactly in decimal (e.g., no matter how many 3's you add after 0.33333, it will never be exactly 1/3), one hour (1/24th of a day) cannot be represented exactly in binary. Likewise one minute, one second, etc. However 12 hours, 3 hours, 1 hr 30 min, etc., can be represented exactly because 1/2, 1/8, 1/16 of a day have multiples of 2 in the denominator.

In your example, neither 2 hr 30 min or 5 hr 30 min have exact representations in binary. This means that when you do arithmetic with these values you will generally get a slight remainder. Even if the resulting value should have an exact representation in binary, there is no guarantee that you will get this value. As a result, you should never test two date-time values for equality. The same is true for floating point numerical values. Most programming courses teach this early on: that you should never test two values for equality, but rather for almost equality. Generally, this is what is recommended: rather than test IF X = Y use the test IF ABS(X-Y) < 10e-13 . In other words, make sure the difference between the two is very small. You can use this type of test between two dates (although I would recommend 10e-6 rather than 10e-13 because the time part only has 4-Byte precision). This would make sure the two values match within a fraction of a second.

The bottom line is that there is absolutely nothing wrong with Excel. This is the way computing is done on virtually every machine and in every programming language, and this problem exists with all of them. As a programmer (and you are "programming" when you enter formulas in Excel) you must just be aware of this issue and write your "code" taking this into account.

Keep Excelling.

Damon

 

[SLARRA]

Very informative reply - thank you! Your detailed explanation will surely be helpful to others too. Seems like this would be a relatively common topic for the forum - Obviously, I didn't find any relevent existing posts, but maybe I didn't search hard enough.

Altho you note that programmers learn early on to avoid this pitfall, I think the casual Excel user could make the same assumptions that I did. A heretofore unenlightened user such as myself might reasonably assume that Excel stores time values with the same degree of precision as was entered by the user. Similarly, one might assume that results of simple addition and subtraction of those values would maintain the same degree of precision. Accordingly, I am surprised that Excel's method of handling time values renders (tiny) fractions of minutes even when the original values were always entered in whole-minute increments and subsequent mathematical operations are limited to addition or subtraction. This little rant, by the way, is not meant to sound as if I'm disputing your explanation!

Knowing now that comparing time values with the expectation of finding exact matches is a faulty approach, I will change my error-checking formulas to consider very small differences to be the same as an exact match (as you suggest) or to round the values to the nearest minute prior to doing any comparisons.

Thanks!