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?
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?