|
|
Sub A3计算每天的小时数()
' 遇到指定的符号时，取值
' 时间：2022-7-27
' 思路：取出一天中的最大值与最小值，二值之差即为时间，超过8小时45分的部分为加班
Sheet3.Select
Rows("4:400").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheet2.Select
x2 = 2
Do While Not (IsEmpty(Sheet2.Cells(x2, 3).Value))
x2 = x2 + 1
Loop
Range(Cells(2, 1), Cells(x2 - 1, 3)).Select
Selection.Copy
Sheet3.Select
Range("A4").Select
Selection.PasteSpecial
Range("A4").Select
x2 = 2
Do While Not (IsEmpty(Sheet2.Cells(x2, 3).Value)) ' 依表2循环
c_xm = Sheet2.Cells(x2, 3).Value
x3 = 4
Do While Not (IsEmpty(Sheet3.Cells(x3, 3).Value)) ' 在表5中寻找姓名
If Sheet3.Cells(x3, 3).Value = c_xm Then ' 如果找到，按日期循环
For y = 4 To 34
c_sj = Sheet2.Cells(x2, y).Value
If IsEmpty(c_sj) Then
Else
js = 0
ks = 1
For i = 1 To Len(c_sj)
If Mid(c_sj, i, 1) = Chr(10) Then
js = js + 1 ' 这个计数是为记录，换行符第几次出现
Sheet4.Cells(js, 1).Value = Mid(c_sj, ks, i - ks)
ks = i + 1
End If
Next i
Sheet4.Cells(js + 1, 1).Value = Mid(c_sj, ks)
minsj = "99:99"
maxsj = "00:00"
For j = 1 To js + 1
If Sheet4.Cells(j, 1).Value < minsj Then
minsj = Sheet4.Cells(j, 1).Value
End If
If Sheet4.Cells(j, 1).Value > maxsj Then
maxsj = Sheet4.Cells(j, 1).Value
End If
Next j
xz = Hour(maxsj) - Hour(minsj)
fz = Minute(maxsj) - Minute(minsj)
If fz < 0 Then ' 分钟为负数，需要借1小时，化为分钟
xz = xz - 1
fz = fz + 60
End If
If fz >= 30 Then
xz = xz - 1
fz = fz - 30 ' 这是减中午的11:30-1:00的一小时半。
Else
xz = xz - 2
fz = fz + 30
End If
If xz < 0 Then
Sheet3.Cells(x3, y).Value = "打卡异常"
Else
Sheet3.Cells(x3, y).Value = xz & "时" & fz & "分"
End If
End If
Next y
End If
x3 = x3 + 1
Loop
x2 = x2 + 1
Loop
End Sub
原来那个没找到，发上这个，你主要看原理吧 |
|
楼主: 18089790912
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2023-6-15 15:31
