Excel表格如何提取考勤中的迟到早退？

Question

我从考勤机上导出了员工考勤时间，现在想筛选出早上9点以后打卡算迟到，下午16:30之前打卡算早退，请教各位大神如何设置公式呀？

Answer 1

每个员工每天有2条考勤记录，分别是“上班”/“下班”打卡记录，所以用2个公式分别判断“迟到”/“早退”。

具体过程如下：

首先用函数HOUR（），MINTUE（），SECOND（）将考勤机记录中的时间部分（小时、分钟，秒）读取出来。

如E1（对应“上班”打卡）所示为A1时间部分。E1对应“上班”打卡时间，E2对应“下班”打卡时间。

公式（E1）：=HOUR(A1)+MINUTE(A1)/60+SECOND(A1)/3600

请点击输入图片

2.以E1单元格公式为基础加上判断，（C1）晚于9:00（>9）即为“迟到”，（C2）早于16:30（<14.5）即为“早退”，即得到“迟到”/“早退”结果。

“迟到”公式（C1）：=IF(HOUR(A1)+MINUTE(A1)/60+SECOND(A1)/3600>9,"迟到","正常")

“早退”公式（C2）：=IF(HOUR(A2)+MINUTE(A1)/60+SECOND(A2)/3600<16.5,"早退","正常")

3.下拉

说了很多，其实就是2个公式：

“迟到”公式（C1）：=IF(HOUR(A1)+MINUTE(A1)/60+SECOND(A1)/3600>9,"迟到","正常")

“早退”公式（C2）：=IF(HOUR(A2)+MINUTE(A1)/60+SECOND(A2)/3600<16.5,"早退","正常")

Answer 2

可以通过分列，将日期和时段分裂开，如图所示，之后通过公式COUNTIFS，计算B列大于9点小于12点的数量，同理早退可以通过计算大于12点，小于16：30的数量。

具体公式为=COUNTIFS(B:B,">=9:00:00",B:B,"<=12:00:00")

Answer 3

1、根据你上图时间所在列，随便写了几个日期。

2、根据你的数据的规律，每日有两个日期，需要加一个辅助列--D列。=DAY(C2)，下拉。

3、在E2单元格输入：=IF(AND(COUNTIF($D$2:D2,DAY(C2))=1,TIME(HOUR(C2),MINUTE(C2),SECOND(C2))-TIME(9,0,0)>0),"迟到",IF(AND(COUNTIF($D$2:D2,DAY(C2))=2,TIME(HOUR(C2),MINUTE(C2),SECOND(C2))-TIME(16,30,0)<0),"早退",""))

下拉就可以判断是否迟到和早退。

Answer 4

=IF(AND(HOUR(A3)>=9,HOUR(A3)<12),"上午迟到",IF(AND(HOUR(A3)>12,HOUR(A3)<=16,MINUTE(A3)<=30),"下午早退","正常"))

只要以上一条公式就行了，没那么复杂，Excel不能直接判断时间区间，必需要用hour和minute函数把时和分取出来，好对比上下午区间和分钟数，再用if函数和and函断配合使用就行了，如果是vb编程的话就更简单。

Answer 5

你这里还有几个问题:
应该有一列数据是班制,是早班还是中班还是晚班;迟到和早专退的时间很属容易混淆,要界定清楚;如果时间是标准格式,可以用=TEXT(C2,"hh:mm:ss")提取时间,提取的时间可以和8:30比较,得出迟到的结论;