第5章　日期和时间函数

5.1　返回日期和时间

函数1：NOW函数（返回当前日期与时间）

函数功能

NOW函数表示返回当前日期和时间的序列号。

函数语法

NOW()

参数解释

NOW函数没有参数。

提示

NOW函数的返回值与当前电脑设置的日期和时间一致。所以只有当前电脑设置的日期和时间设置正确，NOW函数才返回正确的日期和时间。

实例解析

实例172　计算活动剩余时间

NOW函数可以返回当前的日期与时间值，因此利用此函数可以用于对活动精确的倒计时统计。

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

按Enter键即可计算出B1单元格时间与当前时间的差值，并使用TEXT函数将时间转换为正确的格式，如图5-1所示。

由于当前时间是即时更新的，因此通过按键盘上的“F9”键即可实现倒计时的重新更新，如图5-2所示。

公式解析

①　求B1中时间与NOW函数返回的当前时间的差值，返回的结果是时间差值对应的小数值。

② 外层套用TEXT函数，将时间小数值转换为更便于我们查看的正规时间显示格式。关于TEXT函数的学习可参见第3章。

函数2：TODAY函数（返回当前的日期）

函数功能

TODAY函数用于返回当前日期的序列号。

函数语法

TODAY()

参数解释

TODAY函数没有参数。

实例解析

实例173　计算展品陈列天数

某展馆约定某个展架上展品的上架天数不能超过30天，根据上架日期，可以快速求出已陈列天数，从而方便对展品陈列情况的管理。

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

按Enter键即可计算出B2单元格上架日期至今日已陈列的天数。

将鼠标指针指向C2单元格的右下角，光标变成十字形状后，向下复制公式，即可批量求取各展品的已陈列天数，如图5-3所示。

公式解析

①　求取“TODAY()-B2”的差值，默认会显示为日期值。

②　外层嵌套TEXT函数，将计算结果直接转换为数值。

实例174　判断借出图书是否到期

表格统计了图书的借出日期和还书日期，本例规定：借阅时间超过60天时，即显示“到期”，否则显示“未到期”。

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

按Enter键即可判断出借阅的图书是否到期。

将鼠标指针指向C2单元格的右下角，光标变成十字形状后，向下复制公式，即可快速判断出其他图书是否到期，如图5-4所示。

公式解析

①　求取“TODAY()-B2”的差值，并判断是否大于60。

②　如果①步为真，返回“到期”，否则返回“未到期”。

函数3：DATE函数（构建标准日期）

函数功能

DATE函数用于返回表示特定日期的序列号。

函数语法

DATE(year,month,day)

参数解释

• 　year：表示year参数的值可以包含一到四位数字。
• 　month：表示一个正整数或负整数，表示一年中从1月至12月的各个月。
• 　day：表示一个正整数或负整数，表示一月中从1日到31日的各天。

用法剖析

实例解析

实例175　将不规范的日期转换为标准的日期形式

由于数据来源不同或输入不规范，经常会出现将日期录入为如图5-5所示的B列中的样式。为了数据方便后期对数据的分析，可以一次性转换为标准日期。

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

按Enter键即可将B2单元格中的数值转换为日期形式。

将鼠标指针指向D2单元格的右下角，光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可将其他不规范的日期转换为标准日期形式，如图5-5所示。

嵌套函数

MID函数用于从给定的文本字符串中提取字符，提取的起始位置与结束位置都用参数来指定。

公式解析

①　使用MID函数在A2单元格从第1个字符开始提取，共提取4个字符数作为年份。以此类推，从第5个字符开始提取，共提取2个字符数作为月份；从第7个字符开始提取，共提取2个字符数作为日。

②　使用DATE函数可以将步骤①结果中的值转换为日期。

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

表格中统计了一段时间内临时工的工作起始日期，工作统一结束日期为“2018-12-20”，要求计算出每位临时工的实际工作天数。

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

按Enter键即可计算出B2单元格中的日期距离“2018-12-20”这个日期的间隔天数（但默认返回的是日期值）。

将鼠标指针指向C2单元格的右下角，光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，如图5-6所示。

选中C2∶C9单元格区域，在“开始”选项卡“数字”组中设置数字格式为“常规”格式即可正确显示工作天数，如图5-7所示。

公式解析

①　将“2018-12-20”这个日期转换为可以计算的日期。

②　用①步日期减去B2单元格中的日期。

函数4：TIME函数（构建标准时间）

函数功能

TIME函数表示返回某一特定时间的小数值。

