自动统计出勤异常

grf1973

Sub 筛选()

On Error Resume Next
Application.ScreenUpdating = False
Sheet1.Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "筛选"
With Sheets("筛选")
    maxrow = .[A65536].End(3).Row
    .Columns("H:I").Insert Shift:=xlToRight
    .[h1] = "上班时间确认": .[i1] = "下班时间确认"
    For i = maxrow To 2 Step -1
        ts = .Cells(i, "E"): tx = .Cells(i, "F")
        hs = Hour(ts): ms = Minute(ts)
        hx = Hour(tx): mx = Minute(tx)
        If hs > 8 Or (hs = 8 And ts > 30) Then .Cells(i, "H") = "上班时间异常"
        If hx < 17 Or (hx = 17 And tx < 30) Then .Cells(i, "I") = "下班时间异常"
        If .Cells(i, "H") = "" And .Cells(i, "I") = "" Then Rows(i).Delete
    Next
End With
Application.ScreenUpdating = True
End Sub