用 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），作者：明镜在心