EXCEL 考勤记录筛选迟到以及加班时间

Question

如图所示，请问
1 如何令超过上班时间超过10:00的显示红框？并且计算迟到时间（以半小时为单位，不满半小时按半小时计算，例如10:18则显示为0.5H,11:11显示为1.5H
2如何筛选下班时间（超过18:30）框内颜色底为绿色？，并且计算加班时间（以半小时为单位，不满半小时则不算）
例如 22:08，则显示为4.H

Answer 1

　　一、制作方法：
　　假设某公司有甲、乙、丙、丁四个部门，以甲部门为例（因为其他部门的方法是一样的，只不过时间设置不同而已），其上下班时间安排是： A班：7：30-16：30 B班：12：00-21：00 C班：10：00-19：00 D班：8：30-17：30
　　考勤规则：上班时间后5分钟内打卡不算迟到，加班半小时以下不计加班。
　　首先，按名称整理好每个人的上下班的打卡时间（有电子打卡机的可以直接导入数据，手动打卡钟的就只能手动输入时间了）
　　第二步，在整理好的上下班时间工作表的第一行依次输入姓名（即A1格）、日期（即B1格）、排班（即C1格）、上班时间（后面的以此类推）、下班时间、考勤结果（上班）、考勤结果（下班），标准下班时间、加班时间、加班时间修正等行名
　　第三步，如果有几个部门，且每个部门的上下班时间不一致，则最好按部门将员工分类在同一个工作表的不同工作薄里， 第四步，设置单元格的格式
　　1、凡是用时间表示的，都用“hh:mm”的格式 2、记得在输入时间的时候关闭输入法
　　第五步，下面将进行具体的计算公式设置（以甲部门的A班为例，其他部门的不同班次，只是公式里的时间不同）
　　二、EXCEL的使用技巧：
　　1、右键单击窗口下面的工作表标签，如“Sheet1”.在弹出的菜单中选择“选定全部工作表”
　　2、如何快速删除单元格信息， 有一排数据，想快速删除这组数据，可以使用“填充删除法”。选中该列空白单元格，将鼠标移至该单元格右下方，鼠标变成黑色十字后，向上拖动填充。整组数列填充空白完成，等于删除了整组数列。
　　3、excel对每个单元格都有默认的命名方式，即字母+数字，如A7，表示第一列的第七排单元格。如果想按照我们自己的想法去重新命名一个单元格，如，将B7改为“我的单元格”，则首先点击该单元格，，选中左上角的名字，修改为“我的单元格”
　　4、想快速查看一列或者排的和，可以单击这拍最后一个有数字的单元格，网上拖动全部选择，excel会自动显示选择的这列的。
　　5、单元格内的数据有批注，格式，内容等等，但是只想要单纯的数字，不需要其他东西时，可以选中该单元格，点编辑——清除，可以选择清除格式，内容，批注，或者全部东西都删除。
　　6、选中不相邻的单元格，可以先按住ctrl，再进行点击选择。

Answer 2

可以用条件格式设置颜色，用公式计算迟到和加班时间。详见附件：

 

追问

谢谢 请问如果用公式计算迟到和加班时间

中迟到按照分钟算那是怎么修改呢？

追答

C2=IF((MOD(B2,1)TIME(10,,)),TEXT((MOD(B2,1)-TIME(10,,)),"[M]分钟"),"")
D2=IF(MOD(B2,1)>TIME(18,30,),TEXT(MOD(B2,1)-TIME(18,30,),"[M]分钟"),"")

本回答被提问者和网友采纳

Answer 3

=IF(HOUR(B2)<10,"",IF(HOUR(B2)>=16,IF(MINUTE(B2)>=30,"+"&HOUR(B2)-16+0.5,"+"&HOUR(B2)-16),IF(HOUR(B2)>=10,IF(MINUTE(B2)>=30,"-"&(HOUR(B2)-10)+1,"-"&(HOUR(B2)-10)+0.5),""))&"H")

Answer 4

3/10和3/15怎么处理？追问

3/10 未超过10:00则 不算。不显示为红框 下班时间因未打卡不算
3/15 超过10:00显示红框 计算超过10:00的时间 下班时间不算