EXCEL自定义考勤函数
发布日期:2021-10-16 07:11:59 浏览次数:1 分类:技术文章


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


上一篇:临时语法VBA
下一篇:EXCEL密码VBA解决

关于作者

    白红宇是个全栈工程师,前端vue,小程序,app开发到后端框架设计,数据库设计,环境部署上线运维。

最新文章

java配置logback_logback配置和使用 2021-06-24
java 缓冲流 刷新_java 缓冲流 Buffer 2021-06-24
java怎么改变数据类型_java基础系列—数据类型 2021-06-24
Java将数组随机分成几组_使用Arraylist将数组中元素随机均等乱序分为N个子数组... 2021-06-24
牛客网java输出0%_牛客网笔试输入的几种情况总结(java) 2021-06-24
Java堆异常自动修复需要多久_Java中关于异常处理的10个最佳实践 2021-06-24
c# mysql executescalar_C# - MySQL数据库编程 简明教程 2021-06-24
java基本变量_Java基本语法-变量 2021-06-24
html5 java 交互_App与HTML的交互 2021-06-24
python中组合数据类型、函数和代码复用的难点_完成第5章 函数和代码复用 程序练习题P151-152... 2021-06-24
java hashbasedtable_Guava类库学习--Table(双键的Map) 2021-06-24
java runnable main_【BUG】”main” prio=5 tid=1 RUNNABLE 2021-06-24
java中的protect_JAVA中protected的作用 2021-06-24
java rsa .net_.net rsa 加密解密 与 java 互通 2021-06-24
java程序设计经典_Java程序设计经典300例 2021-06-24
navicat怎么重启mysql数据库_Navicat还原数据库出现MySQL server has gone away的解决方法... 2021-06-24
mysql主从启动注意事项_mysql主从的注意事项 2021-06-24
ubuntu命令安装中文语言包_Ubuntu 18.04 LTS 命令行方式安装中文语言包 2021-06-24
python课程设计汽车销售管理系统_Python课程设计:汽车销售管理系统 2021-06-24
python考题在当前目录下有一个文件名为temp.xls_xls文件格式考题系统 2021-06-24