第13关 日期函数

你好，很开心又见到你。通过第12关文本函数的学习，你已经掌握了其使用方法，搞定常见的文本问题。

今天我们学习的是日期函数，也是我们五大函数的最后一类函数。

在工作中，日期能用在哪里呢？首先，完整的表格是要标明日期的。其次，我们想算员工的工期工时、员工生日的自动提醒，甚至是找到合同到期的员工，都离不开日期函数。搞定了日期> 函数，就再也不用一个个数日子了！

请你先试着运行几个简单的日期函数吧！请下载课程配套练习：

下载后打开案例，在【1-日期函数初体验】附表中，你有没有看到一份计算每月工作日的表？

现在，我们想快速知道每月有多少天？以往，我们在看每月的天数、工作日，是不是都要翻日历呢？现在有了日期函数，完全不用！

来，下面是一串已经写好的公式，请你先双击F2单元格右下角的十字角标，出现光标后" | "，把公式复制到F2单元格中，然后按【回车键】确认。

=day(E2)

然后，我们计算每月工作日天数，同样的，先双击G2单元格，出现光标后" | "，把公式复制到G2单元格中，然后按【回车键】确认。

=networkdays(D2,E2)

出现结果了么？老规矩，双击F2单元格，完成批量填充。

是不是发现，每月的工作日自动计算出来了？双击G2单元格右下角的十字角标，完成批量填充。做出如下效果：

通过刚才的实操体验，你已经感受到日期函数的高效，接下来就让我们正式学习第13关-日期函数，内容如下：

1.初识日期函数

日期函数就是与日期的构造、提取、计算相关的函数。根据是否需要计算可以分为基础日期函数和日期计算函数。

“分类”

日期函数的应用虽然并没有统计函数、查询函数广泛，但是在计算员工年龄与工龄、计算全年每月的工作日天数、合同到期日，有着不可替代的作用。

“应用”

2.基础日期函数

下面，我们就先来学习基础日期函数。

基础日期函数包含了六大函数now、today、day、month、year、date，它们具体的含义和格式如下：

“now、today”

在规范制表时候，我们需要加入日期和时间。换做以往，你会一个个手动输入。但这里，不需要，有几个快捷键和日期函数都可以搞定。

请你打开【2-当前日期】附表。是不是看到下图？

尝试在C2单元格，按快捷键【Ctrl+;】，在C3单元格，按快捷键【Ctrl+Shift+;】，观察一下做出什么结果。

是不是发现自动填写了日期和时间。其实today函数、now函数也有这样的功能。他们的格式如下：

把函数=today()复制到E2单元格，把函数=now()复制到E3单元格，也能出现当前的日期和时间。不同的是，函数生成的当前日期和时间会实时更新。

“year、month、day”

下面，我们开始学习年、月、日函数。

请你打开【3-提取和构造日期】附表，是不是看到下图？

我们要从【出生日期】这列，提取出年、月、日，还有今明年生日。别怕，我们的year、month、day函数帮你搞定。我们先来认识一下它们的格式：

下面，我们就来实操这3个函数，依照下面的操作图，依次提取年、月、日：

“date”

年月日是写好了，但是我们还想了解到员工今年、甚至是明年的生日，该怎么办呢？其实，我们用date函数就能解决。

date函数用于构建日期，我们先来了解一下它的含义和格式：

含义：根据年、月、日生成日期

格式：=date(年,月,日)

下面，我们来实操date函数，在H2单元格，输入以下公式，具体的函数拆解图如下：

然后，我们在年份上加1，就生成了计算明年生日的公式：

=date(year(today())+1,F2,G2)

计算员工今年生日的公式如下，你写出来了么？

=date(year(today()),F2,G2)

接着在I2单元格右下角，双击黑色角标实现批量填充。你是否做出了如下的效果呢？

学完了基础的日期函数，下面我们学习日期计算函数：

3.日期计算

“datedif”

日期计算函数，顾名思义，它有计算的功能。最典型的是datedif函数。

datedif函数用来计算两个日期间的天数/月数/年数。它可以用来干什么呢？比如，可以计算出员工的年龄、入职年限，甚至是员工距离下一个生日的天数。它的书写格式如下：

下面，请你点击【4-datedif函数】附表，我们来实操用datedif函数计算出员工的年龄。在H2单元格中输入计算员工年龄的公式，函数公式书写参考以下拆解图：

你用公式算出员工的年龄了么？老师写的公式如下，你写的公式是这样的么？

=datedif(E2,today(),"Y")

然后，我们计算员工入职年限（月），在I2单元格中输入计算员工入职年限的公式，函数公式书写参考以下拆解图：

接下来，我们在J2单元格中计算员工距离下一个生日还有多少天。这个公式的书写有点难，需要完成2个步骤，需要你耐心看完下面的文字说明和函数书写拆解图。

