Sub 统计人数()

'A为号码C姓名 D时间

Dim j As Integer

j = 1

For i = 2 To 100000 Step 1

If Range("a" & i).Value <> Range("a" & i + 1).Value Then

 j = j + 1

Sheets("sheet3").Range("a" & j).Value = Range("a" & i).Value

Sheets("sheet3").Range("b" & j).Value = Range("c" & i).Value

Else: If Range("a" & i).Value = "" Then End

End If

Next i

End Sub

Sub 统计迟到早退()

'Sheets("sheet3").Range("c" & j).Value = 1

Dim a, b, c, j As Integer

j = 1

For i = 2 To 100000 Step 1

               a = Sheets("sheet1").Range("a" & i).Value

               b = Sheets("sheet3").Range("a" & j).Value

               c = Sheets("sheet1").Range("d" & i).Value

               If a = "" Then Exit For

   If a - b = 0 Then

          Sheets("sheet3").Range("c" & j).Value = Sheets("sheet3").Range("c" & j).Value + 0.5

          '判断时间  经理9:00 员工9:20

          d = Val(Format(c, "hhnn"))

          e = Format(c, "d")

             'If d - 900 > 0 And d - 900 < 300 Then

             '   Sheets("sheet3").Range("i" & j).Value = Sheets("sheet3").Range("i" & j).Value & e & "、"

             ' End If

            If d - 920 > 0 And d - 920 < 280 Then

              Sheets("sheet3").Range("i" & j).Value = Sheets("sheet3").Range("i" & j).Value & e & "迟到、"

            End If

            If d - 1200 < 600 And d - 1200 > 0 Then

              Sheets("sheet3").Range("h" & j).Value = Sheets("sheet3").Range("h" & j).Value & e & "早退、"

            End If

Else

    j = j + 1

    Sheets("sheet3").Range("c" & j).Value = 0.5

'MsgBox (a)

'MsgBox (b)

End If

Next i

End Sub

Sub 统计上下午未打卡()

Dim a, b, c, j As Integer

j = 1

For i = 2 To 100000 Step 1

               a = Sheets("sheet1").Range("a" & i).Value

               l = Sheets("sheet1").Range("a" & i + 1).Value

               b = Sheets("sheet3").Range("a" & j).Value

               c = Sheets("sheet1").Range("d" & i).Value

               d = Sheets("sheet1").Range("d" & i + 1).Value

               g = Sheets("sheet1").Range("d" & i - 1).Value

               e = Format(c, "d")

               f = Format(d, "d")

               h = Format(g, "d")

               k = Format(c, "h")

               If a = "" Then Exit For

If a - b = 0 Then

               If e <> f And e <> h And k < 12 Then

               Sheets("sheet3").Range("j" & j).Value = Sheets("sheet3").Range("j" & j).Value & e & "下午、"

               End If

                If e <> f And e <> h And k > 12 Then

               Sheets("sheet3").Range("j" & j).Value = Sheets("sheet3").Range("j" & j).Value & e & "上午、"

               End If

Else: j = j + 1

            If e <> f And k > 12 Then

              Sheets("sheet3").Range("j" & j).Value = e & "上午\、"

            End If

            If k < 12 And e <> f Then

             Sheets("sheet3").Range("j" & j).Value = e & "下午\、"

            End If

End If

Next i

End Sub

Sub 填写第一行()

Sheets("sheet3").Range("a" & 1).Value = "考勤号码"

Sheets("sheet3").Range("b" & 1).Value = "姓名"

Sheets("sheet3").Range("c" & 1).Value = "出勤天数"

Sheets("sheet3").Range("h" & 1).Value = "早退"

Sheets("sheet3").Range("i" & 1).Value = "迟到"

Sheets("sheet3").Range("j" & 1).Value = "未打卡"

End Sub

Sub test()

'Dim arry(1 To 2) As Integer

c = Sheets("sheet1").Range("d" & 29).Value

'arry(1) = 23

'arry(2) = 909

a = DateAdd("n", 20, c)

MsgBox (Format(c, "hh") > 12)

End Sub