【原】时间函数time、hour、minute、second的基本用法

你好，我是刘卓。欢迎来到我的公号，excel函数解析。今天咱们来学习一下时间函数，主要有4个，分别是time、hour、minute和second。这4个函数比较简单，就一起介绍。

-01-

hour、minute、second函数

hour返回时间值的小时数。是介于0~23之间的整数。语法如下，只有1个参数：

HOUR(serial_number)

serial_number：要取出小时数的一个日期时间值。可以是文本型的日期时间，比如"9:00"，也可以是整数或小数表示的日期时间，或者是函数公式返回的日期时间。

minute返回时间值的分钟数。是介于0~59之间的整数。语法如下，同样只有1个参数，参数和hour的参数是一样的。

MINUTE(serial_number)

second返回时间值的秒数。是介于0~59之间的整数。语法如下，同样只有1个参数，参数和hour、minute的参数是一样的。

HOUR(serial_number)

下面看下简单的示例，A列是一些日期时间，B列是用hour取出小时数，C列是用minute取出分钟数，D列是用second取出秒数。

这个很好理解，和我们前面学过的year，month，day类似。这里来说一下第3个数据2020/5/3。它是个日期，没有时间，可以看作2020/5/3 0:00:00，所以取出的时分秒都是0。

-02-

time函数

再来看下time函数，它根据指定的小时、分钟和秒数返回时间。是介于0~0.99999999之间的小数，用来表示0:00:00~23:59:59之间的时间。语法如下，有3个参数：

TIME(hour, minute, second)

第1参数hour：代表小时。是介于0~32767之间的数字。任何大于23的值，都会除以24，余数将作为小时数。比如TIME(27,0,0) = TIME(3,0,0)。如果是小数，将被截尾取整。

第2参数minute：代表分钟。是介于0~32767直接的数字。任何大于59的值将被转换为小时和分钟。比如=TIME(0,66,0)=TIME(1,6,0)。如果是小数，将被截尾取整。

第3参数second：代表秒数。是介于0~32767之间的数字。任何大于59的值将被转换为小时、分钟和秒数。比如=TIME(0,0,121)=TIME(0,2,1)。如果是小数，将被截尾取整。

time函数和date函数类似，会自动进行进制的转换。下图的time将时分秒合成一个时间。

-03-

具体应用

1.计算通话停止时间

比如你和你对象煲电话粥，记录了通话开始时间和通话时长，求通话停止时间。我们知道通话停止时间=通话开始时间+通话时长。

但是这里通话时长的单位是分钟，二者不能直接相加，要把通话时长的分钟数加在开始时间的分钟数上，所以在C10单元格输入下面的公式：

=TIME(HOUR(A10),MINUTE(A10)+B10,SECOND(A10))

首先用hour、minute、second分别把开始时间的小时、分钟和秒数取出来。然后在分钟数上加上通话的分钟数，最后用time合成一个时间。

但是我们在工作中通常不用这个公式。第1，因为它有点长。第2，时间是特殊的数值，可以通过进制来转换。1天=24小时=1440分钟=86400秒。

那么公式为=A10+B10/60/24。首先把B10的分钟数转化为对应的小数，也就是多少天。然后再加上开始时间。用进制的转换，公式就简单多了。

除了用进制转换将分钟数变为小数，还可以用time函数将其转为小数。公式为=A10+TIME(,B10,)。这里TIME(,B10,)=TIME(0,B10,0)，是一种省略的写法。

还可以通过构造文本型的时间来将通话的分钟数转换为时间，公式为=A10+("0:"&B10)。"0:"&B10就构造出"0:65"，也就是0时65分。

还记得我们昨天说的时间数据的特征吗？秒数可以省略，时分秒中只有1个超过进制时，excel是可以识别为时间的。所以"0:65"是可以被转化为时间的。

2.计算停车费用

下图A列是停车的时长，假如1小时停车费是20元，求停车费用。比如12:10:32就代表停车时长为12小时10分32秒。

在C20单元格输入公式=ROUND(A20*24*20,0)。首先将停车时长乘以24转化为小时数，然后再乘以20，最后用round四舍五入到个位。这里用的是进制转化的方法。

如果用hour、minute、second来完成，公式如下，简直不要太长。

=ROUND((HOUR(A20)+MINUTE(A20)/60+SECOND(A20)/3600)*20,0)

思路是用hour、minute、second分别取出小时、分钟和秒数。然后分别转化为小时数，最后加起来乘以20。

3.把不规则的文本时间改为正常的时间

下图A列是一些不规则的文本时间，既有2分8秒，又有25分，还有55秒。现在要将这些不规则的时间改为B列的正常时间。该怎么办呢？手工改吗？烦躁！(真想"掐死"这样录入数据的"家伙"，有木有)。

其实找到方法，也简单。在B32单元格输入下面的公式，向下填充。

=LOOKUP(1,--({"0时0分","0时"}&A32))

思路是将不规范的时间组成"0时0分0秒"的规范时间。我们的数据源中没有小时，只有分钟和秒数。

对于只有秒数的，比如22秒，前面要连接"0时0分"。对于有分钟数的，比如"15分20秒"，前面要连接"0时"。

但是我们不想判断是只有秒还是有分有秒。那我们就可以用一个数组{"0时0分","0时"}将它们两个同时和不规范的时间连接起来。

{"0时0分","0时"}&A32返回的结果为{"0时0分2分8秒","0时2分8秒"}。其中第1个值"0时0分2分8秒"是不会被识别为时间的，第2个值"0时2分8秒"是可以被识别为时间的。

通过数学减负运算，--({"0时0分","0时"}&A32)返回的结果为{#VALUE!,0.00148148148148148}。第1个值返回错误值，第2个值返回时间对应的小数。

最后用LOOKUP(1,--({"0时0分","0时"}&A32))返回时间。因为时间是小数，肯定是小于1的。所以查找1就可以找到小数。而且lookup可以忽略错误值。

对于只有分钟的25分，这个公式也是适用的，因为"0时25分"是可以被识别为时间的，昨天说了秒数可以省略。

链接：

https://pan.baidu.com/s/1jp5SUK5cgE_TDuqC261dKw

提取码：vgq4