3.5 计算问题

在数据处理中，经常需要使用Excel处理日期和时间型数据的计算问题，本节通过实例从多个角度对日期和时间型数据的计算进行介绍。

3.5.1 基本计算

通常情况下，在Excel中对日期和时间型数据的处理是将日期和时间型数据进行直接的算术运算。计算两个日期和时间型数据之间的差，或者计算一个日期和时间型数据与一段时间的间隔，都可以通过算术运算来实现。

例如，在图3-29中，C列是对A列和B列进行算术运算的结果，D列是C列所使用的公式。

在数据处理过程中，经常会用到当前的日期和时间。Excel提供了函数TODAY()和NOW()来获取当前的日期和时间。通过这两个函数获取的日期和时间，能够在每次更新时，自动获取更新时刻的日期和时间。

3.5.2 计算特定时间单位间隔

日期和时间型数据，有时是一个特定的日期，有时是一个特定的时间，还有时同时包含日期和时间，如图3-30中A列所示。在图3-30中，A列和B列的数据是日期和时间型数据，C列是A列和B列的差值。

在对日期和时间型数据进行计算时，计算结果可能仍以单元格C2所示的日期和时间型数据显示。这时，需要把单元格格式调整为“常规”，将其转换为一个数值。图3-30中单元格C3的内容，是将单元格格式调整为“常规”后的显示结果。在运算结果中，不足一天的小时差异，以小数的形式显示。

单元格C4中显示了差值的文字说明，所使用的公式为：

=INT(B4-A4)&"天"&TEXT(B4-A4, "h小时m分s秒")

在上述公式中，INT函数用来获取单元格A4和B4差值的整数部分，作为计算的天数。例如，本例中INT(2.0818)得到的值是“2”，表示时间间隔是2天。

TEXT函数用来规范计算结果中时间显示的格式，将不足一天的小时、分钟、秒数以“h小时m分s秒”的形式显示出来。

在单元格C5中，所使用的公式为：

=ROUND((B5-A5)*24,2)&"个小时"

计算结果为单元格B5和A5之间相差的小时数。

用公式“(B5-A5)*24”计算二者之间相差的小时数。这里，单元格B5和A5作为日期和时间型数据相减，得到的是二者之间的天数差“2.0818”，每天24个小时，所以用差值乘以24。计算“2.0818*24”得到“49.9632”。

函数ROUND用来指定计算结果的显示位数。这里，ROUND(数据，2)表示将数据以2位小数的形式显示，计算“ROUND(49.9632,2)”得到“49.96”。

最后，将计算结果与“个小时”连接，得到“49.96个小时”。

按照上述思路，可以计算特定时间长度之间的分钟数、秒数等时间间隔。

3.5.3 跨天时间间隔计算

通常情况下，如果要计算在同一天内终止时间相比起始时间经过了多长时间，则计算起来比较方便，直接使用终止时间减去起始时间即可。例如，在图3-31中，单元格C2内使用公式“=B2-A2”，计算单元格B2相比单元格A2，经过了6小时5分钟。

某项工作需要0～24小时完成，而终止时间与起始时间可能不在同一天，终止时间可能是起始时间的第2天，出现了跨天的情况。这时，如果仍旧直接使用减法，则可能会出现问题。例如，在单元格C3内使用公式“=B3-A3”，单元格C3内显示的结果为“#######”。这是由于单元格B3的时间是第2天的时间，如果单从时间角度看，单元格B3的时间晚于单元格A3的时间，所以出现了错误。

遇到跨天的情况，考虑到可能出现了负数，使用函数ABS直接将其转换为绝对值的做法是不正确的。例如，在图3-31中，单元格C4内使用的公式为“=ABS(B4-A4)”，尝试将负数的结果转换为正数，但此时的结果是“1:01”，表示经过了1小时1分钟，它是用单元格A4减去单元格B4得到的，显然这个计算结果是错误的。

对于跨天时间长度的计算，正确的处理方式是使用对1取模的方式完成。将计算后得到的时间值对1取模后，运算结果变为正数，得到正确的时间间隔。例如，在图3-31中，单元格C5中使用的公式为“=MOD(B5-A5,1)”，得到了正确的结果“22:59”，表示经过了22小时59分钟。

上述对1取模，实际上是将时间对天取模的结果。在运算过程中，自动将后续时间作为下一天处理。

