Excel夜班工时计算及多班次结果求和的简化公式需求

背景 #

你的Excel表格中，每组班次对应两列：一列是开始时间（如A1=15:30），一列是结束时间（如B1=01:00），每日一组。需要统计所有班次中落在22:00-次日06:00的夜班工时，典型示例：

• 17:00-02:15 → 4小时15分
• 22:00-09:15 → 8小时

已知=MOD(B2-A2;1)可计算单班次总工时，但无法提取夜班工时；现有单班次公式过于冗长，需要简洁的多组求和方案。

优化单班次公式 #

先简化单班次夜班工时计算，用直观的时间交集逻辑：

=MAX(0;MIN(B1;TIME(6;0;0))-TIME(22;0;0)) + MAX(0;MOD(B1;1)-MAX(A1;TIME(22;0;0))) + (A1>B1)*MAX(0;MIN(TIME(6;0;0);MOD(B1;1))-A1)

逻辑拆解 #

• 第一段：计算22:00到次日06:00的固定夜班区间时长（如果班次覆盖这段）
• 第二段：计算班次中22:00到结束时间的时长（非跨午夜情况）
• 第三段：处理跨午夜班次中，开始时间到次日06:00的时长

多组班次求和方案 #

方案1：SUMPRODUCT批量计算（兼容所有Excel版本） #

如果你的班次是成对列（如A/B、C/D、E/F...），用SUMPRODUCT一次性求和所有组的夜班工时：

=SUMPRODUCT(
    MAX(0;MIN(B2:B10;TIME(6;0;0))-TIME(22;0;0)) + MAX(0;MOD(B2:B10;1)-MAX(A2:A10;TIME(22;0;0))) + (A2:A10>B2:B10)*MAX(0;MIN(TIME(6;0;0);MOD(B2:B10;1))-A2:A10),
    MAX(0;MIN(D2:D10;TIME(6;0;0))-TIME(22;0;0)) + MAX(0;MOD(D2:D10;1)-MAX(C2:C10;TIME(22;0;0))) + (C2:C10>D2:D10)*MAX(0;MIN(TIME(6;0;0);MOD(D2:D10;1))-C2:C10)
)

替换A2:A10/B2:B10、C2:C10/D2:D10为你实际的时间范围，有更多组就继续追加对应列的计算段。

方案2：Excel 365/2021专属（更灵活） #

用BYROW+LAMBDA批量处理每行的多组班次，代码可读性更高：
假设每行的班次是A/B、C/D、E/F列，数据从第2行开始：

=SUM(BYROW(A2:F10; LAMBDA(row, 
    LET(
        calc, LAMBDA(s,e, MAX(0;MIN(e;TIME(6;0;0))-TIME(22;0;0)) + MAX(0;MOD(e;1)-MAX(s;TIME(22;0;0))) + (s>e)*MAX(0;MIN(TIME(6;0;0);MOD(e;1))-s)),
        calc(INDEX(row,1), INDEX(row,2)) + calc(INDEX(row,3), INDEX(row,4)) + calc(INDEX(row,5), INDEX(row,6))
    )
)))

新增班次组时，只需在最后追加calc(INDEX(row,7), INDEX(row,8))这类代码即可。

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