真伤脑，这份考勤表整理了2个小时，头痛！

考勤表绝对是Excel中最伤脑的问题，每次帮学员处理，头都痛。

某公司考勤记录，现在要根据上下班时间，计算出迟到分钟、扣款金额、早退、上午工时、下午工时、实际工时。

上班时间为7:30-11:30和13:30-17:30，超过4小时按4小时计算，4小时以内按实际工时计算。

1.迟到分钟

=(MAX(0,C2-TIME(7,30,0))+MAX(0,E2-TIME(13,30,0)))*24*60

时间用TIME函数表示是标准用法，用打卡时间跟上班时间比较，如果大于0就显示本身，否则就显示0。用MAX(0,公式)思路就跟个税一样。

直接运算得到的是天数，天数*24*60就转换成分钟。

这个后面会解释。

2.扣款金额

每个月迟到时间，每30分钟扣款10元，30分钟以内不扣款。

=IF(A2<>A1,INT(SUMIF(A:A,A2,G:G)/30)*10,'')

先用IF函数判断上下两个单元格是否一样，如果不一样，证明是首次出现的，就计算扣款金额，否则显示空白。

用SUMIF函数就是计算每个人当月的迟到时间，除以30，再用INT获取整数，就知道总共迟到多少个30分钟，再乘以10元，就是总扣款金额。

3.早退

=IF(OR(AND(D2<>'',D2<TIME(11,30,0)),AND(F2<>'',F2<TIME(17,30,0))),'早退','')

上午提前下班或者下午提前下班，同时单元格不能为空，满足了就是早退。

4.上午工时

=IF(COUNTA(C2:D2)=0,'',IF(COUNTA(C2:D2)=1,'未打卡',ROUND((MIN(TIME(11,30,0),D2)-MAX(C2,TIME(7,30,0)))*24,2)))

01 不上班，也就是没有打卡时间，这个就要加一个IF函数判断，让没有打卡时间的显示空白。只打卡一次，也就是未打卡。都是用COUNTA统计非空单元格的个数而判断。

02 MIN(TIME(11,30,0),E2)在计算工时的时候，下班超过11:30的都按11:30计算，11:30之前的按实际时间计算，所以用MIN获取两个时间的最小值。当然这里也可以用IF函数进行判断。

=IF(E2>TIME(11,30,0),TIME(11,30,0),E2)

03 MAX(D2,TIME(7,30,0))在计算工时的时候，上班超过7:30按实际时间计算，7:30之前的按7:30计算。上班打卡跟下班打卡刚好相反，所以用MAX函数获取最大值。当然也可以用IF函数进行判断。

=IF(D2<TIME(7,30,0),TIME(7,30,0),D2)

04 两个时间相减是获得时间

时间要转换成小时，乘以24，在转换的过程中会有很多小时点，所以嵌套ROUND函数保留2位小数点。

5.下午工时

=IF(COUNTA(E2:F2)=0,'',IF(COUNTA(E2:F2)=1,'未打卡',ROUND((MIN(TIME(17,30,0),F2)-MAX(E2,TIME(13,30,0)))*24,2)))

计算方法跟上午工时一样，就不做说明。

6.实际工时

=SUM(J2:K2)

源文件：

https://pan.baidu.com/s/1eG3MRJ802Pj5j9Zv0xksaQ