Google Sheets餐饮行业夜班时长计算问题求助

核心问题分析 #

你的公式存在两个关键错误：

1. TIME(24,0,0) 在Google Sheets中会被解析为 00:00:00（即0天），而非24小时，导致计算出负的时长值。
2. 使用文本 "0" 而非数值 0，干扰了时间值的求和逻辑。

修正后的基础公式 #

以下公式解决了负时长问题，并排除Grace的时长计算：

=IF(A2<>"Grace", 
  (IF(B2<>"Off", 1 - B2, 0) + 
   IF(C2<>"Off", 1 - C2, 0) + 
   IF(D2<>"Off", 1 - D2, 0) + 
   IF(E2<>"Off", 1 - E2, 0) + 
   IF(F2<>"Off", 1 + 1/24 - F2, 0) + 
   IF(G2<>"Off", 1 + 1/24 - G2, 0) + 
   IF(H2<>"Off", 1 - H2, 0)),
  0)

• 1 代表完整的一天（24小时），对应周一至周四、周日的下班时间（次日00:00）。
• 1 + 1/24 代表25小时，对应周五、周六的下班时间（次日01:00）。
• 用数值 0 替代文本 "0"，确保求和逻辑正确。

显示超过24小时的时长 #

计算完成后，需要设置单元格格式以显示累计时长：

1. 选中结果单元格。
2. 点击 格式 > 数字 > 自定义数字格式。
3. 输入 [h]:mm:ss 并确认（方括号表示显示总时长，而非24小时内的余数）。

简化数组公式（可选） #

使用数组公式可减少重复代码，自动处理整行的日期列：

=IF(A2<>"Grace",
  SUMPRODUCT(
    ARRAYFORMULA(
      IF(OR(B2:H2="Off", NOT(ISNUMBER(B2:H2))), 0, 
        IF(COLUMN(B2:H2) IN {6,7}, 1 + 1/24 - B2:H2, 1 - B2:H2)
      )
    )
  ),
  0)

• COLUMN(B2:H2) IN {6,7} 匹配周五（F列）和周六（G列），应用25小时的计算规则。
• NOT(ISNUMBER(B2:H2)) 跳过非时间值（如DELIVERY等文本标识），若需计算DELIVERY班次，需将其替换为对应开始时间或在公式中添加映射规则。

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