两个时间值之间的差异,其中时间值为 [h]:mm 格式且大于 24 小时

两个时间值之间的差异,其中时间值为 [h]:mm 格式且大于 24 小时

所有列的格式均为[h]:mm

=ABS(M5-I5)M中间的 Hours 在哪里并且I是‘Af Hours’)公式一直给我一个#VALUE!错误。

我希望第三列能够显示两列之间的差异。

当值大于时,时间计算似乎*24不适用于格式化的单元格[h]:mm9999:59

在此处输入图片描述

答案1

如何输入值至关重要。

正如 MS 所记录的Excel 规格和限制“可输入的最大时间量:9999:59:59”

您输入的任何大于该值的值都将被视为文本. 更改单元格的数字格式不是将该文本字符串转换为数字。

为了处理较大的时间数字,您可以将它们转换为 Excel 可以理解的时间值。

因此,如何输入值是最重要的。

“最安全”的方法是创建一个表格,在其中分别输入小时和分钟作为整数,然后使用数学公式将它们转换为时间。

然后可以将它们格式化为时间,并且您可以按照此方式添加/减去它们。

如果你只是将它们输入到单元格中,格式为,您可以直接在该条目上使用公式。

Excel 将时间存储为一天的分数。因此,公式需要:

  • 检查值是否为数字(如果是数字则不需要转换
  • 如果不是数字,则将小时部分除以 24(一天的小时数),将分钟部分除以 1440(一天的分钟数),然后将两者相加。
  • 然后 Excel 即可将结果视为时间。

这不像用户表单方法那么强大,因为它允许您输入“非法”值,但对于您提供的数据,以下公式将起作用:

=ABS(IF(ISNUMBER(A1),A1,LEFT(A1,LEN(A1)-3)/24+RIGHT(A1,2)/1440)-IF(ISNUMBER(B1),B1,LEFT(B1,LEN(B1)-3)/24+RIGHT(B1,2)/1440))

在此处输入图片描述

相关内容