5.2　日期计算

函数14：DATEDIF函数（计算两个日期之间的年数、月数、天数）

函数功能

DATEDIF函数用于计算两个日期之间的年数、月数和天数。

函数语法

DATEDIF(date1,date2,code)

参数解释

• 　date1：表示起始日期。
• 　date2：表示结束日期。
• 　code：表示要返回的两个日期的参数代码。

用法剖析

表5-1说明了DATEDIF函数的code参数与返回值。

实例解析

实例194　统计固定资产的已使用月份

表格中显示的部分固定资产的新增日期，要求计算出每项固定资产的已使用月份。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键即可根据C2单元格中的新增日期计算出第一项固定资产已使用月数。

将鼠标指针指向D2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可批量计算各固定资产的已使用月数，如图5-30所示。

公式解析

=DATEDIF(C2,TODAY(),"m")

以C2单元格中的日期值为起始日期，结束日期为系统当前的日期值。返回两个日期之间的相差的月份数。

实例195　计算员工工龄

表格中显示了员工的入职时间，现在要求根据入职时间计算出员工的工龄。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键返回的是一个日期值。将鼠标指针指向D2单元格的右下角，光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，如图5-31所示。

保持公式返回结果的选中状态，再选择“开始”选项卡的“数字”组中设置数据格式为“常规”格式即可显示出正确的工龄，如图5-32所示。

公式解析

=DATEDIF(C2,TODAY(),"y")

以C2单元格中的日期值为起始日期，结束日期为系统当前的日期值。返回两个日期之间相差的年数。

实例196　设置员工生日提醒

某些公司会在员工生日时赠送生日礼物，为了方便人事部门的工作，利用函数可以更加方便工作人员的查看，以便及时送出生日礼品。本例将标识出当前日期开始7天内过生日的员工记录。

选中C2单元格，在公式编辑栏中输入公式：

按Enter键即可返回第一位员工的生日提醒。

将鼠标指针指向C2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可返回其他员工的生日提醒情况，如图5-33所示。

公式解析

①　“$B2-7”的日期值为起始日期，结束日期为系统当前的日期，忽略年份值并返回两个日期之间的天数，当两者相差在7天以内（<=7）时即是即将过生日的员工。当相差天数小于或等于7时返回TRUE，否则返回FALSE。

②　当步骤①中的结果为TRUE时则返回“提醒”，结果为FALSE时则返回空值。

实例197　统计大于12个月的账款

根据表格显示的借款时间和对应的借款金额，统计出时长为12个月内以及12个月以上的账款金额合计值。

选中E2单元格，在公式编辑栏中输入公式：

按Ctrl+Shift+Enter组合键即可返回12个月内的账款，如图5-34所示。

选中E3单元格，在公式编辑栏中输入公式：

按Ctrl+Shift+Enter组合键得出12个月以上的账款，如图5-35所示。

公式解析

①　用DATEDIF函数依次返回A2∶A8单元格区域中各个日期与当前日期相差的月数，返回的是一个数组。

②　依次判断步骤①中数组中各个值是否小于12，如果是，返回TRUE，如果不是，返回FALSE。返回的是一个数组。

③　将②步数组中是TRUE的对应在B2∶B8单元格区域上的值取出，并进行求和运算。

函数15：DAYS360函数（按照一年360天的算法计算两日期间相差的天数）

函数功能

DAYS360函数按照一年360天的算法（每个月以30天计，一年共计12个月），返回两日期间相差的天数，这在一些会计计算中将会用到。

函数语法

DAYS360(start_date,end_date,[method])

参数解释

• 　start_date：表示计算期间天数的起始日期。
• 　end_date：表示计算的终止日期。如果start_date在end_date之后，则DAYS360将返回一个负数。应使用DATE函数来输入日期，或者将日期作为其他公式或函数的结果输入。
• 　method：可选。一个逻辑值，它指定在计算中是采用欧洲方法还是美国方法。

提示

与DATEDIF函数的区别在于：DAYS360无论当月是31天还是28天全部都以30天计算，DATEDIF函数是以实际天数计算的。另外，计算两个日期之间相差的天数，要“算尾不算头”，即起始日当天不算作1天，终止日当天要算作1天。

实例解析

实例198　计算还款剩余天数

表格中的C列为借款金额的应还日期，D列为账期，本例要求计算出各项借款的还款剩余天数（如果返回的结果为负数则表示已经到期）。

选中E2单元格，在公式编辑栏中输入公式：

按Enter键即可返回第一项借款的还款剩余天数。

将鼠标指针指向E2单元格的右下角，光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可得到其他借款的还款剩余天数，如图5-36所示。

公式解析

①　二者相加为借款的到期日期。

②　按照一年360天的算法计算当前日期与①步返回结果间的差值。

函数16：EDATE函数（计算间隔指定月份数后的日期）

函数功能

EDATE函数用于返回表示某个日期的序列号，该日期与指定日期（start_date）相隔（之前或之后）指示的月份数。

函数语法

EDATE(start_date, months)

参数解释

• 　start_date：表示一个代表开始日期的日期。应使用DATE函数输入日期，或者将日期作为其他公式或函数的结果输入。
• 　months：表示start_date之前或之后的月份数。months为正值将生成未来日期，为负值将生成过去日期。

用法剖析

实例解析

实例199　计算食品的过期日期

本例将根据食品的生产日期和保质期，计算出食品过期的日期。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键即可根据产品的生产日期和保质期计算出过期日期（这里的日期格式为默认的常规格式），将鼠标指针指向D2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，如图5-37所示。

保持过期日期列的选中状态，选择“开始”选项卡的“数字”组中设置数据格式为“日期”格式，即可正确显示出食品的过期日期，如图5-38所示。

公式解析

=EDATE(B2,C2)

将B2单元格中的生产日期设置为开始日期，C2单元格时间为之后指示的月份数，最终返回结果为B2单元格日期间隔C2中指定月份后的日期。

提示

由于EDATE函数返回的是日期序列号，因此需要把公式所在单元格区域的数字格式修改为“日期”格式才能够正确显示。

实例200　提示合同是否到期

公司员工的合同签约时间各不相同，利用EDATE函数配合其他相关函数可以判断其合同是否过期。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键即可判断出第一位员工的合同是否到期。

将鼠标指针指向D2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可返回其他员工的合同的状态，如图5-39所示。

公式解析

①　首先使用EDATE函数计算出合同到期日，“C2*12”表示将C2单元格中的年份数转换为月份数。

②　判断①步结果与当前日期的差值是否小于0。

③　如果②步为真返回“合同过期”文字。

实例201　根据出生日期与性别计算退休日期

企业有接近于退休年龄的员工，人力资源部门建立表格予以统计，可以根据出生日期与性别计算退休日期。假设男性退休年龄为55岁；女性退休年龄为50岁，可按如下方法建立公式。

选中E2单元格，在公式编辑栏中输入公式（见图5-40）：

按Enter键返回是日期序列号。将鼠标指针指向E2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，如图5-40所示。

保持退休日期列的选中状态，选择“开始”选项卡的“数字”组中设置数据格式为“日期”格式，即可正确显示各位员工的退休日期，如图5-41所示。

公式解析

①　表示如果C2单元格显示为男性“C2="男"”返回TRUE，然后退休年龄为“TRUE*5+50”（“TRUE*5”等于5），如果C2单元格显示为女性，“C2="女"”返回FALSE，然后退休年龄为“FALSE*5+50”（“FALSE　*5”等于0），前面乘以12的处理是将前面的返回的年龄转换为月份数。

②　使用EDATE函数返回日期，此日期是D2中出生日期之后n个月（①步返回指定）的日期。

函数17：WORKDAY函数（获取间隔若干工作日后的日期）

函数功能

WORKDAY函数表示返回在某日期（起始日期）之前或之后、与该日期相隔指定工作日的某一日期的日期值。工作日不包括周末和专门指定的假日。

函数语法

WORKDAY(start_date, days, [holidays])

参数解释

• 　start_date：表示一个代表开始日期的日期。
• 　days：表示start_date之前或之后不含周末及节假日的天数。days为正值将生成未来日期，为负值将生成过去日期。
• 　holidays：可选。一个可选列表，其中包含需要从工作日历中排除的一个或多个日期。

用法剖析

实例解析

实例202　根据休假天数自动显示出休假结束日期

根据休假开始日期和休假天数可以计算出员工的休假结束日期。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键即可返回第一位员工的休假结束日期。

将鼠标指针指向D2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可返回其他员工的休假结束日期，如图5-42所示。

公式解析

=WORKDAY(B2,C2,$F$2∶$F$4)

将B2单元格中的日期值设置为开始日期，间隔C2单元格中指定的天数后的日期。间隔日期中不包含周末以及指定的节假日（本例在F2∶F4单元格区域中指定，因为公式要向下复制使用，所以使用绝对引用）。

函数18：WORKDAY.INTL函数

函数功能

WORKDAY.INTL函数用于返回指定的若干个工作日之前或之后的日期的序列号（使用自定义周末参数）。周末参数指明周末有几天以及是哪几天。工作日不包括周末和专门指定的假日。

函数语法

WORKDAY.INTL(start_date, days, [weekend], [holidays])

参数解释

• 　start_date：表示开始日期（将被截尾取整）。
• 　days：表示start_date之前或之后的工作日的天数。正值表示未来日期；负值表示过去日期；零值表示开始日期。day_offset将被截尾取整。l weekend：可选。指示一周中属于周末的日子和不作为工作日的日子。weekend是一个用于指定周末日的数字或字符串。
• 　holidays：可选。一组可选的日期，表示要从工作日日历中排除的一个或多个日期。holidays应是一个包含相关日期的单元格区域，或者是一个由表示这些日期的序列值构成的数组常量。holidays中的日期或序列值的顺序可以是任意的。