函数语法

TIME(hour, minute, second)

参数解释

• 　hour：表示0～32767的数值，代表小时。
• 　minute：表示0～32767的数值，代表分钟。
• 　second：表示0～32767的数值，代表秒。

用法剖析

实例解析

实例177　计算指定促销时间后的结束时间

例如某网店预备在某日的几个时段进行促销活动，开始时间不同，但促销时间都只有两小时30分，利用时间函数可以求出每个促销商品的结束时间。

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

按Enter键计算出的是第一件商品的促销结束时间。

将鼠标指针指向C2单元格的右下角，光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可依次返回各促销商品的结束时间，如图5-8所示。

函数5：YEAR函数（返回某日对应的年份）

函数功能

YEAR函数用于返回某日期对应的年份，返回值为1900～9999之间的整数。

函数语法

YEAR(serial_number)

参数解释

serial_number：表示为一个日期值，其中包含要查找年份的日期。应使用DATE函数输入日期，或者将日期作为其他公式或函数的结果输入。

实例解析

实例178　计算出员工年龄

表格的C列中显示了各员工的出生日期。要求从出生日期快速得出各员工的年龄。

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

按Enter键得出结果（是一个日期值）。选中D2单元格，拖动右下角的填充柄向下复制公式，即可批量得出一列日期值，如图5-9所示。

选中“年龄”列函数返回的日期值，在“开始”选项卡“数字”组的下拉列表中选择“常规”格式，即可得出正确的年龄值，如图5-10所示。

公式解析

①　返回当前日期，然后使用YEAR函数根据当前日期返回年份值。

②　根据C2单元格的出生日期返回出生年份值。

③　计算②步与③步的差值，即为年龄值。

实例179　计算出员工工龄

表格的C列中显示了各员工入公司的日期。要求根据入公司的日期计算员工的工龄。

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

按Enter键得出结果（是一个日期值）。选中D2单元格，拖动右下角的填充柄向下复制公式，即可批量得出一列日期值，如图5-11所示。

选中“年龄”列函数返回的日期值，在“开始”选项卡“数字”组的下拉列表中选择“常规”格式，即可得出正确的工龄值，如图5-12所示。

公式解析

①　返回当前日期，然后使用YEAR函数根据当前日期返回年份值。

②　根据C2单元格的出生日期返回出生年份值。

③　计算第②步与第③步的差值，即为工龄值。

函数6：MONTH函数（返回日期中的月份）

函数功能

MONTH函数用于返回以序列号表示的日期中的月份。月份是1（一月）和12（十二月）之间的整数。

函数语法

MONTH(serial_number)

参数解释

serial_number：表示要查找的月份的日期。应使用DATE函数输入日期，或者将日期作为其他公式或函数的结果输入。

实例解析

实例180　判断是否是本月的应收账款

表格对公司往来账款的应收账款进行了统计，现在需要快速找到本月的账款。

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

按Enter键，返回结果为空，表示C2单元格中的日期不是本月的。

将鼠标指针指向D2单元格的右下角，光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可得到批量的判断结果，如图5-13所示。

实例解析

实例181　计算本月账款金额总计

当前表格统计了账款金额与借款日期，现在需要统计出本月的账款合计值。

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

按Ctrl+Shift+Enter组合键，即可计算出本月账款合计值，如图5-14所示。

公式解析

①　使用MONTH函数依次提取出B2∶B10单元格区域中各单元格的日期值的月份，并与系统当前日期的月份进行比较，相同的返回TRUE，不同的返回FALSE。返回的是一个数组。

②　将步骤①返回TRUE值的对应在B2∶B10单元格区域上的值取出，并使用SUM函数进行求和。

函数7：DAY函数（返回日期中的天数）

函数功能

DAY函数用于返回以序列号表示的某日期的天数，用整数1～31表示。

函数语法

DAY(serial_number)

参数解释

serial_number：表示要查找的那一天的日期。

实例解析

实例182　计算本月上旬的销售额合计值

表格中按日期统计了不同规格产品的销售记录，现在要求统计出1月份上旬的出库记录，可以使用DAY函数配合SUM和IF函数来设置公式。

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

按Ctrl+Shift+Enter组合键即可得出上旬的出库合计值，如图5-15所示。

公式解析

①　将A2∶A9单元格区域中所有的日期的日数都提取出来，返回的是一个数组。

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

③　把步骤②中返回TRUE值的对应在C2∶C9单元格区域中的值取出，并进行求和运算。

