Google Sheets中ARRAYFORMULA+TIMEVALUE函数报错问题求助

我通过表单向Google Sheets录入时间数据，数据分两种：一种是直接归档的时长（比如28:00:00），另一种是需要通过开始时间、结束时间、员工数计算的时长（比如8:00:00 AM-12:00:00 PM*10=40:00:00）。

单行公式能正常计算（包含30分钟休息扣除逻辑），但改成ARRAYFORMULA数组公式后，出现#VALUE!错误，提示“TIMEVALUE参数无法解析为日期/时间”。因为表格需要按生产日期定期排序，必须用数组公式适配行的动态变化，求解决方法。

原单行公式 #

=IF(ISBLANK(I2000) + ISBLANK(J2000), N2000 + O2000, SUMPRODUCT((TIMEVALUE(J2000) - TIMEVALUE(I2000)) - IF(AND(TIMEVALUE(I2000) < TIMEVALUE("12:00 PM"), TIMEVALUE(J2000) > TIMEVALUE("12:30 PM")), 1/48, 0), H2000))

原数组公式（报错） #

=ARRAYFORMULA(IF(ISBLANK(I2:I) + ISBLANK(J2:J), N2:N + O2:O, SUMPRODUCT((TIMEVALUE(J2:J) - TIMEVALUE(I2:I)) - IF(AND(TIMEVALUE(I2:I) < TIMEVALUE("12:00 PM"), TIMEVALUE(J2:J) > TIMEVALUE("12:30 PM")), 1/48, 0), H2:H)))

报错原因分析 #

• AND函数不支持数组运算：原公式里的AND(TIMEVALUE(I2:I) < ..., TIMEVALUE(J2:J) > ...)在数组中只会返回单个布尔值，无法逐行判断，导致逻辑失效。
• SUMPRODUCT与ARRAYFORMULA冲突：SUMPRODUCT本身会自动处理数组，但和ARRAYFORMULA叠加时，会导致计算范围混乱；同时空行调用TIMEVALUE会直接触发错误。
• 空值未做防护：当I/J列为空时，TIMEVALUE因无法解析空值报错，虽然外层有ISBLANK判断，但数组计算是并行的，错误会提前触发。

修正后的数组公式 #

=ARRAYFORMULA(
  IF(
    ISBLANK(I2:I) + ISBLANK(J2:J),
    N2:N + O2:O,
    IFERROR(
      (TIMEVALUE(J2:J) - TIMEVALUE(I2:I) - 
       IF(
         (TIMEVALUE(I2:I) < TIMEVALUE("12:00 PM")) * (TIMEVALUE(J2:J) > TIMEVALUE("12:30 PM")),
         1/48,
         0
       )) * H2:H,
      0
    )
  )
)

修改说明 #

• 用*替代AND：数组中用(条件1)*(条件2)实现逐行逻辑与，替代不支持数组的AND函数。
• 移除SUMPRODUCT：直接用乘法* H2:H实现员工数的批量计算，避免和ARRAYFORMULA冲突。
• 增加IFERROR防护：捕获空值或无效时间格式导致的TIMEVALUE错误，返回0避免整列报错。
• 保留原逻辑：完全继承单行公式的休息扣除规则（仅当开始时间早于12:00 PM且结束时间晚于12:30 PM时，扣除30分钟即1/48天）。

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