Google Sheets单单元格跨午夜工时计算公式修正求助

问题根源 #

原公式未处理跨午夜班次的时间差逻辑：当结束时间为2400（转换为00:00时间值）时，直接减去开始时间会得到负数，再额外减1小时休息时间后结果完全错误（如1500-2400算出-16）。

修正思路 #

1. 提取单元格内的起止时间并转换为TIMEVALUE格式
2. 判断结束时间是否小于开始时间：若是则说明跨天，需在结束时间基础上加1天（时间值加1）再计算差值
3. 保持原逻辑中扣除1小时休息时间的规则

最终修正公式 #

单个单元格版本（推荐用LET简化可读性） #

=IF(HK81<>"", IFERROR(
  LET(
    start_time, TIMEVALUE(LEFT(HK81,2)&":"&RIGHT(LEFT(HK81,4),2)),
    end_time, TIMEVALUE(LEFT(RIGHT(HK81,4),2)&":"&RIGHT(HK81,2)),
    total_hours, IF(end_time < start_time, (end_time + 1 - start_time)*24, (end_time - start_time)*24),
    total_hours - 1
  ), 0), "")

数组版本（整行批量计算） #

=ARRAYFORMULA(IF(HK81:HK<>"", IFERROR(
  LET(
    start_time, TIMEVALUE(LEFT(HK81:HK,2)&":"&RIGHT(LEFT(HK81:HK,4),2)),
    end_time, TIMEVALUE(LEFT(RIGHT(HK81:HK,4),2)&":"&RIGHT(HK81:HK,2)),
    total_hours, IF(end_time < start_time, (end_time + 1 - start_time)*24, (end_time - start_time)*24),
    total_hours - 1
  ), 0), ""))

无LET兼容版本（适配旧版Google Sheets） #

=IF(HK81<>"", IFERROR(
  IF(
    TIMEVALUE(LEFT(RIGHT(HK81,4),2)&":"&RIGHT(HK81,2)) < TIMEVALUE(LEFT(HK81,2)&":"&RIGHT(LEFT(HK81,4),2)),
    (TIMEVALUE(LEFT(RIGHT(HK81,4),2)&":"&RIGHT(HK81,2)) + 1 - TIMEVALUE(LEFT(HK81,2)&":"&RIGHT(LEFT(HK81,4),2)))*24 - 1,
    (TIMEVALUE(LEFT(RIGHT(HK81,4),2)&":"&RIGHT(HK81,2)) - TIMEVALUE(LEFT(HK81,2)&":"&RIGHT(LEFT(HK81,4),2)))*24 - 1
  ), 0), "")

验证示例 #

• 正常班次1330-2230：计算得(22.5-13.5)-1=8，符合预期
• 跨午夜班次1500-2400：计算得(0+1-15/24)*24 -1=9-1=8，正确输出结果

内容的提问来源于stack exchange，提问作者Karina Baden