日期 – 第4页

Excel 将文本格式的日期转换为序列号：DATEVALUE函数

DATEVALUE函数用于将以文本格式表示的日期转换成序列号，其语法是：

DATEVALUE(date text)

其中，date text参数为以文本格式表示的日期。下面通过实例来具体讲解该函数的操作技巧。

如果用户想计算几个不同年份距离2019-12-31的天数，通过计算器或人工计算，比较费劲，可以利用DATEVALUE函数计算几个不同年份距离2019-12-31的天数。具体操作步骤如下。

STEP01：新建一个空白工作簿，重命名为“DATEVALUE函数”，切换至“Sheet1”工作表，输入本例的原始数据，如图13-6所示。

STEP02：选中C2单元格，在编辑栏中输入公式“=DATEVALUE(“2019-12-31”)-DATEVALUE(“1903-1-1”)”，然后按“Enter”键即可返回1903-1-1到2019-12-31的天数，如图13-7所示。

STEP03：选中C3单元格，在编辑栏中输入公式“=DATEVALUE(“2019-12-31”)-DATEVALUE(“1924-3-5”)”，然后按“Enter”键即可返回1924-3-5到2019-12-31的天数，如图13-8所示。

STEP04：选中C4单元格，在编辑栏中输入公式“=DATEVALUE(“2019-12-31”)-DATEVALUE(“1952-10-1”)”，然后按“Enter”键，返回1952-10-1到2019-12-31的天数，如图13-9所示。

图13-6　目标数据

图13-7　返回单元格B2对应结果

图13-8　返回B3单元格对应的天数

图13-9　返回B4单元格对应结果

STEP05：选中C5单元格，在编辑栏中输入公式“=DATEVALUE(“2019-12-31”)-DATEVALUE(“1959-12-31”)”，然后按“Enter”键即可返回1959-12-31到2019-12-31的天数，如图13-10所示。

STEP06：选中C6单元格，在编辑栏中输入公式“=DATEVALUE(“2019-12-31”)-DATEVALUE(“2009-12-31”)”，然后按“Enter”键即可返回2009-12-31到2019-12-31的天数，如图13-11所示。

图13-10　返回B5单元格对应结果

图13-11　返回B6单元格对应结果

此函数适用于将文本格式的日期转换成序列号，便于管理与统计。

Excel 显示日期天数

DAY函数用于返回指定任意日期在当月中的天数，介于1～31。其语法是DAY（serial number）

其中，serial number参数为要进行查找的日期。下面通过实例来具体讲解该函数的操作技巧。

打开“DAY函数.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图13-3所示。该工作表中记录了某公司员工的姓名及出生日期，要求利用DAY函数，返回员工生日的具体天数。具体操作步骤如下。

图13-3　原始数据

STEP01：选中D2单元格，在编辑栏中输入公式“=DAY(C2)”，然后按“Enter”键即可返回具体天数，如图13-4所示。

STEP02：选中D2单元格，利用填充柄工具向下复制公式至D7单元格，通过自动填充功能即可返回其他单元格所对应的当月具体天数，如图13-5所示。

图13-4　返回C2单元格对应的天数

图13-5　返回其他单元格对应的天数

此函数常用于配合其他日期函数使用。

Excel 显示当前系统日期的方法

TODAY函数用于返回系统当前日期，其语法是TODAY()。该函数没有参数。下面通过实例来具体讲解该函数的操作技巧。

某公司财务人员在制作年终报表的时候，需要记录当前修改日期。下面利用TODAY函数，记录当天修改的日期。具体操作步骤如下。

STEP01：新建一个空白工作簿，重命名为“员工资料表”。切换至“Sheet1”工作表，输入本例的原始数据，如图13-1所示。

STEP02：选中G2单元格，在编辑栏中输入公式“=TODAY()”，然后按“Enter”键即可返回当天修改的日期，如图13-2所示。

图13-1　原始数据

图13-2　返回当天修改日期

此函数广泛适用于人事及财务领域。但此函数所返回的当前日期是指当前所用计算机中的日期。

Excel 函数实战：从身份证种提取公司员工出生日期

打开“员工信息表.xlsx”工作簿，切换至“Sheet1”工作表，本例中的原始数据如图12-56所示。某办公人员需要从该工作表中提取公司员工的出生日期，以便计算工龄。下面通过具体的操作步骤来详细讲解该综合应用案例。

图12-56　原始数据

一般来说，身份证号码的第7位～14位数字为出生日期，可以先用MID函数返回身份证号码中出生日期数值，再使用TEXT函数把出生日期数值转换成数值文本格式，最后使用REPLACEB函数替换出生年份，得出具体的出生日期。

STEP01：选中B2单元格，在编辑栏中输入公式“=MID(A2,7,8)”，然后按“Enter”键即可返回数值“19831004”，如图12-57所示。

STEP02：选中B2单元格，利用填充柄工具向下复制公式至B20单元格，即可返回所有出生日期的数值，如图12-58所示。

图12-57　返回出生日期数值

