Excel计算班次与指定时段重叠小时数的通用公式实现

适用场景 #

• 跨天班次：例如班次22:00_{次日06:00，统计其中落在00:00}07:00的时长
• 普通非跨天班次：例如班次07:00_{22:00，统计其中落在18:00}20:00的时长

通用公式 #

直接粘贴到目标单元格即可使用：

=24*MAX(0, MIN(B2+(A2>B2), D2) - MAX(A2, C2) + MIN(B2, D2) - MAX(A2+(A2>B2), C2))

如果使用的Excel版本不支持逻辑值直接参与计算，可替换为兼容版本：

=24*MAX(0, MIN(IF(A2>B2,B2+1,B2), D2) - MAX(A2, C2) + MIN(B2, D2) - MAX(IF(A2>B2,A2+1,A2), C2))

规则说明 #

• 单元格对应关系：A2=班次开始时间，B2=班次结束时间，C2=目标时段开始时间，D2=目标时段结束时间
• 公式自动判断班次是否跨天，无需额外设置区分场景
• 无重叠时返回值为0，不会出现负数结果
• 若需要返回分钟为单位，将公式开头的24替换为1440即可

测试示例 #

跨天场景测试：A2=22:00，B2=06:00，C2=00:00，D2=07:00，返回结果为6，符合预期
普通班次测试：A2=07:00，B2=22:00，C2=18:00，D2=20:00，返回结果为2，符合预期

内容的提问来源于stack exchange，提问作者Streching my competence