用法剖析

表5-2说明了WORKDAY.INTL函数的weekend参数与返回值。

实例解析

实例203　根据休假天数自动显示出休假结束日期

仍然沿用WORKDAY函数中的例子，要根据休假开始日期和休假天数可以计算出员工的休假结束日期，但要求是只有周日作为法定假日，且中间有五一假期排除在外，这时可以使用WORKDAY.INTL函数来建立公式。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键即可返回第一位员工的休假结束日期。

将鼠标指针指向D2单元格的右下角，光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可返回其他员工的休假结束日期（可将结果与WORKDAY函数中的例子相比较），如图5-43所示。

公式解析

计算B2单元格中日期间隔C3单元格中指定工作日后的日期。

①　此参数指定只有周日是周末日。

②　此参数为指定的排除计算的节假日，因为公式要向下复制使用，这一部分使用要绝对引用。

函数19：YEARFRAC函数（从开始到结束日所经过的天数占全年天数的比例）

函数功能

YEARFRAC函数表示返回开始日期和终止日期之间的天数占全年天数的百分比。

函数语法

YEARFRAC(start_date, end_date, [basis])

参数解释

• 　start_date：表示一个代表开始日期的日期。
• 　end_date：表示一个代表终止日期的日期。
• 　basis：可选。要使用的计数基准类型。

实例解析

实例204　计算员工请假天数占全年天数的百分比

表格显示了员工假期的起始日期和结算日期，需要计算出请假天数占全年工作日的百分比，可以利用YEARFRAC函数直接设置公式。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键，返回的是小数值，将鼠标指针指向D2单元格的右下角，光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，如图5-44所示。

选中返回结果，在“开始”选项卡的“数字”组中，将单元格的格式更改为“百分比”，即可正确显示请假天占全年天数的百分比值，如图5-45所示。

公式解析

=YEARFRAC(B2,C2,3)

B2和C2单元格中的日期值分别作为开始日期和终止日期，然后用实际天数除以365即可返回其天数占全年天数的百分比值。注意，指定此参数为3表示是一年365天计算。

函数20：NETWORKDAYS函数（计算两个日期间的工作日）

函数功能

NETWORKDAYS函数表示返回开始日期和终止日期之间完整的工作日数值。工作日不包括周末和专门指定的假期。可以使用NETWORKDAYS函数根据某一特定时期内雇员的工作天数，计算其应计的报酬。

函数语法

NETWORKDAYS(start_date, end_date, [holidays])

参数解释

• 　start_date：表示一个代表开始日期的日期。
• 　end_date：表示一个代表终止日期的日期。
• 　holidays：可选。不在工作日历中的一个或多个日期所构成的可选区域。

用法剖析

实例解析

实例205　计算临时工的实际工作天数

假设企业在某一段时间使用一批临时工，根据开始使用日期与结束日期可以计算每位人员的实际工作日天数，以方便对他们工资的核算。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键计算出的是开始日期为“2017/12/1”，结日期为“2018/1/10”这期间的工作日数。将鼠标指针指向D2单元格的右下角，光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，可以依次返回各位人员的工作日数，如图5-46所示。

公式解析

=NETWORKDAYS (B2,C2,$F$2)

计算B2和C2单元格中两个日期间的工作日数，F2为指定的法定假日，在公式复制过程中始终不变，所以使用绝对引用。

函数21：NETWORKDAYS.INTL函数

函数功能

NETWORKDAYS.INTL函数表示返回两个日期之间的所有工作日数，使用参数指示哪些天是周末，以及有多少天是周末。工作日不包括周末和专门指定的假日。

函数语法

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

参数解释

• 　start_date和end_date：表示要计算其差值的日期。start_date可以早于或晚于end_date，也可以与它相同。
• 　weekend：表示介于start_date和end_date之间但又不包括在所有工作日数中的周末日。weekend是一个用于指定周末日的周末数字或字符串。
• 　holidays：可选。表示要从工作日日历中排除的一个或多个日期。holidays应是一个包含相关日期的单元格区域，或者是一个由表示这些日期的序列值构成的数组常量。holidays中的日期或序列值的顺序可以是任意的。

实例206　计算临时工的实际工作天数

沿用上面的例子，要求根据临时工的开始工作日期与结束日期计算工作日数，但此时要求指定每周只有周一一天为周末日，此时可以使用NETWORKDAYS.INTL函数来建立公式。

选中D2单元格，在公式编辑栏中输入公式：

按Enter键计算出的是开始日期为“2017/12/1”，结日期为“2018/1/10”这期间的工作日数（这期间只有周一为周末日）。将鼠标指针指向D2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，可以依次返回各位人员的工作日数，如图5-47所示。

公式解析

计算B2单元格中日期到C3单元格中日期间的工作日天数。

①　此参数指定只有周一是周末日。

②　此参为指定的排除计算的节假日，因为公式要向下复制使用，这一部分使用要绝对引用。