☰

IExcel 无法正确比较日期

tag-icon • tag-icon microsoft-excel worksheet-function date
IExcel 无法正确比较日期

我有一张包含两个日期的表格，格式如下：

  • C1 Sep 21, 2017 10:22 AM
  • C2Sep 28, 2017 10:09 AM

如果我对它们运行以下 if 语句……

=IF(M4<L4, "Fail", "Pass")

...然后公式返回Pass。但是，如果两个日期位于不同的月份，则公式失败。

例如，

  • C1[Sep 26, 2017 03:14 PM]
  • C2[Oct 2, 2017 03:39 PM]
  • 返回Fail。

C1 的值低于 C2，但公式仍然失败。

如果单元格格式为文本或日期，则返回值没有区别。并且该=DateValue()函数将不起作用，它返回#VALUE

有任何想法吗？

答案1

If 语句的工作方式如下：

=IF( Logical Test, Value if test is TRUE, value if test is FALSE)

因此你可能想要使用：

=IF(C2<C1,"Pass",Fail")

我没有完全理解你的问题，所以你可能需要<换成>

您可以使用的逻辑运算符：

  • >， 比...更棒
  • <少于
  • =等于
  • <=小于或等于
  • >=大于或等于
  • <>不相等

答案2

在您的公式中，您不是在比较 C1 和 C2，而是在比较 M4 和 L4。如果您想直接比较 C1 和 C2，您也应该在公式中使用这些值。

例如这个公式

=IF(C1<C2, "Fail", "Pass")

如果单元格 C1 中的日期/时间小于单元格 C2 中的日期，则将显示“失败”。如果 C1 中的日期大于或等于单元格 C2 中的日期，则将显示“通过”。单元格 C1 和 C2 应格式化为日期，而不是文本。两个单元格都必须是日期或用户定义的格式（使用 excel 提供的时间和日期变量，如 YYYY、MMM、DD 等）。如果您使用文本格式，“if-clause”将不会比较时间/日期，而是比较字符串，这会给您“有趣”的结果！

DateValue() 函数仅适用于文本格式的单元格，并且只在特定范围内有效，不适用于您的符号。它适用于31.07.2018 20:36但不适用于Jul 31, 2018 08:36 PM。

答案3

cjb110他的评论99.999%肯定正确。

格式化为日期还是纯文本并不重要，因为对于 Excel 来说，它就是纯文本，无论哪种方式看起来都一样。正如所指出的，在这种情况下，Excel 实际上会进行字母比较，而“S”“大于”字母表中较靠后的“O”。“成功”的比较并不成功。它看起来成功了，但只是字母顺序正确，而不是数字或日期正确（“9 月 21 日”中的“1”按字母顺序“小于”“9 月 28 日”中的“8”，因此它似乎成功了，但实际上并没有做任何类似的事情）。

为了完成任务，您需要提取重要的部分并将它们输入到生成日期和/或时间的函数中。例如，对于“2017 年 9 月 21 日上午 10:22”行中的数据，您可以使用类似以下内容查找日期：

=DATEVALUE(L4)

您的日期字符串足够接近 Excel 的格式，所以就这么简单。日期在 Excel 中存储为从起点开始的天数，通常为 1/1/1900，时间存储为 0 到略小于 1 之间的十进制数。对于这个日期，它们是 42999 和 .431944444，所以实际存储的日期和时间值是 42999.431944444。

您不必实际更改工作表中的数据。您只需要在公式中将其更改为日期/时间。因此，对于 L4，可以使用以下方法找到日期和时间：

=DATEVALUE(L4) + TIMEVALUE(L4)

并且由于您可以对以后的日期/时间执行相同操作，因此您可以将数据作为日期和时间与以下内容进行比较：

=( =DATEVALUE(M4) + TIMEVALUE(M4) ) - ( DATEVALUE(L4) + TIMEVALUE(L4) )

它保留了你所做事情的逻辑，因此当你一年后回顾时，就很容易意识到你所做的事情。

不知道您是如何Albin在DATEVALUE()无法使用您的字符串时遇到问题的，因为对我来说它们毫不费力就起作用了。我相信我必须将其归结为 Excel 版本的差异，而现在该函数显然更胜一筹。

但这可能并非事实，读到本文的人确实看到它在这样的字符串上失败了，他们可能会发现这是由于所选的 Windows 设置造成的。我的设置可能包括格式化像您的或接近您的日期/时间。毕竟，无论 Excel 函数如何处理它们识别为日期/时间的内容，它们都必须首先将其识别为日期/时间，然后 Excel 会依靠 Windows 来进行识别。这似乎有点奇怪，但如果 Windows 设置有足够大的差异，那么 Windows 版本可能无法将这些字符串视为日期/时间，因此 Excel 永远不会知道它们是日期/时间。然后DATEVALUE()，比如说，会看到这些字符串与看到的字符串相同SJFHHYUR98234..||mm,*jjds......不是日期，不是时间，只是一些随机字符串。所以如果有人遇到这种情况，也许需要调查一下。

或者人们可以接受它，然后转向更快的做法，只需将字符串拆开，构建一个 Excel 可以识别的字符串，然后将其传递给DATEVALUE()或DATE()，这样就完成了整个过程。并不是每个人都对“Excel At the Edges”感兴趣……

相关内容