【254】--根据考勤记录，计算工作时长

如何根据考勤记录（含重复打卡）计算工作时长。

【考勤数据】

（红色标记为重复打卡记录）

-------------------------------------

想要计算工作时长，就需要有上/下班时间；想要有上/下班时间，就需要先排除重复的打卡记录。但是，使用以往的“数据去重”方法（详见推送21）是无法删除重复的打卡记录的，因为重复打卡记录是由于过度打卡造成的，记录时间本身是不重复的，所以“数据去重”无法排除。那此问题要怎么解决呢？呵呵，别急，万能的表妹已经为你想好了办法，下面开始讲解。

~~~~~~工作时长的分割线~~~~~~

解决方法

步骤1

记录数据分列

【操作】选中打卡记录数据--数据--数据工具--分列--固定宽度--分成两列--列数据格式:日期/常规--确定--调整日期格式

-------------------------------------

步骤2

区分打卡类型

【公式】=IF(C2<=TIME(12,0,0),'上班打卡','下班打卡')

-------------------------------------

步骤3

标记重复打卡记录

【公式】=COUNTIFS(A$2:A2,A2,B$2:B2,B2,D$2:D2,D2)

【说明】次数等于1表示正常打卡，次数大于1表示重复打卡

-------------------------------------

步骤4

计算工作时长

工作时长的计算公式如下：

工作时长=下班时间1-上班时间1+下班时间2-上班时间2+下班时间3-上班时间3+...

我们将公式进行一下变形后，得到如下公式：

工作时长=（下班时间1+下班时间2+下班时间3+...）-（上班时间1+上班时间2+上班时间3+...）

根据变形后的计算公式，我们使用SUMIFS函数进行计算：

工作时长=SUMIFS（下班时间）-SUMIFS（上班时间）

带入参数后，公式如下：

工作时长=SUMIFS（姓名，下班打卡，打卡次数1）-SUMIFS（姓名，上班打卡，打卡次数1）

再使用TEXT函数调整时长显示样式，最终公式如下：

【公式】=TEXT(SUMIFS($C$2:$C$38,$A$2:$A$38,G2,$D$2:$D$38,'下班打卡',$E$2:$E$38,'=1')-SUMIFS($C$2:$C$38,$A$2:$A$38,G2,$D$2:$D$38,'上班打卡',$E$2:$E$38,'=1'),'[H]:MM')

-------------------------------------

以上就是“根据考勤记录计算工作时长”的方法了，如果你不喜欢“[H]:MM”的时长样式，也可以使用推送241中介绍的其他样式进行替换，感兴趣的同学赶快自己动手试试吧！信表妹，EXCEL得永生哟~~

知 识 点

【21】--消灭数据的“影分身”：删除重复项
【241】--想要“计算时间”，你得看这篇！