|
本帖最后由 mzbao 于 2019-2-13 15:25 编辑
- Sub test()
- Dim d, arr(), temp, temp1, tempArr(), brr(), i%, j%, sName$, dDate As Date
-
- ' On Error Resume Next
- With ThisWorkbook.Worksheets("考勤")
- arr = .Range("A13:B" & .Cells(Rows.Count, 1).End(xlUp).row).Value
- End With
-
- Set d = CreateObject("Scripting.Dictionary")
- For i = 1 To UBound(arr)
- sName = arr(i, 1)
- dDate = DateValue(arr(i, 2))
- If TimeValue(arr(i, 2)) < #2:00:00 AM# Then dDate = dDate - 1
- d(sName & "@" & dDate) = d(sName & "@" & dDate) & "," & arr(i, 2)
- Next
- If d.Count = 0 Then Exit Sub
- temp = d.keys
- ' Debug.Print d.Count, UBound(temp)
- ReDim tempArr(1 To d.Count, 1 To 10)
- For i = 0 To UBound(temp)
- tempArr(i + 1, 1) = temp(i)
- temp1 = Split(d(temp(i)), ",")
- For j = 1 To UBound(temp1)
- tempArr(i + 1, j + 1) = temp1(j)
- Next
- Next
-
- Dim rArr(), k%, txt$, n%, nTxt$
- ReDim rArr(1 To 1)
- For i = 1 To UBound(tempArr)
- txt = "": nTxt = ""
- For j = 2 To UBound(tempArr, 2)
- If tempArr(i, j) = 0 Then Exit For
- n = sd(TimeValue(tempArr(i, j)))
- nTxt = nTxt & "," & n
- If n = 2 Then txt = txt & "," & "迟到[" & TimeValue(tempArr(i, j)) & "]"
- If n = 3 Then txt = txt & "," & "早退[" & TimeValue(tempArr(i, j)) & "]"
- Next j
- If InStr(nTxt, 2) = 0 And InStr(nTxt, 1) = 0 Then txt = txt & "," & "上班未打卡"
- If InStr(nTxt, 3) = 0 And InStr(nTxt, 4) = 0 Then txt = txt & "," & "下班未打卡"
-
- If Len(txt) Then
- k = k + 1
- ReDim Preserve rArr(1 To k)
- rArr(k) = tempArr(i, 1) & txt
- End If
- Next i
- ThisWorkbook.Worksheets("Sheet1").Range("A2").Resize(k) = Application.Transpose(rArr)
- End Sub
- Function sd(sj As Date)
- Select Case sj
- Case #12:00:00 AM# To #2:00:00 AM#
- sd = 4
- Case #2:01:00 AM# To #8:45:00 AM#
- sd = 1
- Case #8:45:01 AM# To #11:59:59 AM#
- sd = 2
- Case #12:00:00 PM# To #4:59:59 PM#
- sd = 3
- Case #5:00:00 PM# To #11:59:59 PM#
- sd = 4
- End Select
- End Function
复制代码 |
评分
-
1
查看全部评分
-
|