实例183　实现员工生日自动提醒

在档案统计表中，要求能根据员工的出生日期给出生日自动提醒，即当天生日的员工能显示出“生日快乐”文字。

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

按Enter键即可得出结果。

将鼠标指针指向E2单元格的右下角，光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，可以看到D列的日期只有与系统日期的月份与日数相同时才返回“生日快乐”文字，否则返回空值，如图5-16所示。

公式解析

①　提取D2单元格中日期的月数并判断其是否等于当前日期的月数。

②　提取D2单元格中日期的日数并判断其是否等于当前日期的日数。

③　判断第①与第②步两项判断是否同时满足。

④　当第③步结果为TRUE时，返回“生日快乐”。

函数8：WEEKDAY函数（返回指定日期对应的星期数）

函数功能

WEEKDAY函数表示返回某日期为星期几。默认情况下，其值为 1（星期天）到7（星期六）之间的整数。

函数语法

WEEKDAY(serial_number,[return_type])

参数解释

• 　serial_number：表示一个序列号，代表尝试查找的那一天的日期。应使用DATE函数输入日期，或者将日期作为其他公式或函数的结果输入。
• 　return_type：可选。用于确定返回值类型的数字。

用法剖析

实例解析

实例184　返回日期对应的星期数

表格的B列中显示了各员工的值班日期，要求根据值班日期快速得知对应的星期数，即得到C列的结果。

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

按Enter键得出结果。

选中C2单元格，拖动右下角的填充柄向下复制公式，即可批量根据日期返回对应的星期数，如图5-17所示。

公式解析

=WEEKDAY(B2,2)

返回B2单元格中的值班日期是星期几。

实例185　判断加班日期是平时加班还是双休日加班

表格的A列中显示了加班日期，要求根据A列中的加班日期判断是双休日加班还是平时加班。

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

按Enter键得出加班类型。

选中E2单元格，拖动右下角的填充柄向下复制公式，即可批量根据加班日期得出加班类型，如图5-18所示。

嵌套函数

OR函数属于逻辑函数类型。给出的参数组中任何一个参数逻辑值为TRUE，即返回TRUE；任何一个参数的逻辑值为FALSE，即返回FALSE。

公式解析

①　判断A2单元格中的星期数是否为6。

②　判断A2单元格中的星期数是否为7。

③　判断第①步结果与第②步结果中是否有一个满足。

④　如果第③步结果成立，返回“双休日加班”，否则返回“平时加班”。

实例186　计算每日的计时工资

本例中规定：职工在周一至周五正常上班8小时的工时工资为5元／小时，8小时以外则按1.5倍来计算，周六上班每小时按1.5倍计算。

现在需要计算出某职工每天的计时工资。

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

按Enter键即可得出5月1日的计时工资。

将鼠标指针指向C2单元格的右下角，光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可得出其他日期所对应的计时工资额，如图5-19所示。

公式解析

①　计算正常8小时上班的计时工资。

②　计算星期系数。判断A2单元格中的日期是否为周六或周日，如果小于周六则系数为1，否则系数为1.5。

③　计算出8小时以外的工资。“B2-8”为超过8小时的小时数，乘以5再乘以1.5倍即可计算出8小时以外的计时工资。

④　前面3步之和为总计时工资。

函数9：WEEKNUM函数（返回日期对应一年中的第几周）

函数功能

WEEKNUM函数用于返回一个数字，该数字代表一年中的第几周。

函数语法

WEEKNUM(serial_number,[return_type])

参数解释

• 　serial_number：表示一周中的日期。应使用DATE函数输入日期，或者将日期作为其他公式或函数的结果输入。
• 　return_type：可选。是一个数字，确定星期从哪一天开始。

实例解析

实例187　快速得知2019年中各节日在第几周

表格中显示了2019年中的各节日日期，要求快速得知各日期在全年中的第几周。

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

按Enter键得出结果。

选中C2单元格，拖动右下角的填充柄向下复制公式，即可批量得出结果，如图5-20所示。

公式解析

①　返回A2单元格中的日期所对应的周。

②　使用连字符“&”将其与“第”和“周”连接起来，形成第几周的格式。

函数10：EOMONTH函数（返回某日期在本月最后一天的序列号）

函数功能

EOMONTH函数用于返回某个月份最后一天的序列号，该月份与开始日期相隔（之前或之后）指示的月份数。它可以计算正好在特定月份中的到期日。

函数语法

EOMONTH(start_date, months)

