用 Excel 解决计时问题

在日常工作中,通话时长的计算,或者是商业区停车时长的计算收费等,都是非常常见的。

它们有的是按分钟收费。

比如:电信通话时长,不足 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),作者:明镜在心

广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,所有文章均包含本声明。

Published by

风君子

独自遨游何稽首 揭天掀地慰生平

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注