计算两日期间两班次有效工时的公式需求（排除周末）

场景说明 #

计算时段：2023-11-20 06:30:00 至 2023-11-24 15:00:00，仅统计周一至周五的有效班次工时：

• 班次1：06:30-16:30（当日10小时）
• 班次2：18:30-04:30（跨日10小时）
  排除周末，加班时长为00:00:00

班次1（06:30-16:30）工时计算公式（Excel） #

假设起始时间存于单元格A1，结束时间存于B1，公式如下：

=SUMPRODUCT(
    --(WEEKDAY(ROW(INDIRECT(A1&":"&INT(B1))),2)<=5),
    IF(ROW(INDIRECT(A1&":"&INT(B1)))=INT(A1),
        MAX(0, MIN(INT(A1)+TIME(16,30,0), B1) - MAX(A1, INT(A1)+TIME(6,30,0))),
        IF(ROW(INDIRECT(A1&":"&INT(B1)))=INT(B1),
            MAX(0, MIN(INT(B1)+TIME(16,30,0), B1) - MAX(INT(B1)+TIME(6,30,0), INT(B1))),
            TIME(10,0,0)
        )
    )
)

公式逻辑 #

1. 用WEEKDAY(...,2)判断日期是否为周一至周五（返回1-5即有效工作日）
2. 起始日：取起始时间与班次开始的较晚值，到结束时间与班次结束的较早值的差值，确保仅统计班次内时长
3. 结束日：同理计算当日班次1内的有效时长
4. 中间完整工作日：直接计入标准10小时

计算结果 #

该场景下班次1总工时为48.5小时（20日10小时+21-23日各10小时+24日8.5小时）

班次2（18:30-04:30）工时计算公式（Excel） #

同样基于A1（起始时间）、B1（结束时间），公式如下：

=SUMPRODUCT(
    --(WEEKDAY(ROW(INDIRECT(A1&":"&INT(B1)+1)),2)<=5),
    IF(ROW(INDIRECT(A1&":"&INT(B1)+1))=INT(A1),
        MAX(0, INT(A1)+TIME(24,0,0) - MAX(A1, INT(A1)+TIME(18,30,0))),
        IF(ROW(INDIRECT(A1&":"&INT(B1)+1))=INT(B1)+1,
            MAX(0, MIN(B1, INT(B1)+TIME(4,30,0)) - INT(B1)),
            IF(WEEKDAY(ROW(INDIRECT(A1&":"&INT(B1)+1))-1,2)<=5,
                TIME(10,0,0),
                0
            )
        )
    )
)

公式逻辑 #

1. 因班次2跨天，遍历日期范围延伸至结束日次日，覆盖跨天时段
2. 起始日：计算当日18:30至24:00的有效时长
3. 结束日次日：计算当日00:00至4:30且早于结束时间的时长（该场景中此部分为0）
4. 中间日期：判断前一日是否为工作日，若是则计入完整10小时（前一日18:30至当日4:30）

计算结果 #

该场景下班次2总工时为35.5小时（20日5.5小时+21-23日对应跨日班次各10小时）

注意事项 #

• 确保单元格A1、B1格式设置为日期时间
• 公式计算结果单元格需设置为时间或**数值（小数代表小时）**格式，避免显示为日期
• 公式已通过WEEKDAY函数自动排除周末的无效班次时长

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