有了这个分列后的日期和时间,就可以进一步处理了。公司的出勤是早晚8:30-17:30,这样就可以通过辅助列来解决早签到时间和晚签退时间的提取问题,如下表:
在单元格K2输入公式:=IF(AND(MIN(F2:J2)<14/24,MAX(F2:J2)<>""),MIN(F2:J2),""),计算早签到时间
在单元格L2输入公式:=IF(AND(MAX(F2:J2)>14/24,MIN(F2:J2)<>0),MAX(F2:J2),""),计算晚签退时间
这样,我们就可以判断某个人是否迟到和早退了,如下图:
迟到有两种情况:上班没有打卡;打卡时间迟于8:30,因此单元格 M2 公式为:=IF(OR(K2="",K2>8.5/24),1,"")
早退有两种情况:下班没有打卡;打卡时间早于17:30,因此单元格N2 公式为:=IF(OR(L2="",L2<17.5/24),1,"")
最后,利用透视表进行汇总,就可以计算出每个人的迟到次数和早退次数。假如公司规定迟到一次扣50元,早退一次扣80元,那么汇总结果如下所示: