Excel如何处理跨午夜日期数据以准确统计员工在岗人数

适用于偶尔查询特定时点在岗人数的场景，不需要调整原始同步数据，也不需要写VBA。
假设原始考勤数据存放在A、B列，表头在第1行，你要查询的指定时点（标准日期时间格式，例如2021/1/2 02:00）存放在D2单元格，可直接使用如下公式：

=SUMPRODUCT(--((TIMEVALUE(LEFT(B2:B100,4)) + A2:A100 <= D2) * (IF(TIMEVALUE(RIGHT(B2:B100,4)) < TIMEVALUE(LEFT(B2:B100,4)), TIMEVALUE(RIGHT(B2:B100,4)) + A2:A100 +1, TIMEVALUE(RIGHT(B2:B100,4)) + A2:A100) >= D2)))

公式逻辑说明：

• 拆分每行的上下班时间字符串，转换为Excel可识别的时间序列值
• 自动判断跨午夜场景：如果下班时间早于上班时间，自动给下班时间加1天的序列值
• 批量判断指定时点是否落在每个员工的有效工作区间内，统计符合条件的行数即为在岗人数

提示：公式中的A2:A100、B2:B100可根据实际数据行数调整，如果原始数据使用Excel表（结构化表）存储，替换为对应的列名即可实现动态适配，无需后续手动修改范围。

适用于需要长期做多时点统计、生成考勤报表的场景，一次配置后可随原始同步数据自动刷新，全程无需手动操作。
操作步骤：

• 选中原始考勤数据区域，点击「数据」选项卡 -> 「从表格/区域」导入Power Query编辑器
• 依次添加3个自定义列：
  • 上班时间：Time.From(Text.Start([Time],4))
  • 下班时间：Time.From(Text.End([Time],4))
  • 跨天标识：if [下班时间] < [上班时间] then 1 else 0
• 再次添加自定义列生成完整工作区间，跨天时段会自动拆分为当日和次日两行：

  let
    当日区间 = [Date] & [上班时间] .. [Date] & #time(23,59,59),
    次日区间 = if [跨天标识] = 1 then {当日区间, [Date] + #duration(1,0,0,0) & #time(0,0,0) .. [Date] + #duration(1,0,0,0) & [下班时间]} else {当日区间}
  in
    次日区间
  

• 展开区间列，删除冗余字段后点击「关闭并上载」，后续原始同步数据更新后，只要右键刷新生成的表即可得到最新的完整考勤数据，可直接用于透视表统计等操作。

• 单次查询优先选方案1，操作成本最低
• 长期报表需求优先选方案2，稳定性和可扩展性更强，不需要维护VBA代码

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