使用NETWORKDAYS.INTL计算工作日/周末/节假日时长的跨天班次问题

问题根源 #

原公式依赖NETWORKDAYS.INTL返回的完整天数乘以单日时长G2，但跨天班次（如21:00至次日02:00）仅覆盖部分天数的时长，直接按完整天数计算会导致结果偏差。

解决方案：拆分跨天时间段计算 #

需要将跨天班次拆分为「当天剩余时段」、「次日起始时段」和「中间完整天数」三部分，分别判断各时段所属的日期类型（工作日/周末/节假日）后计算对应时长。

1. 工作日时长公式 #

=IF(INT(B2)=INT(D2),
    // 非跨天：判断当天是否为工作日，计算时段时长
    IF(NETWORKDAYS.INTL(INT(B2),INT(B2),"0000011",$T$2:$T$7)=1, (D2-B2)*24, 0),
    // 跨天：分三部分计算
    (NETWORKDAYS.INTL(INT(B2),INT(B2),"0000011",$T$2:$T$7)* (1-B2+INT(B2))*24) + 
    (NETWORKDAYS.INTL(INT(D2),INT(D2),"0000011",$T$2:$T$7)* (D2-INT(D2))*24) + 
    (NETWORKDAYS.INTL(INT(B2)+1,INT(D2)-1,"0000011",$T$2:$T$7)*G2)
)

• 逻辑说明：
  • 非跨天：若当天为工作日，计算班次时长；否则返回0。
  • 跨天：计算当天剩余的工作日时长 + 次日起始的工作日时长 + 中间完整工作日的总时长。

2. 周末时长公式 #

将工作日公式中的周末参数"0000011"替换为"1111100"即可：

=IF(INT(B2)=INT(D2),
    IF(NETWORKDAYS.INTL(INT(B2),INT(B2),"1111100",$T$2:$T$7)=1, (D2-B2)*24, 0),
    (NETWORKDAYS.INTL(INT(B2),INT(B2),"1111100",$T$2:$T$7)* (1-B2+INT(B2))*24) + 
    (NETWORKDAYS.INTL(INT(D2),INT(D2),"1111100",$T$2:$T$7)* (D2-INT(D2))*24) + 
    (NETWORKDAYS.INTL(INT(B2)+1,INT(D2)-1,"1111100",$T$2:$T$7)*G2)
)

3. 节假日时长公式 #

原公式=SUM(I2-Q2-R2)可保留，但需确保I2为班次总时长（公式：=(D2-B2)*24），Q2为工作日时长，R2为周末时长，逻辑为总时长减去工作日、周末时长，剩余即为节假日时长。

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