☰

为什么 Excel 处理某些日期的方式与其他日期不同？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 date-time
为什么 Excel 处理某些日期的方式与其他日期不同？

我有一个 CSV 文件，其中包含需要解释为带有分钟、秒和毫秒的日期的字符串。

具体来说，条目如下所示：

"07/31/2013 23:59:32.864",otherdata,moredata
"08/01/2013 00:00:02.863",otherdata,moredata

对于 2013 年 7 月 31 日，Excel 决定使用“常规”格式并将值显示为

07/31/2013 23:59:32.864

这正如预期的那样。

但是，对于 2013 年 8 月 1 日，Excel 采用“自定义”格式“mm:ss.0”，并将值显示为

00:02.9

这没用。如果我将单元格格式更改为“常规”，则值将变为 41282.00003。

奇怪的是，如果我将 8 月改为 7 月（如 07/01/2013），显示的值现在是

07/01/2013  00:00:03

采用“自定义”格式“dd/mm/yyyy hh:mm”。

我怎样才能阻止 Excel 根据我认为是随机的原因选择各种格式并同意采用一种格式来显示这些日期？

请注意，这与 Excel 将日期与月份混淆没有多大关系。日期 08/13/2013（其中 8 显然是月份）丢失了毫秒，并显示为

08/13/2013  23:01:06

采用“自定义”格式“mm:ss.0”。

这真是太奇怪了。

答案1

您需要理顺区域设置和日期格式，并使其与 CSV 中日期的月份和日期顺序保持一致。目前它们似乎不太协调。

41282.00003是 2013 年 1 月 8 日，而不是 2013 年 8 月 1 日。

Excel 将尝试根据您计算机的区域设置来解释日期。如果区域设置为 DMY，而要解释的日期为 07/31/2013，则 DMY 顺序将不起作用，Excel 会将数据解释为文本。这就是您看到的内容。看起来像日期/时间值的文本。尝试以不同的方式格式化该明显的日期/时间值。您会发现您无法做到这一点，因为它是文本。

但如果下一行数据为 08/01/2013，则这非常符合区域设置的 DMY 方案，并将返回为 8-Jan-2013。您可以将单元格的格式更改为自定义格式

dd/mm/yyyy hh:mm:ss.000

并且它将显示为08/01/2013 00:00:02.863

在此处输入图片描述

单元格 A1 中的值是文本值，而不是实际日期时间。单元格格式为“常规”，无论设置多少数字格式都不会改变其外观。

单元格 A2 中的值是真实的日期/时间值，使用上面提到的自定义格式进行格式化。

导入日期时，请特别小心检查导入数据中的日期和月份顺序。使用导入向导时，您可以指定源数据的顺序，这样所有日期都将以一致的方式导入。

让我们仔细看看第二个（真实）日期。08/01/2013 00:00:02.863使用“常规”格式显示为41282.00003，使用正确日期格式显示为 8-Jan-2013。很好。

如果增加常规格式的小数位数，您会发现实际的底层数字是41282.0000331366。这个数字有 15 位数字。

格式化为日期，您可以编辑它并将日期从 8 更改为 7。结果将以“常规”格式显示为41281.00003，但如果您增加显示的数字，您将看到该数字是41281.00003472220

嗯？

怎么会这样？我们只减去了一天，所以只有数字前小数点应该改变。

其实，Excel 的内置精度为 15 位数字。位数更多的数字将被四舍五入，或者最后一位数字将被替换为零。此外，Excel 中有一个众所周知的漏洞，它会影响达到 15 位数字限制的数字的准确性。

我认为这是一个错误显现的例子。

当我们的日期/时间值的日期部分发生变化时，它还会导致重新评估小数，这将导致小数点后 4 位出现一些舍入和不一致的行为。因此，实际的秒和毫秒数据将不准确。

看看这个截图是否有助于澄清：

在此处输入图片描述

B 列至 D 列中的值均引用 A 列。A1 和 A2 之间的唯一区别是手动将日期从 08/01 更改为 07/01（其中 01 是 1 月，根据 DMY 的区域设置）。

“常规”格式显示两个值的小数点后均为 x.0003。扩展小数位后可发现小数点后 4 位的小数位存在很大差异。

由于期望的最终结果是显示秒和毫秒的值，因此小数点后第 4 位的小数确实会产生影响，并且当使用显示秒和毫秒的自定义格式格式化该值时，会显示该差异（在 A 列中）。

另外，请注意三个单元格中的数字分别仅由 15、16 和 17 位 1 组成，而 Excel 只是将第 15 位之后的任何数字替换为零，因为它无法显示更高的精度。

答案2

非常简单的解决方案：

  1. 选择所有日期列。
  2. 选择常规按钮。

  3. 对数字进行排序，并将数字与以文本形式分别存储。

    在此处输入图片描述

  4. 您将发现以下数字和日期形式的数据：

    在此处输入图片描述

  5. 将日期格式转换为带有分隔选项的文本列，并在其他位置写入/ - 等，如日期所示：

    在此处输入图片描述

    在此处输入图片描述

  6. 应用日期公式为：

    在此处输入图片描述

  7. 然后进行日期的特殊粘贴（复制、写入并单击特殊粘贴）。

你的问题已经解决了。

相关内容