请求基于班次起止时间计算工作时长的Excel公式（含示例数据）

给定参数 #

假设以下单元格对应时间值：

• A1：实际开始时间 12/6/23 3:00 PM
• B1：实际结束时间 12/7/23 8:00 PM
• C1：每日班次开始时间 11:00 AM（仅时间部分，无日期）
• D1：每日班次结束时间 8:00 PM（仅时间部分，无日期）

需求说明 #

实际工作跨两天：第一天从3PM开始到当日班次结束（8PM）暂停，次日从班次开始时间（11AM）复工到8PM结束，需计算符合班次规则的总工作时长，预期结果为14小时。

Excel计算公式 #

=MAX(0, MIN(INT(A1)+D1, B1) - MAX(A1, INT(A1)+C1)) + IF(INT(A1)=INT(B1), 0, MAX(0, MIN(B1, INT(B1)+D1) - (INT(B1)+C1)) + (INT(B1)-INT(A1)-1)*(D1-C1))

公式说明 #

1. 第一天工作时长计算：

   • 取「实际开始时间」和「当日班次开始时间」的较晚值作为有效起始点
   • 取「当日班次结束时间」和「实际结束时间」的较早值作为有效结束点
   • 两者差值即为第一天的有效工作时长，用MAX(0, ...)避免出现负数

2. 中间完整天数工作时长计算：

   • 如果开始和结束日期不同，计算中间完整天数的总时长：(结束日期天数 - 开始日期天数 - 1) × 每日班次时长

3. 最后一天工作时长计算：

   • 取「实际结束时间」和「当日班次结束时间」的较早值作为有效结束点
   • 减去「当日班次开始时间」得到最后一天的有效工作时长，同样用MAX(0, ...)确保非负

结果验证 #

代入参数后：

• 第一天：8PM - 3PM = 5小时
• 第二天：8PM - 11AM = 9小时
• 总时长：5 + 9 = 14小时，与预期一致

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