小学生日记-excel：考勤表加班统计

考题：现有一张考勤表如下，请根据已有上下班时间对所有人的加班记录进行整理，设计合理的公式（需要考虑一些异常状态），要求合理的计算出每个人的加班时长是多少？

已知条件：
1.我们每天9:30上班，弹性5分钟
2.我们每天18:30下班，不弹性了这个
3.每天18:30以后开始算加班时长
4.加班时长最小单位：0.5H

问题分析：
1.当星期列参数为六，日，节假日时，加班时长=下班（out）-上班（in）
2.当星期列参数为一-五时，加班时长=下班（out）-下班标准时间（18:30）
3.当没有上班，下班打卡记录时，加班时长需要显示为00:00
4.当实际下班时间超过24:00后，加班时长计算公式存在问题，需要调整改进
5.本月加班时长以“是否加班”列中的关键字“Y”作为标志位。

解题思路：
1.判断下班时间是否为空需要使用 isblank函数
2.下班时间不为空时，需要判断上班时间是否为空，依旧使用isblank
3.当下班时间或者上班时间任意时间为空时，加班时长设置默认值00:00
4.当上下班时间不为空时，需要判断当前是否是周六日或者是节假日，如果是，使用IF(out<in,1+out-in,out-in)
5.当上下班时间不为空，且当前时间为正常工作日时，使用公式IF(out<18:30,1+out-18:30,out-18:30)
6.1+out-in或者1+out-18:30 代表意思是当下班时间超过24:00时，需要使用该公式。
7.判断当前日期是否是周六日需要使用公式：OR(B1013="六",B1013="日",B1013="节假日")

最终答案：
说明：OUT1代表下班时间，IN1代表上班时间，ST1代表标准下班时间，WE1代表星期几，以上信息均需要再实际excel表中替换成为实际的单元格

=IF(ISBLANK(OUT1),0,IF(ISBLANK(IN1),0,IF(OR(WE1="六",WE1="日",WE1="节假日"),
IF(OUT1<IN1,1+OUT1-IN1,OUT1-IN1),IF(OUT<IN1,1+OUT1-$WE$1,OUT1-$WE$1))))

实际公式看起来比较复杂 ，实际理解时 ，你会发现这其实就是三层IF语句嵌套。一起来看下：
第一层：判断下班时间是否为空，如果为空，加班时间置为0，不为空时进入第二层判断
第二层：判断上班时间是否为空，如果为空，加班时间置为0，不为空时，进入第三层判断
第三层：判断当前日期是否是星期六，日，节假日，如果是，进入下一层计算节假日加班时间，如果不是，进入下一层计算工作日加班时间

后续优化：
1.第一层和第二层的单元格是否为空可以使用OR公式合并处理OR(ISBLANK(OUT1),ISBLANK(IN1))