当然，这里仅仅考虑的是时间间隔都在24小时之内的情况，如果发生跨天的时间间隔大于24小时，则要重新更改公式完成计算。

3.5.4 DATEDIF函数

有时，需要计算两个日期之间的时间长度，并以“年月日”的形式显示出来，如图3-32所示。

在Excel中，DATEDIF函数用来计算两个日期之间的差值，它能够计算两个日期之间相差的年数、月数、天数等多种不同的差值及组合差值。该函数在Excel中被设置为隐藏函数，必须输入完整的函数名才能使用该函数。

该函数的基本语法格式为：

DATEDIF(起始日期，终止日期，参数)

式中，参数用来表示计算哪种差值，参数的具体情况如表3-1所示。

根据DATEDIF函数，可以很容易地计算出两个日期之间的年份差、月份差、天数差，然后将这些差值使用字符串连接符“&”进行连接。在图3-32中，单元格C2所使用的公式为：

=DATEDIF(A2, B2, "Y")&"年"&DATEDIF(A2, B2, "YM")&"个月"&DATEDIF(A2, B2, "MD")&"天"

上述是一种比较特殊的情况，即终值时间的日期是21日，起始时间是8日，终止时间内“日”的值是大于起始时间的。而在实际计算时，并不一定总是这样的情况，例如在图3-33中，C列是在忽略“年月”的情况下，使用函数“DATEDIF(开始时间，结束时间，"MD")”计算A列和B列相隔天数的结果。可以看到，此时的数据出现了一些难以理解的情况。

当在DATEDIF函数中使用参数MD时，会有一些特殊情况，如表3-2所示。

针对上述特殊情况，在表3-3中列举了在DATEDIF函数中使用参数MD时的几个实例，并做了简单的分析说明。

这里仅仅介绍了使用参数MD的情况，在使用其他参数时同样要考虑各种可能的意外情况。看完这些特例，也就明白DATEDIF函数为什么会被设置为隐藏了。在计算过程中需要考虑的因素太多，而且还可能出现错误的情况。所以，我们在使用这个被隐藏的函数时一定要小心，避免出现错误。

3.5.5 计算入职时间

在计算时间间隔时，有时希望将计算结果处理为小数形式。比如，在计算工资等相关问题时，常需要将“5年7个月”处理为单位为“年”的数值“5.58”，以方便后续的计算。

例如，在图3-34中，要计算某员工从入职（2012-8-8）到今天（2018-4-15）的入职时长。

这里，首先在单元格B2中通过DATEDIF函数计算出该员工入职的总月数，使用的公式为：

=DATEDIF(A2, TODAY(), "M")

根据上述总月数，可以换算得到入职的年数。在单元格C2中将总月数除以12，再使用FLOOR.MATH函数向下取整，即可得到入职的年数。使用的公式为：

=FLOOR.MATH(B2/12)

式中，FLOOR.MATH函数表示向下取整，例如“=FLOOR.MATH(5.67)”向下取整后得到“5”。

在单元格D2中，将总月数对12取模，换算得到总月数扣除整年后的月份余数。使用的公式为：

=MOD(B2,12)

式中，MOD函数表示取模运算，即取余数。例如“=MOD(68,12)”，计算68除以12的余数，计算结果是“8”。

最后，在单元格E2中换算出入职时长，使用的公式为：

=C2+D2/12

3.5.6 时间长度问题

在计算时间时，经常需要计算某个时间点特定一段时间后的时间。例如，计算某个时间点半个小时后的时间。

在日期和时间型数据中，一天的时间相当于数值1，每天有24个小时，那么每个小时相当于数值“1/24”。所以，计算特定时间长度后的一段时间，可以通过将时间长度换算为数值的方式实现。

例如，在图3-35中，计算单元格A2中“8:29:28”半个小时后的时间，可以使用的公式为“=A2+0.5/24”。

3.5.7 计算数字时间差

有时，会遇到纯数字形式的时间。例如，用数字“842”表示时间“8:42”。此时，可以通过TEXT函数将纯数字形式的时间设置为时间形式，采用的格式为：

TEXT(数字，"##! :##")

式中，“#”起到占位的作用，“!”是让当前数据使用自定义格式“:”。经过上述处理后，会在倒数第二位数字的前面加上“:”，纯数字形式转换为时间样式。完成格式转化后，在进行数学计算时，Excel会自动对其按照时间型数据进行处理。

如图3-36所示，在C列内使用TEXT函数计算B列和A列中两个纯数字型时间的差。