![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/37d3d539b6003af375b3141d252ac65c1038b646?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/37d12f2eb9389b5038f6282f9535e5dde7116e46?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/95eef01f3a292df56aca6468ac315c6034a87346?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
有一个思路供题主参考:
新建一张sheet为迟到,建另一张为早退。
迟到sheet的B2=LEFT(Sheet1!A2,FIND(CHAR(10),Sheet1!A2)),自动提取原始数据中A2单元格第一次打卡的时间。
早退sheet的B2=RIGHT(Sheet1!A2,LEN(Sheet1!A2)-(-LOOKUP(,-FIND(CHAR(10),Sheet1!A2,ROW(Sheet1!A:A))))),自动提取原始数据中A2单元格最后一次打卡的时间。
两个公式下拉填充,提出所有时间。剩下的就简单了,用条件格式自动判断晚于上班时间的值和早于下班的值。完成。