参数解释

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

用法剖析

实例解析

实例188　根据活动开始日期计算各月活动天数

表格中显示了企业制定的活动计划的开始时间，结束时间都是到月底结束。现在要求根据活动开始日期返回各月活动的天数。

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

按Enter键得出的结果是2018-5-1到本月最后一天的天数（默认为一个日期值），选中B2单元格，拖动右下角的填充柄向下复制公式，如图5-21所示。

选中“活动天数”列函数返回的日期值，在“开始”选项卡的“数字”组的下拉列表中选择“常规”格式，即可显示出正确的天数，如图5-22所示。

公式解析

①　返回A2单元格中日期在当月的最后一天的序列号。

②　使用最后一天的序列号减去A2单元格日期的序列号，即可计算出当前日期到最后一天的天数。

实例189　统计离职员工的工资结算日期

公司在每月月初的第1天发放员工薪水，根据员工的离职日期可以得出其发薪日。

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

按Enter键即可得出第一位离职人员的工资结算日期。

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

公式解析

①　以0作为EOMONTH函数的参数，表示产生B2单元格中的月份所对应的最后一天的日期，然后加上数值1表示得到次月1日的序列值。

②　使用TEXT函数将步骤①得到的序列值格式转换为日期格式，显示出年月日格式。

实例190　计算优惠券有效期的截止日期

某商场发放的优惠券的使用规则是：在发出日期起的特定几个月的最后一天内使用有效，现在要在表格中返回各种优惠券的有效截止日期。

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

按Enter键返回一个日期的序列号（注意将单元格的格式更改为“日期”格式即可正确显示日期），选中D2单元格，拖动右下角的填充柄向下复制公式，如图5-24所示。

选中返回值的单元格区域，在“开始”选项卡的“数字”组中重新设置单元格的格式为“常规”即可得到截上日期，如图5-25所示。

公式解析

返回的是B2单元格日期间隔C2中指定月份后那一月最后一天的日期。

函数11：HOUR函数（返回时间中的小时数）

函数功能

HOUR函数表示返回时间值中的小时数。

函数语法

HOUR(serial_number)

参数解释

serial_number：表示一个时间值，其中包含要查找的小时。

实例解析

实例191　计算访问的时间的区间

某公司抽取了一日对公司网站的访问时间，并进行了记录，要求根据来访时间显示时间区间，从而实现统计分析哪个时间段的访问量最高。

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

按Enter键得出结果。

选中C2单元格，拖动右下角的填充柄向下复制公式，即可批量得出结果，如图5-26所示。

公式解析

①　根据B2单元格中时间提取小时数。

②　提取B2单元格中的小时数并加1，得出时间区间。然后使用&符号进行连接。

函数12：MINUTE函数（返回时间中的分钟数）

函数功能

MINUTE函数表示返回时间值的分钟数。

函数语法

MINUTE(serial_number)

参数解释

serial_number：表示一个时间值，其中包含要查找的分钟。

实例解析

实例192　计算出精确的停车分钟数

根据停车的开始时间与结束时间，可以精确地计算出停车的总分钟数，以方便准确收费。

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

按Enter键即可返回第一条记录的停车分钟数。

将鼠标指针指向D2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可返回其他停车记录的分钟数，如图5-27所示。

公式解析

①　将C2单元格的时间转换为分钟数。

②　提取B2单元格中时间的小时数，乘以60表示转换为分钟数。

③　提取B2单元格中时间的分钟数。

④　步骤①结果减去步骤②与步骤③结果即为停车分钟数。

函数13：SECOND函数（返回时间中的秒数）

函数功能

SECOND函数表示返回时间值的秒数。

函数语法

SECOND(serial_number)

参数解释

serial_number：表示一个时间值，其中包含要查找的秒数。

实例解析

实例193　计算商品的秒杀秒数

某店铺开展了几项商品的秒杀活动，分别记录了开始时间与结束时间，现在想统计出每种商品的秒杀秒数。

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

按Enter键计算出的值是时间值，将鼠标指针指向D2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，如图5-28所示。

选中返回值的单元格区域，在“开始”选项卡的“数字”组中重新设置单元格的格式为“常规”即可批量得出各商品秒杀的秒数，如图5-29所示。

公式解析

①　计算“C2-B2”中的小时数，两次乘以60表示转换为秒数。

②　计算“C2-B2”中的分钟数，乘以60表示转化为秒数。

③　计算“C2-B2”中的秒数。

④　三者相加为总秒数。