☰

从 Excel 中提取两个时间值之间的时间

tag-icon • tag-icon microsoft-excel worksheet-function
从 Excel 中提取两个时间值之间的时间

我在从 Excel 中提取两个时间值之间的时间时遇到了障碍。以下是一些示例。

有人能帮我吗？

Start date                         End date
12/31/2019, 09:44 AM           01/06/2020, 05:04 PM
01/06/2020, 07:37 AM           01/06/2020, 04:37 PM
01/06/2020, 08:28 AM           01/06/2020, 05:07 PM
01/07/2020, 07:45 AM           01/07/2020, 08:30 AM
01/07/2020, 08:30 AM           01/07/2020, 04:55 PM
01/07/2020, 09:43 AM           01/07/2020, 04:34 PM
01/08/2020, 07:48 AM           01/08/2020, 11:35 AM
01/09/2020, 08:31 AM           01/09/2020, 05:21 PM

谢谢！Cotiso

答案1

如果开始时间在 A 列（从 A2 开始），结束时间在 B 列（从 A2 开始），则以下公式将解析每列中的文本，然后对每部分使用TIMEVALUE()和DATEVALUE来获取 Excel 所看到的开始和结束日期/时间值。从结束对的加法中减去起始对可得出经过的时间。

它本来就是十进制的。所以，假设6.305555556。公式实际上会采用它并将其转换为文本字符串，格式为days, hours, minutes：

=LET(  ElapsedTime,  DATEVALUE(LEFT(B2,FIND(",",B2)-1))  +
    TIMEVALUE(MID(B2,FIND(",",B2)+2,LEN(B2)))  -  
    DATEVALUE(LEFT(A2,FIND(",",A2)-1))  -
    TIMEVALUE(MID(A2,FIND(",",A2)+2,LEN(A2))),

    TEXT(  INT(ElapsedTime),  "@")  &  " days, "  &
    TEXT(  (ElapsedTime-INT(ElapsedTime)),  "H \h\o\u\r\s\,\ M \m\i\n\u\t\e\s"))

ElapsedTime是上面描述的部分。最后一部分只是实现“天、小时和分钟”中经过时间所需的格式

因此，根据第一对，输出将被6 days, 7 hours, 20 minutes格式化，6.305555556而未格式化的版本则为几天。

未格式化的版本是真实数字，而格式化的版本只是文本。每种方法都有其优点。如果想要真实数字，但需要格式化的外观，请仅使用 ElapsedTime 部分，并使用以下数字格式（在所有内容下方）进行格式化：

=DATEVALUE(LEFT(B2,FIND(",",B2)-1))  +
 TIMEVALUE(MID(B2,FIND(",",B2)+2,LEN(B2)))  -  
 DATEVALUE(LEFT(A2,FIND(",",A2)-1))  -
 TIMEVALUE(MID(A2,FIND(",",A2)+2,LEN(A2)))


D "days, "H "hours ", M "minutes"

毕竟，您的需求可能千差万别。也许是为了工资单（毫无疑问，最好是十进制），不再用作真实数字，并希望通过转换为仍能很好地传达信息的格式化文本字符串来巩固它，或者介于两者之间的格式化真实数字。

答案2

这将提取两个日期时间值之间的时间。

：警告：

您正在使用错误的日期时间格式12/31/2019, 09:44 AM，因为逗号分隔日期和时间，并且单元格格式为General，如果应用公式，则会产生#VALUE！错误。

在此处输入图片描述

  • 单元格 AR2 中的公式：

    =IF(AP2>AO2,INT(AP2-AO2)&" days "&TEXT(AP2-AO2,"h"" hrs ""m"" mins """),MOD(AP2-AO2,1))

注意：

  • AO 列和 AP 列的单元格格式为 ，mm/dd/yyyy hh:mm AM/PM而 AR 列的单元格格式为General。

  • 使用此公式的主要原因是，您有日期时间值而不是仅有的时间值，并且在这种情况下需要 24 小时时间计算，因为应该有两个不同日期的值，就像在 AO2 和 AP2 中一样。

  • 否则，您可能只是使用=AP2-AO2来获得小时数的差异，但如果您在 AO2 和 AP2 单元格中拥有值，则这是错误的。

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

相关内容