☰

在 Excel 中，用一个日期和时间减去另一个日期和时间会得到错误的结果

tag-icon • tag-icon microsoft-excel date-time
在 Excel 中，用一个日期和时间减去另一个日期和时间会得到错误的结果

我正在尝试从相同格式的另一个时间中减去单元格格式自定义：mm/dd/yyyy hh:mm:ss AM/PM 中的日期/时间。我尝试了许多网站上关于如何操作的想法，但没有一个能展示我希望如何完成的示例。尝试过使用 DATEDIF 公式、带有 TIMEVALUE 和 MINUTE(TIME(HOUR 的公式等等，但都不起作用……所以假设 A1 = 11-25-2020 3:30:00 PM A2 = 09-16-2020 7:30:00 AM 和 A3 = SUM(A1-A2) = 00/03/10 08:00:00 A3 格式为 yy/mm/dd hh:mm:ss

A1 转换为一般时 = 44160.64583 A2 转换为一般时 = 44090.3125 A3 转换为一般时 = 70.33333333 因此，日期/时间到数字的转换看起来正确，数学看起来也正确 问题在于 A3 中数字到日期/时间的转换。

有谁知道这个问题的解决方案吗？

提前感谢 Curt

答案1

当然，问题在于年份和月份都没有固定的天数，因此需要分别计算差值。

要将值输出为字符串，您可以尝试：

=TEXT(DATEDIF(A2,A1,"y"),"[<>1]0 ""yrs,""; 0 ""yr,""")&
  TEXT(DATEDIF(A2,A1,"ym"),"[<>1] 0 ""months,""; 0 ""month,""") &
  TEXT(DATEDIF(A2,A1,"md")-(MOD(A2,1)>MOD(A1,1)),"[<>1] 0 ""days,"";0 ""day, """) &
  TEXT(HOUR(MOD(A1,1)-MOD(A2,1)+(MOD(A2,1)>MOD(A1,1))),"[<>1] 0 ""hrs,""; 0 ""hr,""") &
  TEXT(MINUTE(MOD(A1,1)-MOD(A2,1)+(MOD(A2,1)>MOD(A1,1))),"[<>1] 0 ""mins,""; 0 ""min""") &
  TEXT(SECOND(MOD(A1,1)-MOD(A2,1)+(MOD(A2,1)>MOD(A1,1))),"[<>1] 0 ""secs""; 0 ""sec""")

请注意，我们使用布尔值(MOD(A2,1)>MOD(A1,1))来确定是否在日期和时间值中添加/减去一天，以解释结束时间早于开始时间。

在此处输入图片描述

如果您想要不同的输出格式，这是可行的。但我坚持使用输出为 *文本，因为某些差异无法用日期格式表达；例如01-02-29（1 yr, 2 months, 29 days），因为当年二月没有 29 天）。

答案2

很难从最终格式中看出你的目的，所以猜测不太可能正确。尽管如此，我会尽我所能猜测，认为你的目的是将时间差异显示为当前年份的一部分，作为一年中的日期，给人一种不同于年份百分比的感觉。所以我猜你在示例中想要的结果将是“20/03/10 08:00:00”...

您可以通过在 A3 中的减法中添加 43830 来实现这一点。因此，经过的时间将相对于年初。您可以使用 DATEVALUE() 并向其传递一个构造的字符串来查找当前年份并将“1/1/”连接到它，从而确保未来不会出现这种情况。

（如果这不是你的想法，你需要进行编辑以“澄清”（即：提及你正在寻找的某种内容）。再过几个小时，即使编辑后也不会有人回答，而且它可能也会被投票关闭。）

答案3

由于您使用的是日期时间戳（日期和时间同时使用），因此您必须使用不同的方法。您不能使用该DATEVALUE函数，因为它会将以文本表示的日期转换为正确的 Excel 日期。

在此处输入图片描述

  • 如果您想获得年、月和日的差异，请在单元格 O5 中使用以下公式：

    =DATEDIF(O3,O1,"Y") & " Years, " & DATEDIF(O3,O1,"YM") & " Months, " & DATEDIF(O3,O1,"MD") & " Days"

  • 获得总天数的公式在 O7 中：

    =-INT(O3-O1) & " Days"

  • 如果要计算总小时数，则单元格 O9 中的公式为：

    =O1-O3

  • 单元格 O9 上应用的格式是[h]:mm:ss，其中[h]将数据评估为 24 小时制时间。

:编辑:

通过评论，OP 透露，确切的需求是找到两个日期时间戳之间的差异年、月、日、时、分、秒。

为此，我想建议两种可能的方案。

在此处输入图片描述

  • 单元格O14中的公式：

    =DATEDIF(O12,NOW(),"Y")&" Years, "&DATEDIF(O12,NOW(),"YM")&" Months, "&DATEDIF(O12,NOW(),"MD")&" Days,  "&TEXT(NOW()-O12,"h"" hrs ""m"" mins ""s"" secs""")

  • 单元格 O16 中的公式：

     =INT(NOW()-O12)&" days "&TEXT(NOW()-O12,"h"" hrs ""m"" mins ""s"" secs""")

笔记：

  • 我已使用NOW()可编辑的当前日期和时间函数。

  • 请记住函数NOW()是不稳定的，因此为了避免当前日期和时间的频繁更改，最好NOW()用任意CURRENT DATE & TIME值替换。

根据需要调整公式中的单元格引用。

相关内容