Excel教程

Excel函数
Excel技巧

还在愁怎么计算时间间隔吗?多种玩法任你挑

当前位置:网站首页 > Excel教程

还在愁怎么计算时间间隔吗?多种玩法任你挑

* 来源: * 作者: admin * 发表时间: 2019-09-11 12:43:26 * 浏览: 32

在使用Excel做数据统计的时候,经常涉及到计算时间间隔的问题,比如人事做考勤要统计员工的加班时长,客服做报表要统计每个单子的受理时长……怎么统计?用计算器拼命地按,那你得准备做好加班的准备。

今天我来谈谈如何快速计算时间间隔的问题,别人需要一天才能完成的工作,你只需要一分钟就搞定;别人在马不停蹄加班的时候,你已经在喝咖啡,翘二郎蹆。不信,就继续往下看。

1、计算同一天内两个时间点的间隔时间

如下图:计算同一天内两个时间点的间隔时间,


【Excel技巧】还在愁怎么计算时间间隔吗?多种玩法任你挑


方法一:

直接两个时间点相减,即C2=B2-A2,

然后设置下C2单元格的格式,即自定义格式类型为:h:mm:ss。

【Excel技巧】还在愁怎么计算时间间隔吗?多种玩法任你挑


最后得到的结果如下:


【Excel技巧】还在愁怎么计算时间间隔吗?多种玩法任你挑


方法二:

直接在C2单元格里输入公式:=TEXT((B2-A2),"h:mm:ss")即可。


【Excel技巧】还在愁怎么计算时间间隔吗?多种玩法任你挑


这种方法可以省去设置单元格格式的步骤。

2、计算带日期且跨天的两个时间点的间隔时间

【Excel技巧】还在愁怎么计算时间间隔吗?多种玩法任你挑


方法一、

同样是C2=B2-A2,

不过,这里C2单元格的格式类型,得自定义格式类型为:[h]:mm:ss。

【Excel技巧】还在愁怎么计算时间间隔吗?多种玩法任你挑


最后得到的结果如下:


【Excel技巧】还在愁怎么计算时间间隔吗?多种玩法任你挑


补充说明:当然,像第一点(计算同一天内两个时间点的间隔时间)的第一个方法,你若要直接把C2单元格的格式类型,直接设置:自定义类型为:[h]:mm:ss也是可以。

方法二、

在C2单元格直接输入公式:=TEXT((B2-A2),"[h]:mm:ss")即可。


【Excel技巧】还在愁怎么计算时间间隔吗?多种玩法任你挑


3、计算不带日期且跨天的两个时间点的间隔时长

当两个时间点不带日期,跨天计算两个时间点的间隔时长,我们如果用上面的方法直接相减,会有负数的情况产生。不超过24小时的情况,我们可以结合MOD函数,具体以下公式,=MOD(结束时间-开始时间,1),即可得到正确的间隔时长。


【Excel技巧】还在愁怎么计算时间间隔吗?多种玩法任你挑


4、计算的间隔时间要转为天数、小时、分钟、秒数

如果计算出来的间隔时间我们需要转化为具体的天数、小时、分钟、秒数显示,如:1天2小时20分30秒,这时我们可以使用DATEDIF函数和TEXT函数结合来完成。

具体公式如下:

=DATEDIF(A2,B2,"D")&"天"&TEXT((B2-A2),"h小时m分s秒")

【Excel技巧】还在愁怎么计算时间间隔吗?多种玩法任你挑


公式说明:DATEDIF函数用来提取天数,TEXT函数提取时间并转换格式为小时分钟秒显示,他们之间用连接符“&”连接起来。

5、计算时间间隔的小时数

计算时间间隔的小时数,即以小时为单位,

公式:=(结束时间-开始时间)*24

【Excel技巧】还在愁怎么计算时间间隔吗?多种玩法任你挑


同理,

计算时间间隔的分钟数,即以分钟为单位,

公式:=(结束时间-开始时间)*24*60

【Excel技巧】还在愁怎么计算时间间隔吗?多种玩法任你挑


计算时间间隔的秒数,即以秒为单位,

公式:=(结束时间-开始时间)*24*60*60

【Excel技巧】还在愁怎么计算时间间隔吗?多种玩法任你挑


6、计算间隔时间取整

1、公司计算员工加班时间,都是很抠门的,按小时数取整,不足一小时部分舍去,割肉啊,然而还是得继续,

公式:=TEXT(结束时间-开始时间,”[h]”)


【Excel技巧】还在愁怎么计算时间间隔吗?多种玩法任你挑


2、当然也有稍微人性化一点的公司,计算员工加班时间,加班30分钟以下取0,满30分钟不满1小时取0.5小时,满1小时不满1.5小时取1小时,依次类推。

公式:=TEXT(FLOOR(结束时间-开始时间,1/48),"hh:mm")

【Excel技巧】还在愁怎么计算时间间隔吗?多种玩法任你挑


今天的总结就先这么多,后续有想到在做补充。