Excel 打卡时间计算

计算时间差时，可以直接用两个时间相减的方法，不过必须是时间大者减去时间小者。所谓时间大者指的是靠后的时间，也就是说“8:45”比“8:30”大。因此第一个公式大于“8:30”的都算迟到，第二个公式大于或等于“17:30”算正常。

公式：“=TEXT(($S$2-C2)*1440,";0分钟;")”或“=TEXT((D2-$T$2)*1440,";0分钟;")”

这里的$S$2单元格为“8:30”，$T$2单元格为“17:30”，之所以用单元格引用，而不直接用时间，是因为这样便于修改。如果老板说上班15分钟之内都算迟到，这样我们就可以直接将$S$2单元格修改成“8:45”，而其他单元格都不需要修改。

您好，钉钉官方应用中还没有考勤确认单；建议企业管理员在钉钉应用中心搜索第三方应用【考勤确认】并开通使用，该应用支持考勤确认单功能。路径：【手机端钉钉/电脑端钉钉】-【工作台】-右上角【应用中心】-【搜 ...

考勤打卡如何计算工时（新版考勤）

您好，考勤打卡的工时是按照组织成员上下班打卡的时间计算，两次打卡区间的时间就是工时。若只有一次打卡，且没有提交补卡/请假等审批，工作时长为0；若打卡一次，但另一个卡点使用了请假审批，当前会统计扣除请假 ...

开启弹性考勤后迟到怎么计算

您好，弹性考勤打开后，严重迟到和旷工迟到设置仍会生效，但会根据弹性设置从弹性后的最晚上班时间开始计算。例如，如果您的正常上班时间是9点，弹性后的最晚打卡时间是10点，严重迟到时间设置为30分钟，那么1 ...

弹性考勤下如何计算加班时长

您好，弹性打卡中不同的设置方式对加班的影响也不同，以【OA后台】中的【班次设置】为例分别说明如下：1、管理员勾选【允许晚到晚走、早到早退】后，应下班时间会根据上班打卡时间动态变化。如果您提前上班了1小 ...

考勤地点名称

您好，考勤地址支持自定义设置名称，管理员操作路径如下：1、【电脑钉钉】-【工作台】-【考勤打卡】-【考勤组管理】-【新增考勤组】/选择考勤组【编辑】-【打卡方式】-地点【添加】选择地点后下一步即可自定 ...

網友問到一個關於用 Excel 來根據打卡清單計算工時的問題，該如何處理？

參考下圖，其條件如下：

8:00~8:14→8:00；8:15~8:30→8:30；8:30~8:44→8:30；8:45~9:00→9:00

【公式設計與解析】

儲存格D2：=MROUND(C2,30/(24*60))-MROUND(B2,30/(24*60))

複製儲存格D2，貼至儲存格D2:D17。

MROUND(C2,30/(24*60))：將上班打卡時間捨去／進位至0／30。

MROUND(B2,30/(24*60))：將下班打卡時間捨去／進位至0／30。

因為在 Excel 中，一天(24小時)是以『1』表示，所以1小時=1/24。

關於 MROUND 函數也可以參考另一篇文章：

http://isvincent.pixnet.net/blog/post/46394263

全站熱搜

創作者介紹

vincent

學不完．教不停．用不盡

无论是迟到还是早退，其实它的衡量标准很简单，就是“打卡时间”，如果打卡时间晚于上班时间就是“迟到”，早于下班时间就是“早退”。在EXCEL中，我们用函数或者VBA按这条标准进行判断，可以智能地、快速地依据考勤机数据得到考勤统计表。

笔者E图表述接触过多种考勤机数据，发现考勤机导出的数据依据打卡时间的记录方式主要分为三类：打卡时间独立型、日期和打卡时间混合型、同一单元格多个打卡时间型。我们分别来说明一下。

说明：以下数据统一按照上班时间8:00，下班时间17:00来计算迟到、早退情况。

一、打卡时间独立型

特点：每一行就是一次打卡记录，而且打卡日期和打卡时间是分开的两列字段。

这种考勤应该是最常见，也是处理最简单的一类数据源。

笔者解法：

首先增加上午/下午辅助列，并用公式自动判定填写上午或下午。

E2单元格输入函数：=IF(D2<=VALUE("12:00:00"),"上午","下午")

函数解析：VALUE("12:00:00")将12:00:00转化成时间格式，然后与D列的时间进行对比，得到记录条中打卡时间是“上午”还是“下午”，向下填充函数到E2:E12单元格区域。

然后增加迟到/早退辅助列，并用公式自动判定填写。

