Excel跨午夜时间计算：工时表时段乘数匹配异常解决方案咨询

问题背景 #

开发工时表应用，需根据预设时段匹配工时乘数，支持24小时多班次、两次打卡处理午休，但跨午夜打卡场景（如首日21:00上班，次日2:00下班）的时段判断逻辑失效。当前G列公式：

=IF(OR(AND($C2>=$J$11,$C2<$K$11),AND($C2>=$L$11,D$2<$M$11)),"YES","no")

该公式无法处理跨午夜时段（如J11=21:00、K11=2:00），导致红色标记单元格应显示no却判断错误，黄色标记单元格应显示YES也未正确识别。

核心思路：用MOD函数处理时间循环 #

Excel中时间以0-1的小数存储（0=00:00，1=24:00），跨午夜的时段本质是起始时间大于结束时间（如21:00=0.875，2:00≈0.083）。利用MOD(time,1)可将任意时间归一到0-1范围，统一处理跨天和非跨天的时段判断。

修正后的公式 #

针对双时段（J11-K11和L11-M11）的场景，G列公式修改为：

=IF(OR(
    IF($J$11<$K$11, AND(MOD($C2,1)>=$J$11, MOD($C2,1)<$K$11), OR(MOD($C2,1)>=$J$11, MOD($C2,1)<$K$11)),
    IF($L$11<$M$11, AND(MOD($D2,1)>=$L$11, MOD($D2,1)<$M$11), OR(MOD($D2,1)>=$L$11, MOD($D2,1)<$M$11))
),"YES","no")

公式解释 #

1. MOD(time,1)：将打卡时间（无论跨天与否）转换为0-1范围内的标准时间值，消除跨天的日期影响。
2. IF(时段起始<时段结束, ..., ...)：
   • 当时段不跨午夜（如9:00-12:00）：直接判断打卡时间是否在[起始, 结束)区间内。
   • 当时段跨午夜（如21:00-2:00）：判断打卡时间是否≥起始时间（当天21:00之后）或**<结束时间**（次日00:00-2:00），覆盖跨天的所有符合时段。
3. OR(...)：保留原逻辑的双时段判断，只要任一时段匹配则返回YES。

验证场景 #

• 对于跨午夜时段21:00-2:00：
  • 打卡时间为22:00（当天）：MOD(22:00,1)=0.916，满足≥21:00，返回YES。
  • 打卡时间为1:00（次日）：MOD(1:00,1)=0.041，满足<2:00，返回YES。
  • 打卡时间为10:00（当天）：既不满足≥21:00也不满足<2:00，返回no，对应红色标记的修正需求。

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