利用时间类函数的高级应用

Excel提供了多种类型的时间类函数，可以完成不同时间类型的数据的计算。

注意：Excel的默认日期格式与“Excel选项-语言”的默认值有关，如果默认语言为“中文（中国）”，则默认的日期格式为“yyyy/mm/dd”；如果默认语言为“英语（美国）”，则默认日期格式为“mm/dd/yyyy”。

1．NOW( )、TODAY( )函数

用途：基于机器的时间和日期，获取现在/今天的时间/日期。返回的时间/日期格式受缺省设置或单元格格式影响。

用法：NOW( ); TODAY( )

说明：

图15-46　TODAY( )函数示例

这两个函数均为易变型函数（Volatile Functions）。当工作表发生任何改变，或者在其他任何工作表中因再次应用相同函数而触发获取时间/日期的功能时，原来获取的时间/日期会发生改变。

如果要获得确定的日期或时间，可以使用快捷键直接获取：Windows用户可以使用快捷组合键“Ctrl+；”（Ctrl键加分号键），Mac用户可以使用“^+；”（“^”键加分号键）获得日期；Windows用户可以使用快捷组合键“Ctrl+Shift+；”（Ctrl键加Shift键加分号键），Mac用户可以使用“^+ +；”（“^”键加“”键加分号键）获得时间。

图15-47　Windows快捷键与Mac快捷键

2．DAY( )、MONTH( )、YEAR( )和DATE( )函数

用途：用DAY( )、MONTH( )、YEAR( )函数从日期型数据中获取其“日”“月”“年”数据，并分别返回在1～31、1～12和1900～9999之间的整数。相反，用DATE函数可以将年、月、日数据“拼装” 成日期型数据。用法：YEAR(serial_number); MONTH(serial_number); DAY(serial_number); DATE(year, month, day)说明：

图15-48　DAY( )、MONTH( )、YEAR( )和DATE( )函数示例

Excel的日期开始于“1900/01/01”，因此，日期时间需大于此日期。

Excel对日期格式的“自适应性”非常好。例如，“=DAY("2020/1/21")”的结果为21，输入“=DAY("21-JAN-2020")”的结果同样为21。

DATE( )函数的参数year、month和day均为整数，当输入“不适当”的整数时，Excel会自动按照日期进位的方式算出相应的日期。例如，输入“=DATE(2020, 13, 3)”的返回值为“2021/1/3”，而输入“=DATE(2021, 8, -10)”的返回值为“2021/7/21”，但是输入“=DATE(1900, 1, -1)”则返回错误提示“#NUM!”。

截取身份证编号的日期段数据可以利用函数公式“=MID(J6, 7, 4)&"年"&MID(J6, 11, 2)& "月"&MID(J6, 13, 2)&"日"”。其中，单元格J6中存放了某个文本（或者“常规”）格式的身份证号码，而返回的单元格的格式默认为“常规”，也可以改换为任何日期型格式。

3．HOUR( )、MINUTE( )、SECOND( )和TIME( )函数

用途：HOUR( )、MINUTE( )和SECOND( )函数分别用于从时间数据中提取小时、分钟和秒的数据。相反地，TIME( )函数可以使用小时、分钟和秒的数据“组装”出完整的时间数据。

用法：HOUR(serial_number); MINUTE(serial_number); SECOND(serial_number); TIME(hour, minute, second)

说明：

图15-49　HOUR( )、MINUTE( )、SECOND( )和TIME( )函数示例

与DATE( )函数类似，TIME( )函数的参数hour、minute和second同样支持任意整数，如果数值大于时间的“合适”范围，则Excel将自动按照时间进位规则进行计算。例如，输入“=TIME(8, -6, 20)”，返回的时间为“7:54:20”；而输入“=TIME(72, 3, -35)”，返回的时间为“0:02:25”。

4．DATEDIF( )、YEARFRAC( )、DAYS( )和DAYS360( )函数

用途：DATEDIF( )函数用于获得日期之间的年、月、日差值，可以获得整年、整月或整日数；也可以获得两个日期之间的年、月、日数值差距，如“16年7个月零15天”。YEARFRAC( )函数用于获得小数型、按年计算的日期差值。

用法：DATEDIF(start_date, end_date, unit); YEARFRAC(start_date, end_date[basis]); DAYS(end_date, start_date); DAYS360(start_date, end_date)

