计算加班时间

3190496160 · 发表于 2021-7-30 17:27

sulli112 发表于 2021-7-30 17:05
我查看了一下，假如打卡时间多3分钟的话，直接为0.5H,请问这个0.5H可以设定范围吗？15-30分钟内才四舍五 ...

你自己好好看看吧，代码是严格按你3楼的说法来写的，绝对不会出现你说的情况的。

3190496160 · 发表于 2021-7-30 17:27

  fz = Minute(sf)
         If fz < 15 Then
            js = 0
         ElseIf fz >= 15 And fz <= 30 Then
            js = 0.5
         ElseIf fz > 30 Then
            js = 1
         End If

3190496160 · 发表于 2021-7-30 17:28

我想这几句你应该是可以看懂的吧。

sulli112 · 发表于 2021-7-30 19:20

3190496160 发表于 2021-7-30 17:28
我想这几句你应该是可以看懂的吧。

谢谢啦，我看懂了，刚开始没有留意到这个

，我修改了一下达到我想要的结果了，非常感谢

3190496160 · 发表于 2021-7-30 20:20

sulli112 发表于 2021-7-30 19:20
谢谢啦，我看懂了，刚开始没有留意到这个，我修改了一下达到我想要的结果了，非常感谢

呵呵呵，还需要你修改吗？？？

sulli112 · 发表于 2021-7-30 21:47

3190496160 发表于 2021-7-30 20:20
呵呵呵，还需要你修改吗？？？

不是，我只是改了一下那个分钟数，其他没有修动大神您的数据，不好意思，可能言语冒犯到您了

sulli112 · 发表于 2021-8-26 14:23

Sub 考勤统计()
Dim d As Object
Dim ar As Variant
Dim i As Long
Dim br()
Set d = CreateObject("scripting.dictionary")
With Sheets("考勤")
r = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("i2:j" & r) = Empty
ar = .[a1].CurrentRegion
ReDim br(1 To UBound(ar), 1 To 6)
For i = 2 To UBound(ar)
If Trim(ar(i, 2)) <> "" And Trim(ar(i, 4)) <> "" Then
rq = CDate(Format(ar(i, 4), "yyyy/mm/dd"))
zf = Trim(ar(i, 2)) & "|" & rq
t = d(zf)
If t = "" Then
k = k + 1
d(zf) = k
t = k
For j = 1 To 3
br(k, j) = ar(i, j)
Next j
br(k, 4) = rq
br(k, 5) = Weekday(rq, 2)
End If
sf = CDate(Format(ar(i, 4), "h:mm:ss"))
fz = Minute(sf)
If fz < 15 Then
js = 0
ElseIf fz >= 15 And fz <= 45 Then
js = 0.5
ElseIf fz > 45 Then
js = 1
End If
If br(t, 5) < 6 Then
br(t, 6) = Hour(sf) - Hour("8:00:00") - 9 + js
Else
br(t, 6) = (Hour(sf) - Hour("8:00:00") - 1 + js)
End If
End If
Next i
End With
With Sheets("统计考勤")
.[a1].CurrentRegion.Offset(1) = Empty
.[a2].Resize(k, UBound(br, 2)) = br
End With
MsgBox "ok!"
End Sub

复制代码

红彬 · 发表于 2021-8-29 03:27

我天，好复杂

sulli112 · 发表于 2022-2-28 10:43

3190496160 发表于 2021-7-30 15:30

Sub 考勤统计()
Dim d As Object
Dim ar As Variant
Dim i As Long
Dim br()
Set d = CreateObject("scripting.dictionary")
With Sheets("考勤")
r = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("i2:j" & r) = Empty
ar = .[a1].CurrentRegion
ReDim br(1 To UBound(ar), 1 To 6)
For i = 2 To UBound(ar)
If Trim(ar(i, 2)) <> "" And Trim(ar(i, 4)) <> "" Then
rq = CDate(Format(ar(i, 4), "yyyy/mm/dd"))
zf = Trim(ar(i, 2)) & "|" & rq
t = d(zf)
If t = "" Then
k = k + 1
d(zf) = k
t = k
For j = 1 To 3
br(k, j) = ar(i, j)
Next j
br(k, 4) = rq
br(k, 5) = Weekday(rq, 2)
End If
sf = CDate(Format(ar(i, 4), "h:mm:ss"))
fz = Minute(sf)
If fz < 15 Then
js = 0
ElseIf fz >= 15 And fz <= 45 Then
js = 0.5
ElseIf fz > 45 Then
js = 1
[color=DarkOrange] End If
If br(t, 5) < 6 Then
br(t, 6) = Hour(sf) - Hour("8:00:00") - 9 + js
Else
br(t, 6) = (Hour(sf) - Hour("8:00:00") - 1 + js)[/color]
End If
End If
Next i
End With
With Sheets("统计考勤")
.[a1].CurrentRegion.Offset(1) = Empty
.[a2].Resize(k, UBound(br, 2)) = br
End With
MsgBox "ok!"
End Sub

复制代码

我想问一下，如果想要将时间改为从18：30开始为加班时间，是不是红色字段修改为下面这段吗？非常感谢

End If
         If br(t, 5) < 6 Then
            br(t, 6) = Hour(sf) - Hour("8:00:00") - 10.5 + js
         Else
            br(t, 6) = (Hour(sf) - Hour("8:00:00") - 10.5 + js)

		自动登录	找回密码
密码			免费注册

[求助] 计算加班时间

评分