你用公式算出员工的入职年限（月）了么？老师写的公式如下，你写的公式是这样的么？

=datedif(D2,today(),"M")

第1步，我们首先要明确，对于员工今年的生日，只有过生日和没过生日两种，结合刚才学的datedif函数，我们对问题分个类：

如果今年生日没过，那计算今天到今年生日间隔日期 datedif(today(),F2,"D")

如果今年生日过了，那计算今天到明年生日间隔日期 datedif(today(),G2,"D")

函数公式书写参考以下拆解图：

第2步，因为涉及到了判断，所以调用if函数，写一个嵌套函数，具体公式书写参考以下拆解图：

你用公式算出员工距离下一个生日还有多少天么？老师写的公式如下，你写的公式是这样的么？

=IF(F2>today(),datedif(today(),F2,"D"),datedif(today(),G2,"D"))

（这个公式稍微有点难，做不出来也算正常，不影响下面课程的学习）

下面，我们来学一个可以计算工作日的函数networkdays：

“networkdays”

networkdays函数主要用于计算两个日期间的工作日，它的格式如下：

下面，我们来实操这个函数，请你点击【5-networkdays函数】附表。我们首先计算每月第1天和最后1天的日期。

还记得刚才学过的date函数么？计算每个月第1天:

=date(A2,B2,C2)

对于最后1天的日期怎么计算呢？并不难，就是下一个月第1天再减1，参考下面的函数拆解图：

然后，请你用day函数计算出每月有多少天：

=day(E2)，函数拆解图如下：

你写出了每月最后1天的函数了么？计算每月最后1天的函数公式如下：

=date(A2,B2+1,1)-1

使用我们刚才讲的networkdays函数，计算出每月的工作日：

=networkdays(D2,E2)

函数拆解图如下：

我们先以国庆节举例，假设国庆节放假日期是从10月1日到10月7日。我们在networkdays函数中加入第3个参数——节假日就可以搞定，计算每月工作日（去除节假日）：

=networkdays(D2,E2,$K$2:$K$8)，函数拆解图如下：

我们知道，实际的工作日中，是不含节假日的，所以要去掉节假日，怎么去掉呢？

对比一下10月份含节假日和不含节假日的工作日天数，是不是不一样：

4.条件格式与函数结合

学完了日期函数，我们给大家加餐学一个组合应用，就是函数功能与条件格式结合，它将为你打开新的应用可能。

请你打开【6-结合条件格式】附表，是否看到了C列是合同起始日期，D列是合同到期日。

在工作中，如果你是一家大型公司的hr，了解员工的合同日期，是基本工作。之前一个个去核对，很浪费时间。现在，我们把函数和条件格式结合使用，让它可以帮你自动标记出合同到期的员工！

这个需求用公式表达为：合同到期天数<=30 ；

其中，合同到期天数是合同到期日（D列）减去今天后剩下的天数，可以表达为：D2-today()；

进而明白“合同到期天数在30天内的员工”用公式表达为：

=D2-today()<=30

做具体操作之前，先做个需求分析。我们的目标是让“合同到期天数在30天内的员工”标记出来。

步骤1：建立条件格式的新规则。框选A2:D21区域-【条件格式】-【新建规则】-点击【使用公式确定要设置格式的单元格】，（不需要点确定），具体参考图如下：

下面，跟随老师，完成以下3个步骤，来突出显示一个月内合同到期的员工。

步骤2：输入公式（=D2-today()<=30 ），然后点【格式】，选择标识的颜色，点确定。意思是：如果公式成立，就会显示颜色，如果不成立，就不显示颜色。

然后，你是不是做出了如下的效果？

为什么，全部的合同都标记为橙色了呢？

还记得相对引用和绝对引用的区别么？原来，刚才我们涉及到引用单元格，我们没有锁定D列。请按照下面步骤继续操作：

步骤3：条件格式-管理规则-编辑规则-填写公式（=$D2-today()<=30 )，具体操作图如下：

修改完后确定，你就能找出合同快要到期的员工了，你是否做出了如下效果呢？

通过学习日期基础函数、日期计算函数、还有条件格式和函数结合，我们能活用日期函数。下面对课程做一个总结。

首先，我们学习了日期函数，能够快速用函数调用日期。

然后，我们学习了日期计算函数，可以用函数来计算员工工作天数、生日倒计时等。

最后，我们把条件格式和函数结合，能够通过组合，快速找到目标数据，实现效率的提升。

下一关我们将学习的是14关-函数出错问诊，老师带你搞定写函数会遇到的常见问题，以后就可以顺畅无阻地写函数，提升计算效率了！

5.下关预告

下载表格

第13关-13-50套实用计划模板