在日常工作中,通话时长的计算,或者是商业区停车时长的计算收费等,都是非常常见的。
它们有的是按分钟收费。
比如:电信通话时长,不足 1 分钟按 1 分钟计算。
有的是按半小时或者 1 小时计算。
比如:商业区停车收费,不足半小时或者不足 1 小时就全部按半小时或者 1 小时来计算收费等等。
今天,我们就来分享下按时间长度计算的问题与解决思路。
1、问题要求
如下图,是一张时长计算表:
要求是:计算开始时间与结束时间之间的分钟数。不足一分钟按一分钟计算。
比如:
开始时间是 10 时 30 分 35 秒;
结束时间是 10 时 38 分 59 秒;
之间的时间差是:8 分 24 秒;
不足一分钟按一分钟计算,最后结果即为:9 分钟。
2、解题方法
从上面的要求来看,解题思路大概可以分为以下两种:
▋方法 1
通常情况下,最为简单的而且是最好理解的是使用时间函数来处理。
比如:Hour 取小时数,Minute 取分钟数,Second 取秒数。
如下图:我们可以在【D3:D6】单元格输入如下公式:
=HOUR(C3-B3)*60+MINUTE(C3-B3)+(SECOND(C3-B3>0)
公式解析:
❶ 用【C3】的结束时间减去【B3】的开始时间,然后用 Hour 函数取出小时数并乘以 60,得出分钟数。
❷ 用【C3】的结束时间减去【B3】的开始时间,然后用 Minute 函数取出分钟数,直接得出分钟数。
❸ 用【C3】的结束时间减去【B3】的开始时间,然后用 Second 函数取出秒数并判断是否大于 0,如果大于 0,就返回 TRUE,用加法让其变成 1 分钟。如果小于等于 0,返回 False,用加法让其变成 0 分钟。
从而实现了不足 1 分钟,按 1 分钟计算的目的。
▋方法 2
其实,方法 1 只是从原理上来计算时间长度。
我们也可以换用专门用于计算进位的函数(Ceiling 函数)来轻松解决此类问题。
它的语法如下:
=CEILING(number, significance)
=CEILING (要舍入的值,倍数)
翻译成中文就是:
将要舍入的值向上舍入到指定倍数。
那么,上面的计算可以改成:
【D3】公式如下:
=CEILING(C3-B3,TIME(0,1,0))*1440
公式解析:
先用结束时间减去开始时间,之后按照一分钟的倍数向上舍入,之后再乘以 1440 分钟。
其中:Time 函数是表示时间的函数。
它的语法如下:
=Time (小时,分钟,秒)
这里是要按 1 分钟为倍数,所以小时和秒都为 0。
这里的 1440 是指:1 天为 24 小时,1 小时为 60 分钟,即:24*60=1440
此处的公式还可以进一步简化,直接改写为如下图:
=CEILING((C3-B3)*1440,1)
公式解析:
先用结束时间减去开始时间,之后乘以 1440 分钟数,然后按 1 的倍数向上舍入到整数。
两种解决思路供大家参考。
3、知识扩展
❶ 在日常工作中,我们可能还会接触到使用 Text 函数来获取和处理时间。
如下图:
公式:
=TEXT(C3-B3,"[m]")
先用结束时间减去开始时间,之后用 Text 函数取出之间的分钟数。
但是,小伙伴们要特别注意下:
这里用 Text 函数取出分钟数,它是直接截尾取整数。
也就是它只管小时和分钟数,而不管秒数有或者没有,都将其忽略不计。
另外:大家在取分钟数的时候千万不要只写一个 M。
如下图:
公式如下:
=TEXT(C3-B3,"m")
此时如果只写一个字母 M 的话,它只会取出两个时间相减的月份数,而不是分钟数。
这里的时间对应的日期默认为 1900 年 1 月 0 日。
所以取其月份数,结果返回 1。
❷ 在使用 Ceiling 函数的时候,有可能会出现下面的错误结果。
如下图:
公式与上面一模一样:
=CEILING((C3-B3)*1440,1)
结束时间减去开始时间应该为 10 分钟,但是结果却是错误的 11 分钟。
我们可以看下 Ceiling 函数里面的第一参数的运算结果,如下图:
里面的运算结果产生了一个极小的尾差,所以导致向上舍入到 11 分钟了。
解决方法可以用 Round 函数进行舍入处理。
修正公式如下:
=CEILING(ROUND((C3-B3)*1440,6),1)
这里用 Round 函数保留 6 位小位,超过 6 位的,直接四舍五入。
最后结果显示正确!
4、写在最后
今天,我们分享了日常工作中计算时间长度的常用方法与思路。
❶ 可以使用最基础的时间函数来处理时长问题。比如:Hour\Minute\Second 等。
❷ 也可以使用微软专门针对此问题设计的专用函数 Ceiling 函数来处理。
只不过在处理的时候,大家要特别注意上面提到的尾差问题,要不然很容易导致出错。
本文来自微信公众号:秋叶 Excel (ID:excel100),作者:明镜在心
广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,所有文章均包含本声明。