嗨,我是爱踢汪。
很多人都知道Excel函数公式威力强大,
但遇到问题时却写不出公式,
想找也找不到。
本系列将给大家带来15个
很多人都在找的Excel公式,
已备大家不时之需。
今天是系列教程的上篇。
01、日期和时间数据的合并
下图为某单位员工刷卡考勤的部分记录,需要根据B列的刷卡日期和C列的刷卡时间,得到日期和时间合并后的数据。
在D2单元格中输入以下公式,并向下复制到D10单元格,即可得到日期和时间合并后的数据。
=B2+C2
![9ea4adee95d2b8d55e73c10d982d47b3.png](https://img-blog.csdnimg.cn/img_convert/9ea4adee95d2b8d55e73c10d982d47b3.png)
02、计算故障处理时长
下图为某运营商宽带故障报修记录表的一部分,需要根据C列的接单时间和E列的处理时间,计算故障处理时长。
![3ab44956fa90307bfc94040c531d01f3.png](https://img-blog.csdnimg.cn/img_convert/3ab44956fa90307bfc94040c531d01f3.png)
在F2单元格中输入以下公式,并向下复制到F6单元格。
=INT((E2-C2)*1440)
一天有1440分钟,要计算两个时间间隔的分钟数,只要用终止时间减去开始时间,再乘上1440即可。最后用INT函数舍去计算结果中不足一分钟的部分,计算出时长的分钟数。
如果需要计算两个时间间隔的秒数,可使用以下公式。
=(E2-C2)*86400
一天有86400秒,所以计算秒数时使用结束时间减去开始时间,再乘上86400。除此之外,使用TEXT函数能够以文本格式的数字返回两个时间的间隔。
以下公式返回取整的间隔小时数。
=TEXT(E2-C2,"[h]")
以下公式返回取整的间隔分钟数。
=TEXT(E2-C2,"[m]")
以下公式返回取整的间隔秒数。
=TEXT(E2-C2,"[s]")
03、计算员工在岗时长
下图所示为某企业员工加班考勤的部分记录,需要根据C列的上班打卡时间和D列的下班打卡时间,计算员工的加班工作时长。
![cc4b8f5321c90b7913932f9184827c00.png](https://img-blog.csdnimg.cn/img_convert/cc4b8f5321c90b7913932f9184827c00.png)
如果在E2单元格中使用公式“=D2-C2”计算时间差,由于部分员工的离岗时间为次日凌晨,仅从时间来判断,离岗时间小于到岗时间,两者相减得出负数,计算结果会出现错误。通常情况下,员工在岗的时长不会超过24小时。如果下班打卡时间大于上班打卡时间,说明两个时间是在同一天,否则说明下班时间为次日。
在E2单元格中输入以下公式,并向下复制到E10单元格。
=IF(D2>C2,D2-C2,D2+1-C2)
IF函数判断D2单元格的下班打卡时间是否大于C2单元格的上班打卡时间,如果条件成立,则使用下班时间直接减去上班时间。否则用下班时间加1后得到次日的时间,再减去上班时间。
公式也可以简化为:
=IF(D2>C2,D2,D2+1)-C2
还可以借助MOD函数进行求余计算。
=MOD(D2-C2,1)
用D2单元格的下班时间减去C2单元格的上班时间后,再用MOD函数计算该结果除以1的余数,返回的结果就是忽略天数的时间差。
04、计算员工技能考核平均用时
下图所示为某企业员工技能考核表的部分数据,B列是以文本形式记录的员工操作用时,需要计算员工的平均操作时长。
![70a352ebf7fa9cd16f48192152b839f9.png](https://img-blog.csdnimg.cn/img_convert/70a352ebf7fa9cd16f48192152b839f9.png)
将D2单元格格式设置为“时间”,然后输入以下数组公式,按组合键,计算结果为“0:01:12”。
=SUM(--TEXT({"0时