图12-58　返回所有出生日期的数值

STEP03：选中C2单元格，在编辑栏中输入公式“=TEXT(B2,”0000-00-00″)”函数，按“Enter”键即可返回“1983-10-04”数值文本，如图12-59所示。

STEP04：选中C2单元格，利用填充柄工具向下复制公式至C20单元格，即可返回所有出生日期的文本，如图12-60所示。

图12-59　返回出生日期文本

图12-60　返回所有出生日期文本

STEP05：选中D2单元格，在编辑栏中输入公式“=REPLACEB(C2,1,5,” “)”函数，按“Enter”键即可返回出生日文本“10-04”，如图12-61所示。

STEP06：选中D2单元格，利用填充柄工具向下复制公式至D20单元格，即可返回所有出生日文本，如图12-62所示。

图12-61　返回出生具体日期

图12-62　返回所有出生日文本

设置有效性特定条件：Excel 2019日期有效性设置

工作表中还可以设置限制单元格中只能输入某个范围内的日期，例如只能输入2019年1月1日～2019年5月12日之间的日期。具体操作步骤如下。

STEP01：选择A2单元格，在“数据工具”组中单击“数据验证”下三角按钮，在展开的下拉列表中选择“数据验证”选项打开“数据验证”对话框。在“验证条件”窗格中单击“允许”选择框右侧的下拉按钮，在展开的下拉列表中选择“日期”选项，如图5-5所示。

STEP02：单击“数据”选择框右侧的下拉按钮，在展开的下拉列表中选择“介于”选项，然后在“开始日期”文本框中输入“2019-1-1”，在“结束日期”文本框中输入“2019-5-12”，最后单击“确定”按钮即可完成设置，如图5-6所示。

图5-5　设置验证条件

图5-6　设置限制日期

STEP03：设置完成后，在设置日期输入限制的单元格中只能输入2019年1月1日～2019年5月12日之间的日期，输入的日期不符合限制条件时，工作表中会弹出如图5-7所示的“Microsoft Excel”提示框。单击“重试”按钮即可重新在单元格中输入正确的日期。

图5-7　“Microsoft Excel”提示框

Excel图表制作之前：Excel选项操作

由于Excel对1900日期系统中1900年视为闰年，所以该日期系统有Bug，如果不需要和Lotus软件相兼容，在Excel 2003中，工具>选项>［重新计算］，在Excel 2007/2010，文件>Excel选项>高级>公式，勾选“1904日期系统”。该系统和1900日期系统的差异是：数值0通过数字格式转化后，其起始日期为1904-1-1日。

微软官方对此Bug给出了详细说明，请参见以下网址：

http://support.microsoft.com/kb/214019

在选中一个图表的前提下，Excel 2003在上述所讲［选项］对话框中可以设定［图表］选项卡，如图5.4-6所示。Excel 2007/2010需单击鼠标右键>选择数据>［选择数据源］>“隐藏单元格和空单元格”，如图5.4-7所示。

Excel2003选项对话框中的图表选项卡

图5.4-6　Excel2003选项对话框中的图表选项卡

Excel 2007隐藏和空单元格设置

图5.4-7　Excel 2007隐藏和空单元格设置

空单元的相关选项主要针对：柱形图、条形图、折线图、XY散点图、面积图、雷达图等图表系列，且数据源中包含未输入任何内容的空单元格。需要特别说明的是，面积类图表空单元格的绘制其实都以零值代表，但数据点标签的值引用会受选项的不同而变化。

不绘制／空距：无数据点，且当设置数据点间连线时，空单元格的位置会被设置为无连线，面积图不支持。
以零值代表／零值：数据点被赋值为0，处在数值坐标0的位置，该选项一般为默认选项。
以内差值替换／用直线连接数据：无数据点，且当设置数据点间连线时，空单元格的引用数值会被内差值替换，视觉上为忽略空值，两点直接相连。柱形和条形图不适用此选项。

注：如果在组合图表中包含了该设置不支持的图表类型，以上3个选项将无法设置。

提示

Excel图表遵循两种方式的内差值计算：

非平滑曲线，采用两点间数值平均的算法；
平滑曲线采用三次贝塞尔曲线算法，该算法较为复杂。

只绘制可见单元格数据／显示隐藏行列中的数据：该选项和隐藏单元格相关，Excel 2003和Excel 2007/2010在设置上正好相反。Excel 2003的“显示名称”和“显示值”选项，主要针对鼠标移至图表相应元素时，鼠标指针右下角出现的提示标签内容，该标签为只读模式，无法设置。

在Excel的全局选项中还包括诸如公式计算的最大误差精度设定（默认为0.001）等众多设定，需要花些时间来研究一下这些设定；这将会对接下来的操作助益良多。

注：在Excel 2003选项中，如果［编辑］选项卡的“单元格拖放”选项未被选中，则本节所述的拖曳功能将无法实现。

代表日期的8位数变为日期数据

这里，让我们来看一下如何运用这3个函数将表示日期的8位数值转换成日期数据。

