微信扫码观看全套Excel、Word、PPT视频
![]()
![]()
![]()
![]()
作者：ExcelHome
来源：Excel之家ExcelHome（ID：iexcelhome）
很多人都知道Excel函数公式威力强大，
但遇到问题时却写不出公式，
想找也找不到。
本系列将给大家带来15个
很多人都在找的Excel公式，
已备大家不时之需。
今天是系列教程的上篇。
01
日期和时间数据的合并
下图为某单位员工刷卡考勤的部分记录，需要根据B列的刷卡日期和C列的刷卡时间，得到日期和时间合并后的数据。
在D2单元格中输入以下公式，并向下复制到D10单元格，即可得到日期和时间合并后的数据。
=B2+C2
![]()
02
计算故障处理时长
下图为某运营商宽带故障报修记录表的一部分，需要根据C列的接单时间和E列的处理时间，计算故障处理时长。
![]()
在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,"")
03
计算员工在岗时长
下图所示为某企业员工加班考勤的部分记录，需要根据C列的上班打卡时间和D列的下班打卡时间，计算员工的加班工作时长。
![]()
如果在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列是以文本形式记录的员工操作用时，需要计算员工的平均操作时长。
![]()
将D2单元格格式设置为“时间”，然后输入以下数组公式，按组合键，计算结果为“0:01:12”。
=SUM(--TEXT({"0时","0时0分"}&B2:B10,"h:m:s;;;!0"))/9
由于B列的时间记录是文本内容，因此，Excel无法直接识别和计算。
使用字符串“{"0时","0时0分"}”与B2:B10单元格的内容连接，变成9行两列的内存数组“{"0时1分18秒","0时0分1分18秒";"0时59秒","0时0分59秒";…;"0时1分27秒","0时0分1分27秒"}”。
Excel 将“0时0分0秒”样式的文本字符串识别为时间，将“0时0秒”“0时0分”“0分0秒”等样式的字符串仍然识别为文本。
TEXT函数的第二参数使用“h:m:s;;;!0”，将时间样式的字符串转换为“h:m:s”样式，非时间样式的文本字符串强制显示为0。计算结果如下。
{"0:1:18","0";"0","0:0:59";…;"0:1:27","0"}
TEXT函数计算出的结果仍然为文本，加上两个负号，即负数的负数为正数，通过减负运算将文本结果转换为时间序列值。
最后将SUM函数的求和结果除以总人数9，得到考核平均用时。
05
从混合内容中提取时间和日期数据
从考勤机中导出的刷卡记录往往同时包含日期和时间，如下图所示，需要在C列和D列分别提取出B列刷卡记录中的日期和时间。
![]()
由于时间和日期数据的实质都是序列值，因此，既包含日期又包含时间的数据可以看作是带小数的数值。其中，整数部分为代表日期的序列值，小数部分为代表时间的序列值。
在C2单元格中使用以下公式提取日期数据。
=INT(B2)
=TRUNC(B2)
使用INT函数或TRUNC函数提取A列数值的整数部分，结果即为代表日期的序列值。
在D2单元格中可使用以下公式提取时间数据。
=B2-INT(B2)
=MOD(B2,1)
使用MOD函数计算A2单元格与1相除的余数，得到A2数值的小数部分，结果即为代表时间的序列值。如果结果显示为小数，可将单元格格式设置为“时间”格式。
除此之外，也可以使用TEXT函数完成日期时间的提取，以下公式可以提取出B列中的日期。
=--TEXT(B2,"e-m-d")
格式代码使用“e-m-d”，即“年-月-日”。
以下公式可以提取出B列中的时间。
=--TEXT(B2,"h:m:s")
格式代码使用“h:m:s”，即“时:分:秒”。
06
将英文月份转换为月份数值
如下图所示，A列为英文的月份名称，需要在B列转换为对应的月份数值。
![]()
在B2单元格中输入以下公式，并向下复制到B10单元格。
=MONTH(A2&1)
使用连接符“&”将A2单元格与数值“1”连接，得到新字符串“Apr1”，成为系统可识别的文本型日期样式，再使用MONTH函数提取出日期字符串中的月份。
YEAR、MONTH和DAY函数均支持数组计算，在按时间段的统计汇总中被广泛应用。
07
汇总指定时间段的销售额
下图为某单位2017年销售记录表的部分内容，A列是业务发生日期，D列是业务金额，需要计算上半年的业务总额。
![]()
可以使用以下公式完成汇总。
=SUMPRODUCT((MONTH(A2:A13) |
|
转播
