求Google Sheets工单响应有效工时计算公式（含节假日规则）

条件说明 #

仅法定节假日（如1月1日、5月1日、12月25日）为全假期，不计入工时，需提前在表格内维护好法定节假日列表。

需求 #

编写Google Sheets公式，计算工单接收时间与响应时间之间的有效工时，单位为分钟。

示例场景 #

• CHAT工单接收时间：2022年12月31日（周六）13:00:00
• CHAT工单响应时间：2023年1月2日（周一）10:00:00

预期结果：180分钟

计算逻辑：周六剩余60分钟（13:00到14:00）未响应，周日为法定节假日不计入，周一消耗120分钟（8:00到10:00），总计60+120=180分钟。

公式实现 #

假设：

• 工单来源单元格为A2
• 接收时间单元格为B2
• 响应时间单元格为C2
• 法定节假日列表存放在$E:$E列（需自行填充所有法定节假日日期）

使用以下公式（新版Google Sheets直接回车即可，旧版需按Ctrl+Shift+Enter确认数组公式）：

=SUM(
  ARRAYFORMULA(
    IF(
      ISNA(VLOOKUP(TEXT(SEQUENCE(DAYS(C2,B2)+1,1,B2),"yyyy-mm-dd"),$E:$E,1,FALSE)),
      LET(
        current_date, SEQUENCE(DAYS(C2,B2)+1,1,B2),
        day_of_week, WEEKDAY(current_date,2),
        start_time, IF(day_of_week<=5,IF(A2="CHAT",TIME(8,0,0),TIME(9,0,0)),TIME(9,0,0)),
        end_time, IF(day_of_week<=5,IF(A2="CHAT",TIME(21,0,0),TIME(18,0,0)),TIME(14,0,0)),
        actual_start, MAX(current_date,IF(current_date=B2,B2,DATEVALUE(current_date)+start_time)),
        actual_end, MIN(current_date+1,IF(current_date=C2,C2,DATEVALUE(current_date)+end_time)),
        IF(actual_start<actual_end,(actual_end-actual_start)*1440,0)
      ),
      0
    )
  )
)

公式核心逻辑说明 #

1. 日期序列生成：SEQUENCE(DAYS(C2,B2)+1,1,B2)生成从接收时间到响应时间的完整日期区间序列。
2. 节假日过滤：通过VLOOKUP检查日期是否在节假日列表中，是则直接计0工时。
3. 工作时段匹配：根据工单来源和星期几，自动匹配对应时段的工作起止时间。
4. 有效时段计算：对起始日取「接收时间」与「当日工作开始时间」的最大值，对结束日取「响应时间」与「当日工作结束时间」的最小值，中间日期直接取完整工作时段。
5. 工时转换求和：将时间差转换为分钟（乘以1440，1天=1440分钟），最终求和得到总有效工时。

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