如何在Excel 2019中获取各班次最早起始与最晚结束时间

问题根源 #

Excel以0-1的小数存储时间（0=00:00，1=24:00），跨天夜班的结束时间（如02:00、06:00）数值小于起始时间23:00，直接用MIN/MAX会错误识别时间顺序，导致结果不符合需求。

具体解决方案 #

假设原始数据：班次列A2:A5、起始时间列B2:B5、结束时间列C2:C5；已提取唯一班次至D2:D3（Day Shift、Night Shift）。

方法一：数组公式（无需辅助列） #

计算最早起始时间

在E2单元格输入公式后按Ctrl+Shift+Enter触发数组计算：

=MIN(IF($A$2:$A$5=D2,IF($B$2:$B$5>=TIME(12,0,0),$B$2:$B$5,$B$2:$B$5+1)))-IF(MIN(IF($A$2:$A$5=D2,IF($B$2:$B$5>=TIME(12,0,0),$B$2:$B$5,$B$2:$B$5+1)))>=1,1,0)

逻辑：将夜班中早于12:00的起始时间加1（视为次日时间），取最小值后若超过24小时则减1还原，得到夜班实际最早起始时间23:00。

计算最晚结束时间

在F2单元格输入公式后按Ctrl+Shift+Enter触发数组计算：

=MAX(IF($A$2:$A$5=D2,IF($C$2:$C$5<TIME(12,0,0),$C$2:$C$5+1,$C$2:$C$5)))-IF(MAX(IF($A$2:$A$5=D2,IF($C$2:$C$5<TIME(12,0,0),$C$2:$C$5+1,$C$2:$C$5)))>=1,1,0)

逻辑：将夜班中早于12:00的结束时间加1，取最大值后减1还原，得到实际最晚结束时间06:00；白班时间无需调整，直接取最大值即可。

方法二：辅助列简化操作 #

1. 添加辅助列：
   • D列（调整后起始时间）：=IF(A2="Night Shift",IF(B2<TIME(12,0,0),B2+1,B2),B2)
   • E列（调整后结束时间）：=IF(A2="Night Shift",IF(C2<TIME(12,0,0),C2+1,C2),C2)
2. 计算结果：
   • 最早时间：=MINIFS(D:D,A:A,D2)-IF(MINIFS(D:D,A:A,D2)>=1,1,0)
   • 最晚时间：=MAXIFS(E:E,A:A,D2)-IF(MAXIFS(E:E,A:A,D2)>=1,1,0)
     此方法更直观，适合不熟悉数组公式的用户。

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