F2单元格输入函数：=IF(E2="上午",IF(D2>VALUE("8:00:00"),"迟到","正常"),IF(D2

早退","正常"))

函数解析：通过IF函数，判断打卡是迟到、早退，还是正常。如果打卡记录是“上午”，那么判断是否大于8:00:00，是就是“迟到”，不是就是“正常”；“下午”，则判断是否小于17:00:00，是就是“早退”，不是就是“正常”。

最后这样处理源数据后，我们就可以使用“索引函数”将这些内容匹配到《考勤统计表》中，如下所示：

在K2单元格输入函数：=LOOKUP(1,0/(($B$2:$B$12=$I2)*($C$2:$C$12=K$1)*($E$2:$E$12=$J2)),($F$2:$F$12))

函数解析：多条件索引的方式有很多、很多，笔者在这里采用的是LOOKUP。LOOKUP(1,0/…)是一个很常用的多条件索引方法，之前的教程中也有涉及，这里就不再赘述。给大家一个公式以后可以套用。

=LOOKUP（1，0/（（条件1）*（条件2）*（条件3）*…*（条件n）），（被查询目标列））

通过上图我们可以看到很多错误码#N/A，那些就需要其他的判断了。例如L2单元格应该是“上午漏打卡”，O2和P2单元格则是“公休”。这不是本文的重点，也不算难点，无外乎用IF函数判断条件再返回需要的说明即可，我们就不介绍了。学习更多技巧，请收藏关注部落窝教育excel图文教程。

二、日期和打卡时间混合型

特点：日期和时间在一个单元格中，导出的数据基本都是文本格式。

日期有两种常见格式：一种带分隔符，如2020/09/01；一种不带分隔符，如20200901。

日期都是双位数显示，如1月——01，12月——12；1日——01，8日——08。

处理这样的内容，完全可以先将打卡记录处理成常规的【独立时间型】之后，然后再来处理考勤。以“打卡记录2”为例，如下：

首先获得日期列。

在E2单元格输入函数：=--LEFT(D2,10)

函数解析：用文本函数Left直接提取日期部分，再用两个符号【--】转换为数值，最后调整单元格格式为日期就可以了。

然后获得时间列。

在F2单元格输入函数：=--RIGHT(D2,LEN(D2)-11)

函数解析：用文本函数Right提取文本右侧的时间。LEN(D2)得到文本的总长度，11是日期长度10加一个空格长度，差值就是时间部分的长度。

G列、H列的处理同上例，索引到《考勤统计表》中也和上例一样，是不是融会贯通了？

三、同一单元格多个打卡时间型

特点：同一人当天的多次打卡时间都在一个单元格中。

对于考勤统计来说，这种数据是最难处理的。而且我们上图是模拟数据，实际情况中，一个单元格中可能不止一两条记录。

处理这样的数据，笔者的总体思路还是先将其转化成第一种数据类型那样的表，然后再来统计。

这里用函数处理，难度比较大，运行效率也比较低（数据量大了后），所以推荐给大家一个VBA的做法吧。（不要谈VBA就色变，VBA很实用而且不难学，有兴趣的同学可以通过我们的QQ群找到辅导老师。）

简单的几行代码就把数据A2:D7处理到F2:I13单元格区域了，那么接下的操作就回到了我们第一例中了。代码如下：

说明：每行代码蓝色引号'''后的灰色部分是笔者对代码的注解，方便大家了解代码的含义。蓝色引号在代码中表示注释，其后内容不参与运行。

Sub 数据清理()

Dim arr, brr '''定义两个变量，作为数组使用

With Sheets("多个时间型") '''指定被操作的工作表《多个时间型》

a = .[A1].End(4).Row '''找到数据末行行号，赋值给a

arr = .Range("A2:D" & a) '''将数据区域赋值到数组arr中

ReDim brr(1 To 500, 1 To 4) '''重新定义brr为二维数组，数组大小为500行，4列

For i = 1 To UBound(arr) '''循环数组arr

s = Split(arr(i, 4), Chr(10)) '''拆分arr数组的第4列，打卡时间列，并赋值给一维数组s

For j = 0 To UBound(s) '''循环一维数组s

k = k + 1 '''计数器效果，得到每次操作brr数组的行号

brr(k, 1) = arr(i, 1) '''将数组arr的第1列的值，赋值给数组brr的第1列

brr(k, 2) = arr(i, 2) '''将数组arr的第2列的值，赋值给数组brr的第2列

brr(k, 3) = arr(i, 3) '''将数组arr的第3列的值，赋值给数组brr的第3列

brr(k, 4) = s(j) '''将一维数组s的值逐个赋值到数组brr的第4列

Next j '''结束循环变量j

Next i '''结束循环变量i

.[F2].Resize(UBound(brr), UBound(brr, 2)) = brr '''将数组brr的值赋值到单元格区域

End With '''结束with语句

Erase arr '''清空数组arr

Erase brr '''清空数组brr

Erase s '''清空数组s

End Sub

考勤表是日常EXCEL工作中比较典型的套表系统之一，对于这类数据的统计，我们可以讲的还有很多。例如【每天四次打卡】的处理，例如【婚丧病事】请假的工资核算，例如【个税抵扣】。如果这些都会了，同学们就可以给自己的公司制作一套DIY的考勤及工资核算模板，这样既可以提高工作的效率，还可以在领导心中树立一个好的形象。