求Google Sheets公式：计算特定工时与节假日下的工单响应时长（分钟）

规则说明 #

• 工作时间：周一至周日全天09:00-18:00（每日工作540分钟）
• 休息日：仅法定节假日（如1月1日、5月1日、12月25日）全天休息

问题分析 #

NETWORKDAYS.INTL单独使用无法满足需求——它仅能计算工作日天数，无法处理单日内部的时间区间差，且需要先设置正确的周末参数（此场景下无周末休息日），再结合时间拆分计算才能得到准确结果。

解决方案（Google Sheets公式） #

假设：

• 工单接收时间存于单元格A2
• 工单响应时间存于单元格B2
• 法定节假日列表存于列D:D（需录入所有法定节假日的纯日期，如2023/5/1）

使用以下公式计算总工作时长（分钟）：

=IF(INT(A2)=INT(B2),
 MAX(0, MIN(INT(A2)+TIME(18,0,0), B2) - MAX(INT(A2)+TIME(9,0,0), A2)),
 MAX(0, MIN(INT(A2)+TIME(18,0,0), INT(A2)+TIME(18,0,0)) - MAX(INT(A2)+TIME(9,0,0), A2)) +
 NETWORKDAYS.INTL(INT(A2)+1, INT(B2)-1, 11, D:D)*540 +
 MAX(0, MIN(INT(B2)+TIME(18,0,0), B2) - MAX(INT(B2)+TIME(9,0,0), INT(B2)))
)*1440

公式拆解 #

1. 同一天场景处理：若接收与响应时间在同一天，直接计算两个时间点在工作时段内的差值，用MAX(0, ...)避免出现负数结果。
2. 起始日剩余时长：计算接收时间到当日18:00的有效工作分钟数，若接收时间晚于18:00则记为0。
3. 中间完整工作日时长：NETWORKDAYS.INTL(...,11,...)参数11标记一周7天均为工作日，同时排除D:D列的法定节假日，得到中间有效工作日数后乘以每日540分钟。
4. 结束日有效时长：计算当日09:00到响应时间的有效工作分钟数，若响应时间早于09:00则记为0。
5. 单位转换：最后乘以1440将日期差（天数）转换为分钟数。

案例验证 #

接收时间：2023/4/30 17:30，响应时间：2023/5/2 09:30，法定节假日含2023/5/1

• 起始日剩余时长：30分钟（17:30到18:00）
• 中间工作日：2023/5/1为节假日，计0分钟
• 结束日时长：30分钟（09:00到09:30）
• 总时长：30+0+30=60分钟，符合预期

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