今日目标
学会考勤中的日期、时间计算
考勤表最难做的，就是日期和时间的计算，知道为什么吗？
1- 因为，Excel中的日期和时间，是一个逻辑问题。
2- 因为，女生的逻辑能力，天生就比较差。
3- 因为，做考勤表的HR，大部分都是女生。
所以考勤表做起来非常的难。
特别是日期和时间。
1- 问题描述
小风的女朋友的同事的前女友，拖小风向我提了一个问题。
这是一个考勤晚班时间的函数计算问题
背景
系统导出的一份表格，包含所有部门、员工的考勤记录。
由于指纹打卡机兼作入口门禁，因此每天会有多次打卡记录。
部分员工有晚班(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
