楼主
wcymiss |
Q：如何判断到离时间段是否交叉？
A：vba代码如下：
- Sub test()
- Dim Arr, N&, I&, D_T, Arr2
- Arr = Range("b2:d" & Cells(Rows.Count, 2).End(3).Row)
- ReDim Arr2(LBound(Arr) To UBound(Arr), 1 To 1)
- For N = LBound(Arr) To UBound(Arr)
- If LCase(Arr(N, 3)) <> "ben" Then
- If Arr(N, 1) <> "" Then
- D_T = Split(Arr(N, 1), " ")
- If IsDate(D_T(0)) Then
- Arr(N, 1) = Format(CDate(D_T(0)), "yymmdd") & Format(TimeValue(D_T(UBound(D_T))), "hhmmss")
- Else
- Arr2(N, 1) = "无效数据"
- End If
- End If
- Erase D_T
- If Arr(N, 2) <> "" Then
- D_T = Split(Arr(N, 2), " ")
- If IsDate(D_T(0)) Then
- Arr(N, 2) = Format(CDate(D_T(0)), "yymmdd") & Format(TimeValue(D_T(UBound(D_T))), "hhmmss")
- Else
- Arr2(N, 1) = "无效数据"
- End If
- End If
- Erase D_T
- If N > LBound(Arr) And Arr2(N, 1) <> "无效数据" Then
- For I = LBound(Arr) To N - 1
- If (Arr(N, 1) >= Arr(I, 1) And Arr(N, 1) <= Arr(I, 2)) Or (Arr(N, 2) >= Arr(I, 1) And Arr(N, 2) <= Arr(I, 2)) Then
- Arr2(N, 1) = "与第" & I + 1 & "行时间重叠"
- Exit For
- End If
- Next I
- If Arr(N, 1) = "" Or Arr(N, 2) = "" And Arr2(N, 1) = "" Then
- Arr2(N, 1) = "数据不全"
- ElseIf Arr2(N, 1) = "" Then
- Arr2(N, 1) = "正常"
- End If
- End If
- End If
- Next N
- [e2].Resize(UBound(Arr2)) = Arr2
- End Sub
 更新.rar
|