第5章 日期和时间函数
5.1 返回日期和时间
函数1：NOW函数（返回当前日期与时间）
函数功能
NOW函数表示返回当前日期和时间的序列号。
函数语法
NOW()
参数解释
NOW函数没有参数。
提示
NOW函数的返回值与当前电脑设置的日期和时间一致。所以只有当前电脑设置的日期和时间设置正确，NOW函数才返回正确的日期和时间。
实例解析
实例172 计算活动剩余时间
NOW函数可以返回当前的日期与时间值，因此利用此函数可以用于对活动精确的倒计时统计。
选中B2单元格，在公式编辑栏中输入公式：
按Enter键即可计算出B1单元格时间与当前时间的差值，并使用TEXT函数将时间转换为正确的格式，如图5-1所示。
图5-1
由于当前时间是即时更新的，因此通过按键盘上的“F9”键即可实现倒计时的重新更新，如图5-2所示。
图5-2
公式解析
① 求B1中时间与NOW函数返回的当前时间的差值，返回的结果是时间差值对应的小数值。
② 外层套用TEXT函数，将时间小数值转换为更便于我们查看的正规时间显示格式。关于TEXT函数的学习可参见第3章。
函数2：TODAY函数（返回当前的日期）
函数功能
TODAY函数用于返回当前日期的序列号。
函数语法
TODAY()
参数解释
TODAY函数没有参数。
实例解析
实例173 计算展品陈列天数
某展馆约定某个展架上展品的上架天数不能超过30天，根据上架日期，可以快速求出已陈列天数，从而方便对展品陈列情况的管理。
选中C2单元格，在公式编辑栏中输入公式：
按Enter键即可计算出B2单元格上架日期至今日已陈列的天数。
将鼠标指针指向C2单元格的右下角，光标变成十字形状后，向下复制公式，即可批量求取各展品的已陈列天数，如图5-3所示。
图5-3
公式解析
① 求取“TODAY()-B2”的差值，默认会显示为日期值。
② 外层嵌套TEXT函数，将计算结果直接转换为数值。
实例174 判断借出图书是否到期
表格统计了图书的借出日期和还书日期，本例规定：借阅时间超过60天时，即显示“到期”，否则显示“未到期”。
选中C2单元格，在公式编辑栏中输入公式：
按Enter键即可判断出借阅的图书是否到期。
将鼠标指针指向C2单元格的右下角，光标变成十字形状后，向下复制公式，即可快速判断出其他图书是否到期，如图5-4所示。
图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所示。
图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所示。
图5-6
选中C2∶C9单元格区域，在“开始”选项卡“数字”组中设置数字格式为“常规”格式即可正确显示工作天数，如图5-7所示。
图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-8
函数5：YEAR函数（返回某日对应的年份）
函数功能
YEAR函数用于返回某日期对应的年份，返回值为1900～9999之间的整数。
函数语法
YEAR(serial_number)
参数解释
serial_number：表示为一个日期值，其中包含要查找年份的日期。应使用DATE函数输入日期，或者将日期作为其他公式或函数的结果输入。
实例解析
实例178 计算出员工年龄
表格的C列中显示了各员工的出生日期。要求从出生日期快速得出各员工的年龄。
选中D2单元格，在公式编辑栏中输入公式：
按Enter键得出结果（是一个日期值）。选中D2单元格，拖动右下角的填充柄向下复制公式，即可批量得出一列日期值，如图5-9所示。
图5-9
选中“年龄”列函数返回的日期值，在“开始”选项卡“数字”组的下拉列表中选择“常规”格式，即可得出正确的年龄值，如图5-10所示。
图5-10
公式解析
① 返回当前日期，然后使用YEAR函数根据当前日期返回年份值。
② 根据C2单元格的出生日期返回出生年份值。
③ 计算②步与③步的差值，即为年龄值。
实例179 计算出员工工龄
表格的C列中显示了各员工入公司的日期。要求根据入公司的日期计算员工的工龄。
选中D2单元格，在公式编辑栏中输入公式：
按Enter键得出结果（是一个日期值）。选中D2单元格，拖动右下角的填充柄向下复制公式，即可批量得出一列日期值，如图5-11所示。
图5-11
选中“年龄”列函数返回的日期值，在“开始”选项卡“数字”组的下拉列表中选择“常规”格式，即可得出正确的工龄值，如图5-12所示。
图5-12
公式解析
① 返回当前日期，然后使用YEAR函数根据当前日期返回年份值。
② 根据C2单元格的出生日期返回出生年份值。
③ 计算第②步与第③步的差值，即为工龄值。
函数6：MONTH函数（返回日期中的月份）
函数功能
MONTH函数用于返回以序列号表示的日期中的月份。月份是1（一月）和12（十二月）之间的整数。
函数语法
MONTH(serial_number)
参数解释
serial_number：表示要查找的月份的日期。应使用DATE函数输入日期，或者将日期作为其他公式或函数的结果输入。
实例解析
实例180 判断是否是本月的应收账款
表格对公司往来账款的应收账款进行了统计，现在需要快速找到本月的账款。
选中D2单元格，在公式编辑栏中输入公式：
按Enter键，返回结果为空，表示C2单元格中的日期不是本月的。
将鼠标指针指向D2单元格的右下角，光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可得到批量的判断结果，如图5-13所示。
图5-13
实例解析
实例181 计算本月账款金额总计
当前表格统计了账款金额与借款日期，现在需要统计出本月的账款合计值。
选中D2单元格，在公式编辑栏中输入公式：
按Ctrl+Shift+Enter组合键，即可计算出本月账款合计值，如图5-14所示。
图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所示。
图5-15
公式解析
① 将A2∶A9单元格区域中所有的日期的日数都提取出来，返回的是一个数组。
② 依次判断①步数组中各个值是否小于10，如果是则返回结果TRUE，不是则返回FALSE。返回的是一个数组。
③ 把步骤②中返回TRUE值的对应在C2∶C9单元格区域中的值取出，并进行求和运算。
实例183 实现员工生日自动提醒
在档案统计表中，要求能根据员工的出生日期给出生日自动提醒，即当天生日的员工能显示出“生日快乐”文字。
选中E2单元格，在编辑栏中输入公式：
按Enter键即可得出结果。
将鼠标指针指向E2单元格的右下角，光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，可以看到D列的日期只有与系统日期的月份与日数相同时才返回“生日快乐”文字，否则返回空值，如图5-16所示。
图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所示。
图5-17
公式解析
=WEEKDAY(B2,2)
返回B2单元格中的值班日期是星期几。
实例185 判断加班日期是平时加班还是双休日加班
表格的A列中显示了加班日期，要求根据A列中的加班日期判断是双休日加班还是平时加班。
选中E2单元格，在公式编辑栏中输入公式：
按Enter键得出加班类型。
选中E2单元格，拖动右下角的填充柄向下复制公式，即可批量根据加班日期得出加班类型，如图5-18所示。
图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所示。
图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所示。
图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-21
选中“活动天数”列函数返回的日期值，在“开始”选项卡的“数字”组的下拉列表中选择“常规”格式，即可显示出正确的天数，如图5-22所示。
图5-22
公式解析
① 返回A2单元格中日期在当月的最后一天的序列号。
② 使用最后一天的序列号减去A2单元格日期的序列号，即可计算出当前日期到最后一天的天数。
实例189 统计离职员工的工资结算日期
公司在每月月初的第1天发放员工薪水，根据员工的离职日期可以得出其发薪日。
选中C2单元格，在公式编辑栏中输入公式：
按Enter键即可得出第一位离职人员的工资结算日期。
将鼠标指针指向C2单元格的右下角，光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可返回其他离职人员的工资结算日期，如图5-23所示。
图5-23
公式解析
① 以0作为EOMONTH函数的参数，表示产生B2单元格中的月份所对应的最后一天的日期，然后加上数值1表示得到次月1日的序列值。
② 使用TEXT函数将步骤①得到的序列值格式转换为日期格式，显示出年月日格式。
实例190 计算优惠券有效期的截止日期
某商场发放的优惠券的使用规则是：在发出日期起的特定几个月的最后一天内使用有效，现在要在表格中返回各种优惠券的有效截止日期。
选中D2单元格，在公式编辑栏中输入公式：
按Enter键返回一个日期的序列号（注意将单元格的格式更改为“日期”格式即可正确显示日期），选中D2单元格，拖动右下角的填充柄向下复制公式，如图5-24所示。
图5-24
选中返回值的单元格区域，在“开始”选项卡的“数字”组中重新设置单元格的格式为“常规”即可得到截上日期，如图5-25所示。
图5-25
公式解析
返回的是B2单元格日期间隔C2中指定月份后那一月最后一天的日期。
函数11：HOUR函数（返回时间中的小时数）
函数功能
HOUR函数表示返回时间值中的小时数。
函数语法
HOUR(serial_number)
参数解释
serial_number：表示一个时间值，其中包含要查找的小时。
实例解析
实例191 计算访问的时间的区间
某公司抽取了一日对公司网站的访问时间，并进行了记录，要求根据来访时间显示时间区间，从而实现统计分析哪个时间段的访问量最高。
选中C2单元格，在公式编辑栏中输入公式：
按Enter键得出结果。
选中C2单元格，拖动右下角的填充柄向下复制公式，即可批量得出结果，如图5-26所示。
图5-26
公式解析
① 根据B2单元格中时间提取小时数。
② 提取B2单元格中的小时数并加1，得出时间区间。然后使用&符号进行连接。
函数12：MINUTE函数（返回时间中的分钟数）
函数功能
MINUTE函数表示返回时间值的分钟数。
函数语法
MINUTE(serial_number)
参数解释
serial_number：表示一个时间值，其中包含要查找的分钟。
实例解析
实例192 计算出精确的停车分钟数
根据停车的开始时间与结束时间，可以精确地计算出停车的总分钟数，以方便准确收费。
选中D2单元格，在公式编辑栏中输入公式：
按Enter键即可返回第一条记录的停车分钟数。
将鼠标指针指向D2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可返回其他停车记录的分钟数，如图5-27所示。
图5-27
公式解析
① 将C2单元格的时间转换为分钟数。
② 提取B2单元格中时间的小时数，乘以60表示转换为分钟数。
③ 提取B2单元格中时间的分钟数。
④ 步骤①结果减去步骤②与步骤③结果即为停车分钟数。
函数13：SECOND函数（返回时间中的秒数）
函数功能
SECOND函数表示返回时间值的秒数。
函数语法
SECOND(serial_number)
参数解释
serial_number：表示一个时间值，其中包含要查找的秒数。
实例解析
实例193 计算商品的秒杀秒数
某店铺开展了几项商品的秒杀活动，分别记录了开始时间与结束时间，现在想统计出每种商品的秒杀秒数。
选中D2单元格，在编辑栏中输入公式：
按Enter键计算出的值是时间值，将鼠标指针指向D2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，如图5-28所示。
图5-28
选中返回值的单元格区域，在“开始”选项卡的“数字”组中重新设置单元格的格式为“常规”即可批量得出各商品秒杀的秒数，如图5-29所示。
图5-29
公式解析
① 计算“C2-B2”中的小时数，两次乘以60表示转换为秒数。
② 计算“C2-B2”中的分钟数，乘以60表示转化为秒数。
③ 计算“C2-B2”中的秒数。
④ 三者相加为总秒数。