我前一章中曾提到过用 Excel 处理日期数据时，需要像下面这样用“/”将年、月、日隔开。

2013/11/12

但是，有些公司也会用“20131112”这样的8位数值来表示日期。但是，这并不是常规的日期形式，只是一种数值，我们也无法运用该数值计算出天数或星期。因此，我们需要先将其转换成日期的数据形式（序列值）。

我们可以使用 DATE 函数制作序列值。比如，想要制作“2014/1/1”这个日期数据，首先按照下列方式，在第一参数中指定公历年，第二参数中指定月份，第三参数指定日期。

=DATE(2014,1,1)

那么，如何从单元格 A2的“20131112”中提取年、月、日的数值呢？请大家按照以下思路思考。

“年”的数值，提取单元格 A2“20131112”左数4个字符“2013”
“月”的数值，提取单元格 A2“20131112”第5个字开始的2个字符“11”
“日”的数值，提取单元格 A2“20131112”右数2个字符“12”

像这样，想要从目标单元格的数据中提取一部分文字，就要用到 LEFT 函数、MID 函数和 RIGHT 函数。

想要抽取单元格 A2左数第4个字符，需要在 B2中输入以下公式：

=LEFT(A2,4)

在单元格 B2中输入=LEFT(A2,4)

接下来导出月份数值。请按以下方式输入 MID 函数，在单元格 A2中从第5个字开始提取2个字符。

=MID(A2,5,2)

在单元格 C2中输入=MID(A2,5,2)

最后提取日期数值。为了返回单元格 A2右数2个字符，按以下公式输入 RIGHT 函数。

=RIGHT(A2,2)

在单元格 D2中输入=RIGHT(A2,2)

像这样，分别提取出年、月、日的数据后，再按照以下方式指定 DATE 函数的参数，我们就能够得到该日期的序列值。

在单元格 E2中输入=DATE(B2,C2,D2)

上述的操作步骤可通过以下公式在1个单元格中集中处理。

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

LEFT 函数、RIGHT 函数和 MID 函数能够从字符串的左数或右数的，以及从字符串中间开始只提取指定的字符数，是字符串处理的基础操作。灵活运用这些函数，可以自由应对不同的数据处理需求。

Excel如何从日期设置中导出星期

Excel 还可以从日期数据中得到当前日期为星期几。掌握这个方法后，在制作日历和行程表时会非常有效率。

Excel 中有个函数叫作 WEEKDAY 函数，其主要用途为返回某日期的星期数。但事实上，还存在一种比它更简单的函数——TEXT 函数。

例如，想要在单元格 B2中显示单元格 A2中的日期为星期几，我们可以在 B2中输入以下公式：

=TEXT(A2,”aaa”)

在单元格 B2中输入=TEXT(A2,”aaa”)

这时候，按照第二参数的指定方法，星期几的表示形式则变为：

“aaa”➛日
“aaaa”➛星期日
“ddd”➛Sun
“dddd”➛Sunday

Excel如何导出除周末和节假日外的营业天数

想要计算除双休日和节假日外距某个截止日期的营业天数，可以使用 NETWORKDAYS 函数。在一般的工作中，这种计算营业天数的案例十分常见。

由于 Excel 本身并不配备节假日的相关数据，因此我们在前期需要另外准备节假日一览表。在此制作一个以“节假日表”命名的工作表，然后照着下表制作一张节假日一览表。可以在网络上搜索节假日数据表。

节假日一览表

在单元格 A2中输入交货日期，想要计算出除去周末和节假日外距离该交货日期的工作日还剩几天时，只要用“当前日期”减掉“除周末和节假日外的截止日期”即可，公式如下：

=NETWORKDAYS(TODAY(),A2,节假日表!A2:A195)

此函数的参数表示意义如下：

第一参数：日期计算的开始日
第二参数：日期计算的结束日
第三参数：需要从日期计算过程中去掉含有节假日的范围

这种方式可以得出“距离今天为止还有多少工作日”的结果，所以开始日期中要填入 TODAY 函数。

第三参数用于指定节假日，在这一例子中实际指定为“节假日表”中含有节假日日期数据的单元格范围（即 A2：A195）。如果要把公司自己规定的休息天数考虑进去的话，可根据需要自行调整第三参数。

Exce随时查看距截止日期还有几天

在利用 Excel 管理客户档案时，最方便的莫过于能自动显示距离每位客户的生日、合同的更新日期还有几天这样的数据。如果想要在含有更新日期数据表格中的“剩余天数”一栏，实时计算出“距离更新日期还有几天……”，可以用“更新日期减去当前的日期”。

例如，按以下方式输入，就能导出截止到单元格 B2中的日期的剩余天数。

=B2-TODAY()

在单元格 C2中输入=B2-TODAY()

B2中的日期数据所对应序列值，与 TODAY 函数导出的当前日期的序列值，二者相减就会得出上述结果。“利用序列值来处理日期的相关计算”，希望诸位读者朋友能够从这个事例中掌握这个诀窍。