vba考勤处理

最新推荐文章于 2024-04-09 15:37:28 发布

weixin_30249203

最新推荐文章于 2024-04-09 15:37:28 发布

阅读量1k

点赞数 1

原文链接：http://www.cnblogs.com/zhenxides/p/6186740.html

版权

sheet1 格式

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

转载于:https://www.cnblogs.com/zhenxides/p/6186740.html