今日目标
学会考勤中的日期、时间计算
考勤表最难做的，就是日期和时间的计算，知道为什么吗？
1- 因为，Excel中的日期和时间，是一个逻辑问题。
2- 因为，女生的逻辑能力，天生就比较差。
3- 因为，做考勤表的HR，大部分都是女生。
所以考勤表做起来非常的难。
特别是日期和时间。
小风的女朋友的同事的前女友，拖小风向我提了一个问题。
这是一个考勤晚班时间的函数计算问题
背景
系统导出的一份表格，包含所有部门、员工的考勤记录。
由于指纹打卡机兼作入口门禁，因此每天会有多次打卡记录。
部分员工有晚班（16:00-00:00），此类员工下班打卡时间需要晚于00:00，否则算早退。
如图，现在要统计出每天考勤最早、最晚的一笔记录，来检查员工是否按时间上下班。
问题
作为秋叶Excel的学员，小风没有给我丢脸，他用函数公式，实现了「最早、最晚」记录的统计。
在每天的第1行中，计算出了最早、最晚的记录。
紫色背景处为公式自动计算。
但是晚班的时间计算出了问题。
例如，
6/3号：
最晚时间应该是6/4 00:12，但实际计算出来的是6/3 22:57。
6/4号：
最早时间应该是6/4 16:02，但实际计算出来的是6/4 00:12。
最晚时间应该是6/5 00:22，但实际计算出来的是6/4 23:30。
像上面，才是正确的时间计算结果。
如果要这样，那么是要IF函数吗？怎么写呢？或者有其他的思路吗？
2- 原因分析what 这是什么问题
这是一个时间的分组统计问题，因为班次节点和自然时间不一致，给统计增加了难度。
类似的时间问题还有：
1- 根据时间判断白晚班
2- 计算每天熬夜最晚时间
3- 计算两个时间差等等
why 为什么搞不定
平常的数字计算，「加」就是数字增长，「减」就是数字减少，大小也容易对比。
时间比较难计算主要原因是：
1- 循环度量单位
时间是一个循环的度量单位，24:00 之后不是25:00，而是01:00
2- 不是简单的大小对比
时间的早晚，不是简单的大小对比。
同日对比，18:00 比 22:00早，而是18< 22
夸日对比，22:00 比 02:00早，但是22 > 2
这两个原因，导致时间统计非常困难。
how 解决方法
解决时间问题的方法，就是反其道而行之。
1- 把循环度量，变成单向大小对比
2- 结合日期，让02:00 > 22:00
针对这个案例，我们来看一下具体的操作。
3- 解决方法大致步骤是这样的：
1- 拆分日期和时间
2- 计算班次日期
3- 日期分组
4- 统计最早最晚时间
1- 拆分日期和时间
使用TEXT、INT等函数，把日期和时间拆分开来，否则无法对时间进行单独的判断。
公式如下：
日期提取
=TEXT(C2,"mm-dd")
时间提取
=C2-INT(C2)
2- 计算班次日期
这一点很重要，先看图示。
1- 原始日期
原始的日期，拆分出来之后是6/4号。
但是那一天小明上的是晚班，00:12下的班，应该是6/3号的班次，00:12才是最后一笔记录。
2- 班次日期
根据刷卡时间点，如果是在早上6:00之前刷的卡，就判定为前一天的班次，日期减1，完成日期的纠正。
这样00:12就是最晚的记录了，而不是22:57。
班次日期的公式如下：
=INT(C2) VLOOKUP(E2,M:N,2,1)
这里用了一下VLOOKUP的模糊查找，根据E2单元格的刷卡时间，就判断，日期是否需要-1。
3- 模糊查找
为此，我添加了一个模糊查找的列表。
时间在06:00之前，返回-1，和原始日期相加，日期就减了一天。
时间在06:00之后，返回0，和原始日期保持一致。
3- 考勤班次分组
班次日期计算完成之后，基于「班次日期」做好班次分组。
每次循环，代表1天，对应的公式如下：
=COUNTIFS($B$1:B2,B2,$F$1:F2,F2)
这不是本节的重点，就不再讲解了。
4- 统计最早最晚时间
使用IF、COUNTIF、INDEX函数，完成最早、最晚时间的查询。
对应的公式如下：
最早时间
=IF(G2=1,E2,"")
最晚时间
=IF(H2<>"",INDEX(E2:E3314,COUNTIFS(B:B,B2,F:F,F2)),"")
这不是本节的重点，就不展开讲解了。
4- 总结本节重点，是跟大家分享了，时间计算的一些技巧：
1- 日期时间，本质上也是数字，可以用INT函数拆分
2- 通过对日期增减，让02:00 > 22:00
如果今天的知识点都学会了，就点击右下角的「好看」，签个到吧。
5- 聊5毛钱同事老A，让我帮他优化一下表格，问我：
拉登你看看，这个表格怎么改改，统计更简单？
我说：
怎么改，你不需要知道，你只要知道，我改完之后，你只需要点击一下按钮，就可以搞定一上午的工作。
老A的回答，让我大跌眼镜，平时都德高望重的印象，一下子破碎了，他说：
拉登出品
《和秋叶一起学Excel》
视频讲解Excel
扫码领20元优惠券，到手价149
截止到明天(7月31 24:00)
还有更多福利，买课就送
更多暑期优惠，点击「阅读原文」了解
老A说：
先不用了，我一上午的工作，你一个按钮就搞定了，那我还干啥去啊？我饭碗不就丢了嘛！
今日话题：如果是你，你会担心效率太高，没活干丢饭碗吗？
我是拉小登，一个会设计表格的Excel老师
= = 推荐文章 = =
项目：按照次数追加行，谁知道可以私聊我
项目：妈呀!行程表错了,一个人同时安排了两个地方
财务：一个合同追款4次，再追下去就到Z列了
销售：产品信息分列，可愁死我了
财务：多工作表财务数据，快速核对
HR：能不能不要改表头，说了800遍了
销售：一长串运单遍号，如何拆分成一列
,
