Excel2020时间计算方法详解

Excel2020时间计算方法详解

一、Excel时间计算的基础

和日期一样，Excel处理时间的方式并不像我们看到的那样——

时间是由小时、分钟和秒钟组成的，有特殊格式的数据。Excel在

内部把每24小时的时间周期作为一个从0至1的小数存储

说明：把A列中的时间数据复制到C列，然后通过“单元格格式”对话框，将C列的格式设置为“常规”即可看到效果。

从中可以看出，每24小时的周期都从午夜零点(0:00:00)开始，存储为0。正午(12:00:00)则存储为0.5，因为从午夜零点至正午恰

好是一个整天的一半。到下一个午夜零点时(0:00:00)，Excel把它

存储为1。从上一个午夜零点开始至下一个午夜零点开始，正好经

历了24小时，这就是我们所说的，Excel处理时间的一个24小时

周期。

将时间作为小数存储使得Excel可以对时间进行运算。例如,要

计算两个时间之间的间隔长度，执行结束时间对应的小数减去开始

时间对应的小数的操作即可，具体应用下面会有实例介绍。

二、使用公式快速输入时间

如果需要在工作表中输入很多没有规律的时间系列。假如只用输入A列的数字，然后用Excel公式自动转换成需要得到的C列的时

间格式，就可以大大提高输入的效率。

这个问题所需的公式，和处理快速输入日期的问题相似，下面先来看一下。选中C2单元格，输入公式：

=TIME(LEFT(A2,2),MID(A2,3,2),RIGHT(A2,2))

回车后，再选中C2单元格，用公式填充柄复制公式到恰当单元格，如C10单元格。

这个公式的其余部分，可以参照上一节的“快速输入无规律的日期系列”部分理解。后面的操作也完全类似，可以仿照进行。

三、计算出工作的小时数

在一些类似于考勤表的工作表中，常常需要计算工作的小时数，这也就是前面所提到的，两个时间间隔长度的问题。我们在下面的工作表中进行操作。

选中B5单元格，输入公式：=(B4-B3)*24

回车后，显示计算结果8.25，即张路路7月1日的工作时间为8.25小时。为什么要在(B4-B3)之后乘以24呢?前面讲过，Excel在内部将每24小时的时间周期作为一个从0至1的小数存储，所以(B4-B3)得到的只是这两个时间对应的小数差值，这个差值再乘以24就可以让计算结果的单位转换为小时。由此可见，理解了Excel 处理时间的规律，有助于理解公式的含义。

然后选中B5单元格，用公式填充柄复制公式到G5单元格即可。

四、计算出考试的分钟数

上面一个实例，计算两个时间的差值，我们希望计算结果的单位为小时。那么，假如我们希望计算结果的单位为分钟，该用什么公式呢?下面用计算考试的分钟数来说明。

工作表中，我们要计算出每科考试的时间，放到D4:D11单元格中，单位为分钟。

处理的方法有两种，包括不使用函数的方法和使用函数的方法。

1、不使用函数的方法

选中D2单元格，输入公式：=(C4-B4)*24*60

2、使用函数的方法

选中D2单元格，输入公式：=CONVERT((C4-B4)*24,"hr","mn")

回车后可以看到得到的计算结果仍为90。

这个公式用到了一个名为CONVERT的函数，在公式中它实现的功能是把小时转换为分钟。其实它可以实现的单位转换功能还有很多，具体情况参考下面的说明。

说明：函数CONVERT的功能为将数字从一个度量系统转换到另一个度量系统中。它的语法为CONVERT(number,from_unit,to_unit)，其中参数Number代表以from_units为单位的需要进行转换的数值，参数from_unit代表数值number的单位，参数to_unit代表结果的

单位。如果用函数CONVERT进行日期或时间的转换，可以作为

from_unit和to_unit的是：年“yr”、日“day”、小时“hr”、

分“mn”、秒“sec”。

不管使用那种方法，输入公式后使用公式填充柄复制公式到恰当的单元格即可。

五、对时间的小时、分钟和秒进行处理

下面这个问题是来自于一位读者的提问，大致是这样的：我希望对时间用如下规则进行处理，如果时间的秒数大于等于30秒，则分

钟数加1，秒数归于0;如果时间的秒数小于30秒，则去掉秒数。下

面是根据这个规则处理的效果图。

我们先来看一下处理这个问题的公式，然后再解释这个公式用到的函数的含义。C2单元格中的公式为：

=IF(SECOND(A2)>=30,TIME(HOUR(A2),MINUTE(A2)+1,0),TIME(HOUR(

A2),MINUTE(A2),0))

这个公式共用到了5个函数，TIME函数前面已经介绍过，IF函

数也多次使用过了。这里主要介绍一下HOUR、MINUTE和SECOND这

三个函数。这个三个函数可以和日期的YEAR、MONTH和DAY对比理解，它们的功能分别为返回时间值的小时数、分钟数和秒数。语法

分别为HOUR(serial_number)、MINUTE(serial_number)和

SECOND(serial_number)，其中参数serial_number代表需要处理的

时间值。

这几个函数的基本意思理解之后，相信公式的理解就比较轻松了。

需要说明的是，用这个公式计算出的结果为时间的小数格式，为了看到的格式，需要打开“单元格格式”对话框，设置C2:C7单元格区域的格式为“时间”，并选择一种恰当的显示类型。