
'6.30-10:00 , 10:00-13.30 , 13.30-15.30 , 15.30-24.00
'第2行的14:26:55为什么定位在第二时段的下班,按规则应该定位于上班
Option Explicit
Sub 无法定位时段()
Dim a, i, j, m, n, tm
a = Range("a1:d" & Cells(Rows.Count, "a").End(xlUp).Row + 1).Value
ReDim b(1 To UBound(a), 1 To 2 + 4)
tm = Array(#5:59:59 AM#, #9:59:59 AM#, #1:29:59 PM#, #3:29:59 PM#, #11:59:59 PM#)
m = 1
For i = 1 To UBound(a) - 1
For j = 1 To 4
If a(i, 4) > tm(j - 1) And a(i, 4) <= tm(j) Then n = j + 2: Exit For
Next
If j = 5 Then MsgBox "无法定位时间段:" & a(i, 4): Exit Sub
If Len(b(m, n)) Then '时间段中已有数据
If n Mod 2 Then '上班
If a(i, 4) < b(m, n) Then b(m, n) = a(i, 4) '取时间小的
Else '下班
If a(i, 4) > b(m, n) Then b(m, n) = a(i, 4) '取时间大的
End If
Else
b(m, n) = a(i, 4)
End If
If a(i, 3) <> a(i + 1, 3) Or a(i, 1) <> a(i + 1, 1) Then
b(m, 1) = a(i, 1): b(m, 2) = a(i, 3)
m = m + 1
End If
Next
[g10].Resize(m, 6) = b
End Sub