使用TIME函数进行10:40:00的Google Spreadsheet比较失败 [英] Google Spreadsheet comparison using TIME function for 10:40:00 fails

问题描述

将值10:40:00的单元格与电子表格函数TIME(10,40,0)

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

一系列比较显示，从8:40到10:39:59以及从10:40:01到13:40:00的值正确比较，但是10:40:00为FALSE. google.com/spreadsheets/d/1SjeN3qh3TSed2PKDNa5_bqTBGJquLdWSev6kfkohkDI/edit?usp=sharing"rel =" nofollow>比较.

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?

推荐答案

我已经在处理您的电子表格了，我想我已经找到了原因.这一定是一个错误(或者至少是在读取时间文字方面的不一致).看看下面的图表，该图表显示了以文字形式输入的时间，从中解析的值以及从=TIME()解析的值:

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

请注意，精度始终为15个小数点，但是出于某种原因，从文字中解析时10:40:00的值将四舍五入，而从=TIME()计算得出的值将四舍五入.

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().

有趣的是，根据=TIME(10,40,0)计算得出的值似乎是不正确的，因为，应将其四舍五入.

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

无论如何，理解上述内容后，我们可以使用1e-15的epsilon(碰巧是小于一纳秒分辨率的一个数量级单位-一纳秒为1.15741e-14)比较这两个值.因此，如果使用以下比较，则可以:

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

这篇关于使用TIME函数进行10:40:00的Google Spreadsheet比较失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！