vba 比较时间值的公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25483259/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Formula to compare time values
提问by user3804276
Below excel formula is working fine but in some cases its not give me proper value .
下面的 excel 公式工作正常，但在某些情况下它没有给我适当的价值。
Input:
输入：
19:20:42
24:58:36
26:11:18
After using this formula:
使用这个公式后：
=IF(TIMEVALUE(K7)>TIMEVALUE("09:00:00"),TRUE,FALSE)
I got the below output:
我得到以下输出：
FALSE
TRUE
TRUE
What I Observe if the time value is > or = 24:00:00 it will not give me the proper answer. How do I fix this?
如果时间值 > 或 = 24:00:00，我观察到的内容不会给我正确的答案。我该如何解决？
回答by Patrick Honorez
As an alternative to Captain's excellent answer, you could also use:
作为船长出色答案的替代方案，您还可以使用：
=IF(K7>(9/24),TRUE,FALSE)
DateTime values are internally stored as a number of days since 1/1/1900,
therefore 1 = 1 day = 24 hours. So 9/24 = 0.375 = 9 hours :-)
DateTime 值在内部存储为自 1900 年
1 月 1 日以来的天数，因此 1 = 1 天 = 24 小时。所以 9/24 = 0.375 = 9 小时 :-)
You can easily verify this by clearing the format of your DateTime cells.
您可以通过清除 DateTime 单元格的格式轻松验证这一点。
Edit: note that such Boolean formula can be expressed in a shorter way without losing legibility:
编辑：请注意，这样的布尔公式可以用更短的方式表达而不会失去易读性：
=K7>(9/24)
回答by Captain
When you go over 24 hours, Excel counts it as the next day... and then the TIMEVALUEis the time the next day (i.e. 00:58:36 and 02:11:18 in your examples) and can, therefore, be before 0900.
You could do DATEVALUE(K7)+TIMEVALUE(K7)to ensure that you count the day part too...
当您超过 24 小时时，Excel 将其计为第二天......然后TIMEVALUE是第二天的时间（即您的示例中的 00:58:36 和 02:11:18），因此可以在0900. 你可以DATEVALUE(K7)+TIMEVALUE(K7)确保你也计算当天的部分......
