分享一段统计工作时间的VB代码
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells(3, "V") = "工作时长"
Cells(3, "X") = "姓名"
Cells(3, "Y") = "月度总工时"
Cells(3, "Z") = "标准工时"
Cells(3, "AA") = "日平均工时"
Range("V3").HorizontalAlignment = Excel.xlCenter
Range("V:V").ColumnWidth = 12
Range("Y:Y").ColumnWidth = 15
Range("Z:Z").ColumnWidth = 10
Range("AA:AA").ColumnWidth = 15
Range("Y3").HorizontalAlignment = Excel.xlCenter
namerow = 4
nametext = ""
Dim minutes
Dim summinutes
summinutes = 0
Dim days
days = 15
Dim begintime
Dim endtime
For m = 4 To 200
endtime = Cells(m, "F")
If endtime = "" Then '行末尾
'最后一个人统计
If m > 4 Then
'If summinutes >= 40 * 60 Then
Cells(namerow - 1, "Y") = (summinutes) \ 60 & "小时" & (summinutes) Mod 60 & "分钟"
Cells(namerow - 1, "Y").Interior.ColorIndex = 0
Cells(namerow - 1, "Z") = (8 * 60 * days) \ 60 & "小时" ' & (8 * 60 * days) Mod 60 & "分钟"
Cells(namerow - 1, "AA") = (summinutes \ days) \ 60 & "小时" & (summinutes \ days) Mod 60 & "分钟"
' Else
'Cells(namerow - 1, "Y") = (40 * 60 - summinutes) \ 60 & "小时" & (40 * 60 - summinutes) Mod 60 & "分钟"
If (summinutes - days * 8 * 60) < 0 Then
Cells(namerow - 1, "Y").Interior.ColorIndex = 3
End If
End If
'边框
'Range("X3","Y" & namerow)
Exit For
End If
If nametext <> Cells(m, "D") Then
nametext = Cells(m, "D")
Cells(namerow, "X") = nametext
'计算总计
If m > 4 Then
'If summinutes >= 40 * 60 Then
Cells(namerow - 1, "Y") = (summinutes) \ 60 & "小时" & (summinutes) Mod 60 & "分钟"
Cells(namerow - 1, "Y").Interior.ColorIndex = 0
Cells(namerow - 1, "Z") = (8 * 60 * days) \ 60 & "小时" ' & (8 * 60 * days) Mod 60 & "分钟"
Cells(namerow - 1, "AA") = (summinutes \ days) \ 60 & "小时" & (summinutes \ days) Mod 60 & "分钟"
'Else
' Cells(namerow - 1, "Y") = "缺" & (40 * 60 - summinutes) \ 60 & "小时" & (40 * 60 - summinutes) Mod 60 & "分钟"
If (summinutes - days * 8 * 60) < 0 Then
Cells(namerow - 1, "Y").Interior.ColorIndex = 3
End If
End If
summinutes = 0
namerow = namerow + 1
End If
begintime = endtime
For n = 7 To 21
If Cells(m, n) > endtime Then
endtime = Cells(m, n)
Else
Exit For '空单元格
End If
Next n
If Len(begintime) = 5 Then
minutes = Left(endtime, 2) * 60 + Right(endtime, 2) - (Left(begintime, 2) * 60 + Right(begintime, 2)) - 90 '工作总分钟
Else
minutes = 0
End If
If minutes > 0 Then
Cells(m, "V") = TimeSerial(0, minutes, 0)
summinutes = summinutes + minutes '总分钟
Else
Cells(m, "V") = ""
End If
Next m
End Sub
[解决办法]
感谢分享
[解决办法]
嗯，8错
[解决办法]
懂得分享，就懂得了编程的乐趣。
[解决办法]
楼上3头大牛都感谢了……
[解决办法]
感谢分享
[解决办法]
收藏了
[解决办法]
8错8错
