![Excel 调度 - 尝试计算午夜后的小时数,使其不为负数](https://linux22.com/image/1421988/Excel%20%E8%B0%83%E5%BA%A6%20-%20%E5%B0%9D%E8%AF%95%E8%AE%A1%E7%AE%97%E5%8D%88%E5%A4%9C%E5%90%8E%E7%9A%84%E5%B0%8F%E6%97%B6%E6%95%B0%EF%BC%8C%E4%BD%BF%E5%85%B6%E4%B8%8D%E4%B8%BA%E8%B4%9F%E6%95%B0.png)
如果有人能帮我解释为什么我的数字是负数而不是 40(它应该等于 40),请帮忙。我将不胜感激。谢谢!
答案1
您已在文件 > 高级 > “计算此工作簿时”部分(向下滚动到末尾)中为工作簿正确地选择了“使用 1904 数据系统”。
这是为了允许将时间计算为负数。(尝试将其关闭,您会看到负数全部变成 #)
但是,例如在 B16 和 C16 中,时间只是简单地输入了 18:00 和 01:00,没有任何日期。01:00 - 18:00 确实是负时间。如果显示为数字,则大约为 -0.71。
解决方案是更改每个单独的结束-开始时间计算以适应午夜过后的情况。因此,将“C16-B16”替换为“IF(C16-B16<0,C16-B16+1,C16-B16)”
您需要对一周中的每一天都执行此操作,这会使公式相当长。
考虑在日期之间添加一个额外的列,以显示当天的工作时间。然后总数只需将这些单元格相加即可。
編輯:打败了它!
再次编辑:您应该像我的例子一样使用 +1,而不是像上一篇文章中那样使用 +24,因为单位是天,而不是小时。
最终编辑:一个更短的解决方案是将“C16-B16”替换为“MOD(C16-B16,1)”。这通过仅保留时间的小数部分来实现。对于时间,小数 1 表示 24 小时。
答案2
超过 24 的通常公式是:
=EndTime - StartTime +(EndTime < StartTime)
将所有这些组合成一个公式,用于计算一周中所有天数的总和,虽然很麻烦,但可行。它需要一个数组输入公式,因为您需要单独测试每一对。
以下公式利用了您的设置,其中所有结束时间都在偶数列中;而开始时间都在奇数列中。
注意构建的两个数组有一列不同。在 Excel 2007+ 版本中,可以使用 ISODD/ISEVEN 函数直接测试奇偶性。
此公式必须数组输入:
=SUM(
MOD(COLUMN($C16:$O16),2)*$C16:$O16-
(MOD(COLUMN($B16:$N16),2)=0)*$B16:$N16+
((MOD(COLUMN($C16:$O16),2)*$C16:$O16-(MOD(COLUMN($B16:$N16),2)=0)*$B16:$N16)<0))
*24
或者使用 ISODD 和 ISEVEN:
=SUM(
ISODD(COLUMN($C16:$O16))*$C16:$O16-
ISEVEN(COLUMN($B16:$N16))*$B16:$N16+
((ISODD(COLUMN($C16:$O16))*$C16:$O16-ISEVEN(COLUMN($B16:$N16))*$B16:$N16)<0))
*24
或者,甚至更短,但更难理解,因为我们使用 MOD 函数仅保留小数部分:
=SUM(
MOD(
ISODD(COLUMN($C16:$O16))*$C16:$O16-
ISEVEN(COLUMN($B16:$N16))*$B16:$N16,
1))
*24
到数组输入公式,在单元格或公式栏中输入公式后,按住 Ctrl-Shift在击球时进入。如果您操作正确,Excel 将在公式周围放置括号 {...}。
我已经为您在屏幕截图中显示的线条计算了公式。使用您的数字,结果是 37.9833
答案3
您的问题在于,当您超过午夜时。以星期五、星期六和星期日的第一行为例。每天的结束时间都是凌晨 2:00。例如,在星期五,当您减去时间时,需要星期五下午 6:00 减去星期五凌晨 2:00,后者在下午 6:00 之前。
为了避免出现负面结果,但仍保持图表美观,您应该使用 IF 语句。要计算星期五,请使用:
=IF(K3 < J3, K3 - J3 + 24, K3 - J3)
IF 语句有三个部分。
(1)条件
K3 < J3
检查 K3(结束时间)是否小于 J3(开始时间)。如果是,则返回负数(凌晨 2:00“小于”下午 6:00)。
(2)如果条件为真
K3 - J3 + 24
如果条件为真,Excel 将使用此公式。我将其写为添加 24 以抵消可能出现的任何负数。
(3)如果条件为假
K3 - J3
如果条件为真,Excel 则使用此公式。
概括
使用提供的 IF 语句代替您用来计算每天小时数的公式。
答案4
我在计算时间时也遇到了类似的问题,有时时间会超过午夜,且没有日期或星期信息。我的开始时间是在晚上,有时会延续到第二天,所以我结合使用了几个公式来让它适合我:
=IF(D4< C4,TEXT(D4-C4+24,"h:mm"),TEXT(D4-C4,"h:mm"))
(请注意,由于某些格式原因,我不得不在小于号后添加一个空格 - 我在公式中没有使用空格)
这是一个简单的 if 语句,在我的情况下,D4 是结束时间,C4 是开始时间。如果结束时间小于开始时间,则必须超过午夜。中间语句给出了经过午夜后校正的总工作时间 (+24)。如果您只需要常规减法,则末尾显示工作时间。我发现,如果没有“TEXT”和时间格式,+24 数学运算将显示为十进制值。