考勤打卡机导出的excel考勤时间表如何生成实用的考勤表
该excel表有如下结构
| 姓名\日期 | 周1 | 周2 | 周3 | 周4 | 周5 |
| 张三 | 7:35 18:02 |
7:35 18:02 |
7:46 | 17:56 | |
| 李四 | 7:35 18:02 |
7:02 18:00 18:02 |
需要判断每天是否迟到早退，并生成考勤表
用如下自定义函数cal或者cals
Public Function cal(ByVal cs As Range) As Integer \'计算单元格并返回相应值如下 \'没打卡 1 \'正常上下班 0 \'迟到 2 \'迟到超过2小时 11 \'早退 3 \'早退超过2小时 12 \'迟到+早退 5 \'迟到+早退分别都超过2小时 \'只有上班打卡，没有下班打卡 4 \'只有上班打卡，迟到，没下班打卡 2+4=6 \'只有下班打卡，没有上班打卡 7 \'只有下班打卡，早退，没有上班打卡 3+7=10 \'一天3次打卡记录30 \'一天n次以上（n>3)打卡记录 N*10=10n \'异常打卡 10:00-15:30打卡 8 Const morning_time = "08:00" Const evening_time = "17:30" \'超过120分钟算旷工 Const offset_point = 120 \'单元格每行是一个数组元素lines() Dim lines() As String \'单元格有多少行 Dim count As Integer \'没打卡-空值，返回1 相当于count=0 If IsEmpty(cs) Then cal = 1 Exit Function End If count = Len(cs.Text) - Len(Application.WorksheetFunction.Substitute(cs.Text, Chr(10), "")) + 1 \'大于3次以上的打卡记录返回10*n If count >= 3 Then cal = count * 10 Exit Function End If \'处理只有一条记录的，并计算是否上班 If count = 1 Then Dim offset_morning, offset_evening As Integer offset_morning = Hour(CDate(cs.Text) - CDate(morning_time)) * 60 + Minute(CDate(cs.Text) - CDate(morning_time)) offset_evening = Hour(CDate(cs.Text) - CDate(evening_time)) * 60 + Minute(CDate(cs.Text) - CDate(evening_time)) \'MsgBox offset_morning If CDate(cs.Text) <= CDate(morning_time) Then \' 只有上班打卡，没有下班打卡 cal = 4 \'MsgBox cal Exit Function End If If CDate(cs.Text) >= CDate(evening_time) Then \' 只有下班打卡，没有上班班打卡 cal = 7 Exit Function End If If (CDate(cs.Text) > CDate(morning_time)) And (CDate(cs.Text) < CDate(evening_time)) Then If offset_morning < 120 Then \'只有上班打卡，迟到，没有下班打卡 cal = 6 ElseIf offset_evening < 120 Then \'只有下班打卡，早退，没有上班打卡 cal = 10 Else \'异常打卡 cal = 8 End If Exit Function End If \'count=1 End If \'count=2 Dim line1, line2 As String Dim moring_tmp, evening_tmp As Integer morning_tmp = 0 evening_tmp = 0 \'提取第一行打卡和第二行打卡时间line1是上班打卡，line2是下班打卡 line1 = Left(cs.Text, 5) line2 = Split(cs.Text, Chr(10))(1) \'MsgBox "line1:" & line1 \'MsgBox "line2:" & line2 \'分别给出 morning_tmp值:如果line1早于8点则返回0，晚于8点且不超过2小时为迟到2,超过2小时取值11 offset_morning = Hour(CDate(line1) - CDate(morning_time)) * 60 + Minute(CDate(line1) - CDate(morning_time)) offset_evening = Hour(CDate(line2) - CDate(evening_time)) * 60 + Minute(CDate(line2) - CDate(evening_time)) \'MsgBox offset_morning \'MsgBox offset_evening If CDate(line1) <= CDate(morning_time) Then \' 正常上班打卡早于8点 morning_tmp = 0 ElseIf (CDate(line1) > CDate(morning_time)) And (offset_morning < 120) Then \' 上班迟到 morning_tmp = 2 Else \' 上班迟到超过2小时 morning_tmp = 11 End If \'分别给出 evening_tmp值:如果line2晚于于17:30则返回0，早于17:30且不超过2小时为早退取值3,超过2小时给值12， If CDate(line2) >= CDate(evening_time) Then \' 正常下班打卡 evening_tmp = 0 ElseIf (CDate(line2) < CDate(evening_time)) And (offset_evening < 120) Then \' 早退 evening_tmp = 3 Else \' 早退超过2小时 evening_tmp = 12 End If \'最终cal= morning_tmp+evening_tmp cal = morning_tmp + evening_tmp End Function Public Function cals(ByVal cs As Range) As String \'计算单元格并返回相应值如下 \'没打卡 1 \'正常上下班 0 \'迟到 2 \'迟到超过2小时 11 \'早退 3 \'早退超过2小时 12 \'迟到+早退 5 \'迟到+早退分别都超过2小时 \'只有上班打卡，没有下班打卡 4 \'只有上班打卡，迟到，没下班打卡 2+4=6 \'只有下班打卡，没有上班打卡 7 \'只有下班打卡，早退，没有上班打卡 3+7=10 \'一天3次打卡记录30 \'一天n次以上（n>3)打卡记录 N*10=10n \'异常打卡 10:00-15:30打卡 8 Const morning_time = "08:00" Const evening_time = "17:30" \'超过120分钟算旷工 Const offset_point = 120 \'单元格每行是一个数组元素lines() Dim lines() As String \'单元格有多少行 Dim count As Integer \'没打卡-空值，返回1 相当于count=0 If IsEmpty(cs) Then cals = "休息" Exit Function End If count = Len(cs.Text) - Len(Application.WorksheetFunction.Substitute(cs.Text, Chr(10), "")) + 1 \'大于3次以上的打卡记录返回10*n If count >= 3 Then cals = "异常打卡" & CStr(count) & "次" Exit Function End If \'处理只有一条记录的，并计算是否上班 If count = 1 Then Dim offset_morning, offset_evening As Integer offset_morning = Hour(CDate(cs.Text) - CDate(morning_time)) * 60 + Minute(CDate(cs.Text) - CDate(morning_time)) offset_evening = Hour(CDate(cs.Text) - CDate(evening_time)) * 60 + Minute(CDate(cs.Text) - CDate(evening_time)) \'MsgBox offset_morning If CDate(cs.Text) <= CDate(morning_time) Then \' 只有上班打卡，没有下班打卡 cals = "无下班打卡" \'MsgBox cal Exit Function End If If CDate(cs.Text) >= CDate(evening_time) Then \' 只有下班打卡，没有上班班打卡 cals = "无上班打卡" Exit Function End If If (CDate(cs.Text) > CDate(morning_time)) And (CDate(cs.Text) < CDate(evening_time)) Then If offset_morning < 120 Then \'只有上班打卡，迟到，没有下班打卡 cals = "迟到,无下班打卡" ElseIf offset_evening < 120 Then \'只有下班打卡，早退，没有上班打卡 cals = "早退,无上班打卡" Else \'异常打卡 cals = "10点15点30之间异常打卡" End If Exit Function End If \'count=1 End If \'count=2 Dim line1, line2 As String Dim moring_tmp, evening_tmp As Integer morning_tmp = 0 evening_tmp = 0 \'提取第一行打卡和第二行打卡时间line1是上班打卡，line2是下班打卡 line1 = Left(cs.Text, 5) line2 = Split(cs.Text, Chr(10))(1) \'MsgBox "line1:" & line1 \'MsgBox "line2:" & line2 \'分别给出 morning_tmp值:如果line1早于8点则返回0，晚于8点且不超过2小时为迟到2,超过2小时取值11 offset_morning = Hour(CDate(line1) - CDate(morning_time)) * 60 + Minute(CDate(line1) - CDate(morning_time)) offset_evening = Hour(CDate(line2) - CDate(evening_time)) * 60 + Minute(CDate(line2) - CDate(evening_time)) \'MsgBox offset_morning \'MsgBox offset_evening If CDate(line1) <= CDate(morning_time) Then \' 正常上班打卡早于8点 morning_tmp = 0 ElseIf (CDate(line1) > CDate(morning_time)) And (offset_morning < 120) Then \' 上班迟到 morning_tmp = 2 Else \' 上班迟到超过2小时 morning_tmp = 11 End If \'分别给出 evening_tmp值:如果line2晚于于17:30则返回0，早于17:30且不超过2小时为早退取值3,超过2小时给值12， If CDate(line2) >= CDate(evening_time) Then \' 正常下班打卡 evening_tmp = 0 ElseIf (CDate(line2) < CDate(evening_time)) And (offset_evening < 120) Then \' 早退 evening_tmp = 3 Else \' 早退超过2小时 evening_tmp = 12 End If \'最终cal= morning_tmp+evening_tmp \'MsgBox morning_tmp + evening_tmp Select Case (morning_tmp + evening_tmp) Case 0 cals = "全勤" Case 1 cals = "休息" Case 2 cals = "迟到" Case 3 cals = "早退" Case 4 cals = "无下班打卡" Case 5 cals = "迟到+早退" Case 6 cals = "上班迟到，下班没打卡" Case 7 cals = "无上班打卡" Case 8 cals = "10:00-15:30异常打卡" Case 10 cals = "早退,无上班打卡" Case 11 cals = "迟到超2小时" Case 12 cals = "早退超2小时" Case 23 cals = "迟到早退都超2小时" Case Else calse = "异常打卡" & CStr(morning_tmp + evening_tmp) & "次" End Select End Function
Public Function cal(ByVal cs As Range) As Integer
\’计算单元格并返回相应值如下\’没打卡 1\’正常上下班 0\’迟到 2\’迟到超过2小时 11\’早退 3\’早退超过2小时 12\’迟到+早退 5\’迟到+早退分别都超过2小时\’只有上班打卡，没有下班打卡 4\’只有上班打卡，迟到，没下班打卡 2+4=6\’只有下班打卡，没有上班打卡 7\’只有下班打卡，早退，没有上班打卡 3+7=10\’一天3次打卡记录30\’一天n次以上（n>3)打卡记录 N*10=10n\’异常打卡 10:00-15:30打卡 8Const morning_time = “08:00″Const evening_time = “17:30″\’超过120分钟算旷工Const offset_point = 120
\’单元格每行是一个数组元素lines()Dim lines() As String\’单元格有多少行Dim count As Integer
\’没打卡-空值，返回1 相当于count=0If IsEmpty(cs) Then cal = 1 Exit FunctionEnd If
count = Len(cs.Text) – Len(Application.WorksheetFunction.Substitute(cs.Text, Chr(10), “”)) + 1
\’大于3次以上的打卡记录返回10*nIf count >= 3 Then cal = count * 10 Exit FunctionEnd If
\’处理只有一条记录的，并计算是否上班If count = 1 Then
Dim offset_morning, offset_evening As Integer
offset_morning = Hour(CDate(cs.Text) – CDate(morning_time)) * 60 + Minute(CDate(cs.Text) – CDate(morning_time))offset_evening = Hour(CDate(cs.Text) – CDate(evening_time)) * 60 + Minute(CDate(cs.Text) – CDate(evening_time))\’MsgBox offset_morning If CDate(cs.Text) <= CDate(morning_time) Then \’ 只有上班打卡，没有下班打卡 cal = 4 \’MsgBox cal Exit Function End If If CDate(cs.Text) >= CDate(evening_time) Then \’ 只有下班打卡，没有上班班打卡 cal = 7 Exit Function End If If (CDate(cs.Text) > CDate(morning_time)) And (CDate(cs.Text) < CDate(evening_time)) Then If offset_morning < 120 Then \’只有上班打卡，迟到，没有下班打卡 cal = 6 ElseIf offset_evening < 120 Then \’只有下班打卡，早退，没有上班打卡 cal = 10 Else \’异常打卡 cal = 8 End If Exit Function End If \’count=1End If
\’count=2Dim line1, line2 As StringDim moring_tmp, evening_tmp As Integermorning_tmp = 0evening_tmp = 0\’提取第一行打卡和第二行打卡时间line1是上班打卡，line2是下班打卡line1 = Left(cs.Text, 5)line2 = Split(cs.Text, Chr(10))(1)\’MsgBox “line1:” & line1\’MsgBox “line2:” & line2
\’分别给出 morning_tmp值:如果line1早于8点则返回0，晚于8点且不超过2小时为迟到2,超过2小时取值11
offset_morning = Hour(CDate(line1) – CDate(morning_time)) * 60 + Minute(CDate(line1) – CDate(morning_time))offset_evening = Hour(CDate(line2) – CDate(evening_time)) * 60 + Minute(CDate(line2) – CDate(evening_time))
\’MsgBox offset_morning\’MsgBox offset_evening
If CDate(line1) <= CDate(morning_time) Then \’ 正常上班打卡早于8点 morning_tmp = 0 ElseIf (CDate(line1) > CDate(morning_time)) And (offset_morning < 120) Then \’ 上班迟到 morning_tmp = 2 Else \’ 上班迟到超过2小时 morning_tmp = 11 End If
\’分别给出 evening_tmp值:如果line2晚于于17:30则返回0，早于17:30且不超过2小时为早退取值3,超过2小时给值12，
If CDate(line2) >= CDate(evening_time) Then \’ 正常下班打卡 evening_tmp = 0 ElseIf (CDate(line2) < CDate(evening_time)) And (offset_evening < 120) Then \’ 早退 evening_tmp = 3 Else \’ 早退超过2小时 evening_tmp = 12 End If
\’最终cal= morning_tmp+evening_tmpcal = morning_tmp + evening_tmp
End Function
Public Function cals(ByVal cs As Range) As String
\’计算单元格并返回相应值如下\’没打卡 1\’正常上下班 0\’迟到 2\’迟到超过2小时 11\’早退 3\’早退超过2小时 12\’迟到+早退 5\’迟到+早退分别都超过2小时\’只有上班打卡，没有下班打卡 4\’只有上班打卡，迟到，没下班打卡 2+4=6\’只有下班打卡，没有上班打卡 7\’只有下班打卡，早退，没有上班打卡 3+7=10\’一天3次打卡记录30\’一天n次以上（n>3)打卡记录 N*10=10n\’异常打卡 10:00-15:30打卡 8Const morning_time = “08:00″Const evening_time = “17:30″\’超过120分钟算旷工Const offset_point = 120
\’单元格每行是一个数组元素lines()Dim lines() As String\’单元格有多少行Dim count As Integer
\’没打卡-空值，返回1 相当于count=0If IsEmpty(cs) Then cals = “休息” Exit FunctionEnd If
count = Len(cs.Text) – Len(Application.WorksheetFunction.Substitute(cs.Text, Chr(10), “”)) + 1
\’大于3次以上的打卡记录返回10*nIf count >= 3 Then cals = “异常打卡” & CStr(count) & “次” Exit FunctionEnd If
\’处理只有一条记录的，并计算是否上班If count = 1 Then
Dim offset_morning, offset_evening As Integer
offset_morning = Hour(CDate(cs.Text) – CDate(morning_time)) * 60 + Minute(CDate(cs.Text) – CDate(morning_time))offset_evening = Hour(CDate(cs.Text) – CDate(evening_time)) * 60 + Minute(CDate(cs.Text) – CDate(evening_time))\’MsgBox offset_morning If CDate(cs.Text) <= CDate(morning_time) Then \’ 只有上班打卡，没有下班打卡 cals = “无下班打卡” \’MsgBox cal Exit Function End If If CDate(cs.Text) >= CDate(evening_time) Then \’ 只有下班打卡，没有上班班打卡 cals = “无上班打卡” Exit Function End If If (CDate(cs.Text) > CDate(morning_time)) And (CDate(cs.Text) < CDate(evening_time)) Then If offset_morning < 120 Then \’只有上班打卡，迟到，没有下班打卡 cals = “迟到,无下班打卡” ElseIf offset_evening < 120 Then \’只有下班打卡，早退，没有上班打卡 cals = “早退,无上班打卡” Else \’异常打卡 cals = “10点15点30之间异常打卡” End If Exit Function End If \’count=1End If
\’count=2Dim line1, line2 As StringDim moring_tmp, evening_tmp As Integermorning_tmp = 0evening_tmp = 0\’提取第一行打卡和第二行打卡时间line1是上班打卡，line2是下班打卡line1 = Left(cs.Text, 5)line2 = Split(cs.Text, Chr(10))(1)\’MsgBox “line1:” & line1\’MsgBox “line2:” & line2
\’分别给出 morning_tmp值:如果line1早于8点则返回0，晚于8点且不超过2小时为迟到2,超过2小时取值11
offset_morning = Hour(CDate(line1) – CDate(morning_time)) * 60 + Minute(CDate(line1) – CDate(morning_time))offset_evening = Hour(CDate(line2) – CDate(evening_time)) * 60 + Minute(CDate(line2) – CDate(evening_time))
\’MsgBox offset_morning\’MsgBox offset_evening
If CDate(line1) <= CDate(morning_time) Then \’ 正常上班打卡早于8点 morning_tmp = 0 ElseIf (CDate(line1) > CDate(morning_time)) And (offset_morning < 120) Then \’ 上班迟到 morning_tmp = 2 Else \’ 上班迟到超过2小时 morning_tmp = 11 End If
\’分别给出 evening_tmp值:如果line2晚于于17:30则返回0，早于17:30且不超过2小时为早退取值3,超过2小时给值12，
If CDate(line2) >= CDate(evening_time) Then \’ 正常下班打卡 evening_tmp = 0 ElseIf (CDate(line2) < CDate(evening_time)) And (offset_evening < 120) Then \’ 早退 evening_tmp = 3 Else \’ 早退超过2小时 evening_tmp = 12 End If
\’最终cal= morning_tmp+evening_tmp
\’MsgBox morning_tmp + evening_tmpSelect Case (morning_tmp + evening_tmp)
Case 0cals = “全勤”Case 1 cals = “休息”Case 2 cals = “迟到”Case 3 cals = “早退”Case 4 cals = “无下班打卡”Case 5 cals = “迟到+早退”Case 6 cals = “上班迟到，下班没打卡”Case 7 cals = “无上班打卡”Case 8 cals = “10:00-15:30异常打卡”Case 10 cals = “早退,无上班打卡”Case 11 cals = “迟到超2小时”Case 12 cals = “早退超2小时”Case 23 cals = “迟到早退都超2小时”Case Else calse = “异常打卡” & CStr(morning_tmp + evening_tmp) & “次”
End Select
End Function