Google Sheets出差需请假工作日数计算公式优化需求

需求说明 #

在Google Sheets中，需基于出发日期、出发时间、返回到达日期、返回到达时间，计算「Biz Days」列的需请假工作日数，需满足全部规则要求。

计算规则 #

• 出发时间≥15:30，无需请假当天
• 返回到达时间≤7:00，无需请假当天
• 出发/到达日为周六/周日，无需请假
• 公司节假日（范围：Hols!$A$2:$A$1001）无需请假

完整计算公式 #

将以下公式填入「Biz Days」列对应单元格（以第2行为例）：

=NETWORKDAYS.INTL(B2,D2,1,Hols!$A$2:$A$1001)
- IF(AND(WEEKDAY(B2,2)<6,ISNA(MATCH(B2,Hols!$A$2:$A$1001,0)),C2>=TIME(15,30,0)),1,0)
- IF(AND(WEEKDAY(D2,2)<6,ISNA(MATCH(D2,Hols!$A$2:$A$1001,0)),E2<=TIME(7,0,0)),1,0)

公式拆解 #

1. 基础工作日计算：NETWORKDAYS.INTL(B2,D2,1,Hols!$A$2:$A$1001)
   计算从出发日（B2）到返回日（D2）的总工作日，自动排除周六周日（参数1）和指定范围的公司节假日。
2. 出发日请假减免：第一个IF判断
   当出发日是工作日（非周末、非节假日）且出发时间≥15:30时，从总工作日中减去1天（当天无需请假）。
3. 返回日请假减免：第二个IF判断
   当返回日是工作日（非周末、非节假日）且返回到达时间≤7:00时，从总工作日中减去1天（当天无需请假）。

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