☰

计算跨越两天的时间长度的公式

tag-icon • tag-icon microsoft-excel worksheet-function
计算跨越两天的时间长度的公式

列A：24 小时格式的开始时间（使用公式并用++=NOW()锁定） 列： 24小时格式的结束时间（输入方式与列相同） 列：持续时间，格式化为，使用公式CtrlSHIFT;
BA
C[mm]=B1-A1

如果列中的数据B包含第二天发生的时间，我该如何计算持续时间？

实际工作表：

实际工作截图

答案1

有可能最优雅、最简单的解决方案是这个答案fixer1234 提到此评论：

=MOD(B1-A1,1)

请注意，它假定两个值都只是时间值（或者日期部分将被截断），即每个值都小于 1，并且持续时间小于 24 小时。

解释：

这是可行的，因为对于序列日期时间值，1 对应 24 小时，而仅时间值是小于 1 的日期时间值。因此：

  • 如果B1 >= A1那么MOD(B1-A1,1)→B1-A1如预期
    或
  • 如果B1 < A1则MOD(B1-A1,1)相当于1-(A1-B1)（因为MOD(-n,m)=m-MOD(n,m)）这给出了正确的“补集”持续时间


另一种解决方案（您判断它是否更简单/更优雅;-)）是：

=(B1<A1)+B1-A1

解释：

这是可行的，因为在算术运算中使用时，TRUE值被隐式强制转换为 a 1，而FALSE值被隐式强制转换为 a 。因此：0

  • 如果B1 >= A1则(B1<A1)→0且公式变为B1-A1
    或
  • 如果B1 < A1则(B1<A1)→1且公式等同于1-(A1-B1)

请注意，此公式将正确运行即使两个值都是日期时间和相隔超过24小时！这是不是第一个公式的情况，因此第二个公式更有用/更通用。

答案2

Ctrl++Shift只是;将当前时间放入没有日期戳的单元格中，正如@Akina 在他的回答中指出的那样，它什么都没有锁定。

NOW()向单元格添加日期戳和时间。要查看该日期戳，请点击General未格式化的空单元格或任何格式的单元格并输入=NOW()。在输入此内容时，我得到了21/08/2018 11:25。

如果您将 放入=NOW()单元格A1并=NOW()+TIME(23,0,0)放入单元格B1，然后您将 放入=B1-A1单元格C1并将单元格格式化为，Time您将获得23:00:00。

如果您输入的时间没有日期戳，则需要使用公式IF()来确定第二个时间是否早于第一个时间（时间范围超过午夜）。以下将计算单元格中的时间B1与单元格中的时间之间的时间差A1，无论结束时间是否超过午夜。它也适用于有或没有日期戳的情况，因为日期戳会被忽略。

=IF(B1<A1,MOD(B1-A1,1),B1-A1)

公式解释

=IF(B1<A1,...)

单元格中的时间B1是否超过午夜？如果是，则执行下一部分

MOD(B1-A1,1)

返回MOD(number,divisor)数字除以除数后的余数。单元B1格减去单元格A1是负值，因为单元格中的时间B1已过午夜，而 Excel 在大多数情况下不喜欢负时间值，因此它会给出#####不带 的结果MOD()。MOD()对它进行排序。

公式的替代方法MOD()是使用

TIME(23,59,59)-TIME(HOUR(A1),MINUTE(A1),SECOND(A1))+TIME(0,0,1)+B1

也就是23:59:59 minus time in cell A1 plus 1 second plus time in cell B1。

最后一位是单元格中时间,B1-A1)的最后一部分IF()B1不是午夜过后。

答案3

谢谢@Akina，这是唯一对我有用的公式...我尝试了上述几种方法（请注意，投票数为正数），但得到的是可怕的######或带有几个小数点的负数。计算了跨越两天的时间，没有日期戳和所有那些胡言乱语...

答案4

要获得Minutes两者之间的差异24Hrs DateTime Stamps，您可以使用其中任一项：

=Int((A2-A1)*24*60)

或者

=int((A2-A1)*1440)

或者

=ROUNDUP((A2-A1)*1440,2)

笔记，公式单元格必须具有General格式。

相关内容