如何使用Excel公式计算23:00-07:00夜班时段的工作时长

针对E列（班次开始时间）、F列（班次结束时间），计算落在23:00-次日07:00时段的工作时长，同时处理完全在该时段且时长超6小时需扣除0.5小时的规则，可用以下公式：

=LET(
    start, E2,
    end, F2,
    night_start, TIME(23,0,0),
    night_end, TIME(7,0,0),
    // 校正跨天结束时间（结束早于开始则视为次日）
    adj_end, IF(end < start, end + 1, end),
    // 计算与夜间时段的重叠时长
    overlap, MAX(0, MIN(adj_end, night_end + 1) - MAX(start, night_start)),
    // 处理超6小时的扣减规则
    adjustment, IF(AND(start >= night_start, adj_end <= night_end + 1, adj_end - start > 6/24), -0.5/24, 0),
    // 转换为小时数输出
    (overlap + adjustment) * 24
)

场景验证 #

• 跨夜班次（23:30-06:30）：夜间总时长7小时，因完全在夜间且超6小时，扣减0.5小时，最终结果6.5小时
• 23:00前开始（17:00-00:00）：夜间重叠时段为23:00-00:00，时长1小时
• 07:00前开始后结束（05:00-15:00）：夜间重叠时段为05:00-07:00，时长2小时
• 完全在夜间且时长5小时（23:00-04:00）：无扣减，结果5小时

公式逻辑说明 #

1. 时间校正：判断结束时间是否早于开始时间，若是则按次日时间计算，避免跨天逻辑错误
2. 交集计算：通过取时间范围的最大起始点和最小结束点，精准算出与夜间时段的重叠时长
3. 扣减判断：检查班次是否完全落在夜间时段且总时长超过6小时，满足条件则扣除0.5小时
4. 单位转换：将Excel内部以天为单位的时间值，转换为直观的小时数输出

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