跨午夜时段的白、中、夜班工时计算方法求助

需求说明 #

我有一个排班模板，包含班次的开始时间和结束时间，需要分别计算：

• 白班：6:00-16:00
• 中班：16:00-20:00
• 夜班：20:00-次日5:30

现有问题：

• 用IF结合比较运算符的公式冗余低效
• 跨午夜的班次工时计算逻辑复杂
• 最终需用ARRAYFORMULA实现批量计算（当前测试公式在BA列及以后）

示例：开始时间9:00 AM、结束时间9:00 PM，对应白班7小时、中班4小时、夜班1小时。

解决方案 #

核心思路是通过MAX/MIN计算时段重叠时长，同时处理跨天场景（结束时间早于开始时间时，将结束时间视为次日时间）。

单单元格测试公式 #

假设开始时间在A2，结束时间在B2：

白班工时

=MAX(0, MIN(B2, TIME(16,0,0)) - MAX(A2, TIME(6,0,0))) + IF(B2 < A2, MAX(0, TIME(16,0,0) - TIME(6,0,0)), 0)

中班工时

=MAX(0, MIN(B2, TIME(20,0,0)) - MAX(A2, TIME(16,0,0))) + IF(B2 < A2, MAX(0, TIME(20,0,0) - TIME(16,0,0)), 0)

夜班工时

=MAX(0, MIN(B2, TIME(23,59,59)) - MAX(A2, TIME(20,0,0))) + IF(B2 < A2, MAX(0, MIN(B2+1, TIME(5,30,0))), 0)

批量计算（ARRAYFORMULA） #

适配整列数据（从第2行开始，A列=开始时间，B列=结束时间）：

白班数组公式

=ARRAYFORMULA(IF(A2:A="", "", 
  MAX(0, MIN(B2:B, TIME(16,0,0)) - MAX(A2:A, TIME(6,0,0))) + 
  IF(B2:B < A2:A, MAX(0, TIME(16,0,0) - TIME(6,0,0)), 0)
))

中班数组公式

=ARRAYFORMULA(IF(A2:A="", "", 
  MAX(0, MIN(B2:B, TIME(20,0,0)) - MAX(A2:A, TIME(16,0,0))) + 
  IF(B2:B < A2:A, MAX(0, TIME(20,0,0) - TIME(16,0,0)), 0)
))

夜班数组公式

=ARRAYFORMULA(IF(A2:A="", "", 
  MAX(0, MIN(B2:B, TIME(23,59,59)) - MAX(A2:A, TIME(20,0,0))) + 
  IF(B2:B < A2:A, MAX(0, MIN(B2:B+1, TIME(5,30,0))), 0)
))

逻辑说明 #

1. 正常时段计算：用MAX(开始时间, 时段起始)和MIN(结束时间, 时段结束)取重叠区间，计算时长
2. 跨天处理：当结束时间<开始时间时，额外计算次日对应时段的完整时长（白/中班）或结束时间到次日5:30的时长（夜班）
3. 负时长规避：用MAX(0, ...)确保不会出现负工时结果
4. 批量计算：ARRAYFORMULA自动遍历整列，无需手动下拉公式

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