说明：

图15-50　DATEDIF( )函数示例

DATEDIF( )函数的参数“unit”的单位“Y”“M”和“D”，分别返回整年、整月和整日数。“MD”考虑日期中的月、日数据并计算天数差，“YM”考虑日期中的年、月数据并计算月数差。

DAYS( )函数的计算结果等于当DATEDIF( )函数的第三个参数“unit”为“D”时的结果。

DAYS360( )函数与DAYS( )函数类似，该函数将一年按12个月、每月按30天计算。因此，若用DAYS360( )函数计算两个日期之间的天数，在折算为年时应该除以360。

图15-51　利用YEARFRAC( )函数计算日期差值(www.xing528.com)

这些函数在计算工龄之类的实际应用场景中被广泛使用。

5．EDATE( )、EOMONTH( )函数

用途：从一个给定的日期，前移或者后移若干个月，EDATE( )函数可以用于获得移动后的日期，EOMONTH( )函数可以用于获得移动后月份的最后一天。

用法：EDATE(start_date,months);EOMONTH(start_date,months)

说明：

图15-52　EDATE( )、EOMONTH( )函数操作示例

EOMONTH( )函数的名称实际上是“End of Month”的简写形式，往往用于计算正好在特定月份的最后一天到期的到期日。

6．WORKDAY( )、 NETWORKDAYS( )函数

用途：WORKDAY( )函数用于获得在某日期（起始日期）之前或之后，与该日期相隔指定工作日的某一日期的日期值。工作日不包括周末和专门指定的假日。在计算发票到期日、预期交货时间或工作天数时，也可以使用WORKDAY( )函数来扣除周末或假日。NETWORKDAYS( )函数则用于计算两个日期之间的净工作日，包含头尾。

图15-53　WORKDAY( )函数示例

用法：WORKDAY(start_date, days, [holidays]); NETWORKDAYS(start_date, end_date, [holidays])

说明：

图15-54　NETWORKDAYS( )函数示例

计算净工作日时，起始日期和终止日期位于闭区间。也就是说，起始日期和终止日期是被包含在“工作日”内的。这是职场以及旅行社的一贯算法。

在这里，周六、周日均默认为休息日。如果遇到了其他自己设定的工作制（或者是轮休工作制）的情况，则需要使用WORKDAY.INTL( )函数和NETWORKDAYS.INTL( )函数。

7．WORKDAY.INTL( )、NETWORKDAYS.INTL( )函数

用途：基于专门设定的周末和节假日，WORKDAY.INTL( )函数用于计算在某起始日期之前或之后的与该日期相隔指定工作日的某一日期的日期值。而基于专门设定的周末和节假日，NETWORKDAYS.INTL( )函数用于计算两个日期之间的净工作日。

用法：WORKDAY.INTL(start_date, days, [weekend], [holidays]); NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

说明：

参数“[weekend]”的设置如表15-8所示：

表15-8　参数“[weekend]”的设置

一个实用的设置是由0或1组成的七位数的字符串代码，每一位代表从周一到周日的每一天，0为工作日，1为周末，如“0000001”代表只有周日为休息日。且作为参数的代码要用半角双引号引起来。

图15-55　WORKDAY.INTL( )函数示例

这两个函数均包括缩写“INTL”，让人联想到微软的“好战友”英特尔（Intel）公司，英特尔在纳斯达克的代码就是“INTL”。这似乎是Excel开发人员给英特尔公司开的一个小玩笑。实际上，“INTL”也可以是International（国际的）的缩写，从含义上来说，后者更为准确。

图15-56　NETWORKDAYS.INTL( )函数示例

8．WEEKDAY( )、WEEKNUM( )函数

用途：WEEKDAY( )函数可用于查找某一个日期所对应的星期。当参数“return_type”为1或者缺省时，WEEKDAY( )函数将返回数值1～7，分别对应星期日至星期六；当参数“return_type”为2时，返回的1～7分别对应星期一至星期日。

而WEEKNUM( )函数可用于查找某个日期在全年中对应的周数。当参数“return_type”为1或者缺省时，包含1月1日的周为该年的第1周；当参数“return_type”为21时，包含第一个周四的周为该年的第1周。

图15-57　WEEKDAY( )、WEEKNUM( )函数示例

用法：WEEKDAY(serial_number, [return_type]); WEEKNUM(serial_number, [return_type])