VBA检测同员工排班时间与日期范围重叠的重复问题

业务场景说明 #

• 排班表规则：所有员工排班以两类范围形式存储：
  • 日期范围：例如21/01/2021-26/01/2021，代表该班次覆盖的连续日期区间
  • 时间范围：例如11:00-18:00，代表每个覆盖日期内的固定上班时段
• 重复判定标准：同一名员工的两段排班，只要存在以下任意重叠情况，即判定为重复排班：
  • 两段排班的覆盖日期区间存在交叉
  • 同一段交叉覆盖的日期内，两段排班的每日上班时段存在交叉
  • 典型重复示例：同一名员工在21/04/2021-22/04/2021期间，被同时安排了11:00-18:00和13:00-15:00两个班次
• 现有代码缺陷：当前编写的VBA逻辑仅能识别两类重复：
  1. 字段完全一致的排班
  2. 仅时间字段前3个字符匹配的排班
     完全无法识别区间交叉类的重复排班

现有检测代码 #

Private Sub CommandButton1_Click()
Dim lrow As Long
Dim x As Integer
Dim y As Integer
Dim i As Integer

lrow = ActiveSheet.ListObjects("LeaveTracker").DataBodyRange.Rows.Count + 5
    shibuzim.ListObjects("LeaveTracker").ListColumns(2).DataBodyRange.Clear
For x = 5 To lrow
    For y = x + 1 To lrow
        If (Cells(x, 12).Value = Cells(y, 12).Value And _
           Cells(x, 13).Value = Cells(y, 13).Value And _
           Cells(x, 14).Value = Cells(y, 14).Value And _
           Cells(x, 17).Value = Cells(y, 17).Value And _
           Cells(x, 18).Value = Cells(y, 18).Value And _
           Cells(x, 20).Value = Cells(y, 20).Value) _
           Or _
               (Cells(x, 12).Value = Cells(y, 12).Value And _
                Cells(x, 13).Value = Cells(y, 13).Value And _
                Cells(x, 14).Value = Cells(y, 14).Value And _
                Left(Cells(x, 17).Value, 3) = Left(Cells(y, 17).Value, 3) And _
                Cells(x, 18).Value = Cells(y, 18).Value And _
                Cells(x, 20).Value = Cells(y, 20).Value) _
        Then
                Cells(x, 11).Value = "duplicate"
                Cells(y, 11).Value = "duplicate"
                MsgBox "line" & " " & x - 4 & " " & "with line" & " " & y - 4
        End If
    Next y
Next x

End Sub

代码修改核心逻辑 #

要实现区间重叠类重复的检测，需要替换原有的字面相等判断逻辑，核心步骤如下：

1. 前置校验：仅当两段排班属于同一名员工时，才进入重叠判断逻辑，避免跨员工排班误判
2. 数据拆分：将存储为文本格式的日期范围、时间范围字符串，通过范围分隔符拆分为独立的起止值，转换为可量化比对的日期、时间类型，不要直接用字符串做比对。拆分日期值时，要严格匹配单元格存储的dd/mm/yyyy格式做解析，避免自动转换时出现月、日错位的问题，导致日期重叠判断完全失准
3. 通用重叠校验规则：对拆分后的两个区间，套用通用区间重叠判定公式：

   对区间[S1, E1]和[S2, E2]，若满足S1 <= E2 且 S2 <= E1，则两个区间存在重叠

4. 校验顺序：先判断两个排班的日期区间是否重叠，只有日期存在交叉时，再判断交叉覆盖日期内的每日时间区间是否重叠，两个条件同时满足即判定为重复
5. 移除原代码中用Left(单元格,3)做时间匹配的无效判断逻辑，该逻辑仅能匹配字符前缀，无法覆盖真实的时间区间交叉场景

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