EXCEL自定义考勤函数
来源:互联网 发布:康佳液晶电视网络连接 编辑:程序博客网 时间:2024/05/24 22:43
Function kq(tcs, tc, tls, tl, zk)Rem tcs:stannard of tc,tc:time of come;tl:time of leaveDim tce, tle 'tce:有效签到时间Dim tn1s, tn2stw = tls - tcs '时间不可为负,否则显示####tn1s = 0.5 'the time of leaving morning,set12:00,上午下班时间If tw >= 8 / 24 Thentn2s = tls - (8 / 24 - (tn1s - tcs)) 'the time ofcome afternoon,下午上班时间Else: tn2s = tn1sEnd If'kq = tn2stwa = tn1s - tcs ' the time of AM,上午标准工作时长twp = tls - tn2s ' the time of PM,下午标准工作时长Dim dc As Integer '迟到或早退允许时间dc = 15Rem-----------判断签到时间----------------Dim da1 As IntegerIf Not tc Like "--" Then On Error GoTo come1 da1 = DateDiff("n", tcs, tc)'上午签到时差,时间相差的单位为:分钟数 If da1< -60 Or tc >= tls And Not tc Like "--" Then tce = "签到无效": zt_c = "无效" ' Rem 早到1小时以上无效 ElseIf da1 >= -60 And da1 <= dc Then tce = tcs: zt_c ="正常" ElseIf da1 > dc And tc < tls Then tce = tc: zt_c = "迟到" Else: tce = "come?"come1: tce = "未签到1": zt_c = "未签" End IfEnd IfIf tc Like "--" Then tce = "未签到": zt_c = "未签"'kq = zt_cRem ----------判断签离时间----------------Dim dp1 As Integer If Not tl Like "--" Then On Error GoTo leave1 dp1 = DateDiff("n", tls,tl) '下午签离时差,时间相差的单位为:分钟数 If dp1 >60 Or tl <= tcs Then tle = "签离无效": zt_l = "无效" ElseIf dp1 <= 60 And dp1 >= -dc Then tle = tls: zt_l ="正常" ElseIf dp1 < -dc And tl > tcs And tl <> "--" Then tle =tl: zt_l = "早退" Else: tle = "leave?"leave1: tle = "未签离": zt_l = "未签" End If End IfIf tl Like "--" Then tle = "未签离": zt_l = "未签"'kq = tle Rem -----------计算出勤状态---------- Dim xzk '--------------------- If zk Like "外勤" Then If tce > 1 And tle > 1 Then xzk = "无效" Else: xzk = "外勤" End If End If '---------------------------- If zk = "请假" Then If (tce > 1 Or tle > 1) Then xzk = "请假" Else: xzk = "疑假" End If End If '------------------------------------------ If (Not zk Like "外勤" And Notzk Like "请假") And zk > 1 Then If zt_c = zt_l Then xzk = zt_c If zt_c <> zt_l Then xzk = "来:" & zt_c & ";离:" &zt_l End If ' kq = xzk Rem-------------------计算出勤时长---------- Dim we If tce <1 And tle < 1 Then If tle <= tn1s Then we = tle - tce If tce <= tn1s And tle <= tn2s And tle >= tn1s Then we =tn1s - tce If tce <= tn1s And tle >= tn2s Then we = (tn1s - tce) + (tle- tn2s) If tce >= tn1s Then we = tle - tn2s Else: we = "--" End If 'kq =we 'If tce <1 Or tle < 1 Then tce = CDate(tce): tle = CDate(tle) Dim myarrmyarr = Array(tce, zt_c, tle, zt_l, we, xzk)kq = myarrEnd Function
0 0
- EXCEL自定义考勤函数
- 考勤加列及引用自定义函数
- Excel自定义函数
- Excel操作自定义函数
- EXCEL自定义函数
- Excel自定义函数VBA
- Excel自定义函数
- Excel 自定义函数
- 自定义考勤统计日历(一)
- 自定义考勤统计日历(二)
- 浅谈Excel开发:四 Excel 自定义函数
- 考勤
- 考勤
- python 操作excel用来做考勤报表。
- excel进行考勤数据的分析
- VC读写Excel MFC读写Excel 考勤数据
- Excel 中自定义函数的限制
- excel中个人所得税之自定义函数
- 帮你理解“日期值”和“时间值”
- 来自 W3School 的 HTML 快速参考。可以打印它,以备日常使用。
- VBA学习记忆点小记:www.51zxw.net
- mongoDB & Nodejs 访问mongoDB (一)
- EXCEL密码VBA解决
- EXCEL自定义考勤函数
- 临时语法VBA
- VB封装Excel_VBA代码为Dll
- 按钮事件
- C#题目总结
- 工作薄、工作表所有事件一览表
- 搬圆桌
- 考勤加列及引用自定义函数
- Protocol Buffer技术详解(语言规范)