Google Spreadsheet comparison using TIME function for 10:40:00 fails

Question

I'm having a problem when comparing cells with value 10:40:00 to the result of the spreadsheet function TIME(10,40,0)

A series of comparisons shows that values from 8:40 to 10:39:59 and from 10:40:01 to 13:40:00 compare correctly but 10:40:00 incorrectly returns FALSE for the comparison.

Am I missing something or is this a bug?

Hakluyt

Answer 1

I've played around with your spreadsheet and I think I've found the reason. This has got to be a bug (or at least an inconsistency in reading time literals). Take a look at the following chart, which shows the time entered as a literal, the value parsed from this, and the value parsed from =TIME():

value       from literal                from TIME(...)
08:40:00    0.3611111111111110000000    0.3611111111111110000000
09:40:00    0.4027777777777780000000    0.4027777777777780000000
10:30:00    0.4375000000000000000000    0.4375000000000000000000
10:39:00    0.4437500000000000000000    0.4437500000000000000000
10:39:59    0.4444328703703700000000    0.4444328703703700000000
10:40:00    0.4444444444444440000000    0.4444444444444450000000
10:40:01    0.4444560185185190000000    0.4444560185185190000000
10:41:00    0.4451388888888890000000    0.4451388888888890000000
10:50:00    0.4513888888888890000000    0.4513888888888890000000
11:40:00    0.4861111111111110000000    0.4861111111111110000000
12:40:00    0.5277777777777780000000    0.5277777777777780000000
13:40:00    0.5694444444444440000000    0.5694444444444440000000

Notice the precision is always 15 decimal points, but for some reason the value for 10:40:00 is rounded one direction when parsed out of a literal, and the other direction when calculated from =TIME().

Interestingly enough, the value calculated from =TIME(10,40,0) seems to be the incorrect one, as 10:40:00 is really the repeating decimal , which should be rounded down.

In any event, understanding the above, we can compare the two values using an epsilon of 1e-15 (which happens to be one unit of magnitude smaller than one nanosecond of resolution - one nanosecond is 1.15741e-14). Therefore if you use the following comparison it works:

=abs(A7-time(10,40,0))<1E-15

Stephen