清理包含日期值的数据

您从不同来源获取的数据可能包含日期值。在本章中，您将了解如何准备包含用于分析的数据值的数据。

您将了解 -

日期格式
- 串行格式的日期
- 不同月-日-年格式的日期
将串行格式的日期转换为月-日-年格式
将月-日-年格式的日期转换为序列格式
获取今天的日期
查找指定天数后的工作日
定制周末的定义
两个给定日期之间的工作日数
从日期中提取年、月、日
从日期中提取星期几
从年、月、日获取日期
计算两个日期之间的年数、月数和天数

日期格式

Excel以两种方式支持日期值 -

串行格式
不同的年月日格式

您可以转换 -

序列格式的日期到年月日格式的日期
年月日格式的日期到串行格式的日期

串行格式的日期

序列格式的日期是一个正整数，表示给定日期和 1900 年 1 月1日之间的天数。当前日期和1900 年 1 月 1 日都包含在计数中。例如，42354 是代表 12/16/2015 的日期。

月-日-年格式的日期

Excel根据您选择的区域设置（位置）支持不同的日期格式。因此，您需要首先确定日期格式和当前数据分析的兼容性。请注意，某些日期格式带有 *（星号）前缀 -

以 *（星号）开头的日期格式响应为操作系统指定的区域日期和时间设置的更改
不带 *（星号）的日期格式不受操作系统设置的影响

为了便于理解，您可以假设美国为区域设置。您可以找到以下日期格式来选择日期- 2016 年 6 月 8^日-

*2016年6月8日（受操作系统设置影响）
*2016 年 6 月 8 日星期三（受操作系统设置影响）
6/8
2016年6月8日
2016年6月8日
6月8日
2016 年 6 月 8 日
2016 年 6 月 8 日
16年6月
6 月 16 日
J
歼16
2016年6月8日
2016 年 6 月 8 日

如果您仅输入两位数字来代表年份并且如果 -

数字为 30 或更高，Excel 假定这些数字代表二十世纪的年份。
数字低于 30，Excel 假定这些数字代表二十一世纪的年份。

例如，1/1/29 被视为 2029 年 1 月 1 日，1/1/30 被视为 1930 年 1 月 1 日。

将串行格式的日期转换为月-日-年格式

要将日期从串行格式转换为月-日-年格式，请按照以下步骤操作 -

单击“设置单元格格式”对话框中的“数字”选项卡。
单击“类别”下的“日期”。
选择区域设置。可用的日期格式将显示为类型下的列表。
单击“类型”下的“格式”以查看“示例”旁边的框中的预览。

选择格式后，单击“确定”。

将月-日-年格式的日期转换为序列格式

您可以通过两种方式将月-日-年格式的日期转换为序列格式 -

使用“设置单元格格式”对话框
使用 Excel DATEVALUE函数

使用“设置单元格格式”对话框

单击“设置单元格格式”对话框中的“数字”选项卡。
单击类别下的常规。

使用 Excel DATEVALUE 函数

您可以使用 Excel DATEVALUE函数将日期转换为序列号格式。您需要将日期参数括在“”中。例如，

=DATEVALUE ("6/8/2016") 结果为 42529

获取今天的日期

如果需要根据今天的日期进行计算，只需使用Excel函数TODAY()即可。结果反映了使用日期。

^{以下 TODAY () 函数使用截图于 2016 年 5月}16 日拍摄-

查找指定天数之后的工作日

您可能需要根据您的工作日执行某些计算。

工作日不包括周末和任何节假日。这意味着，如果您可以定义周末和假期，那么您所做的任何计算都将基于工作日。例如，您可以计算发票到期日、预计交货时间、下次会议日期等。

您可以使用 Excel WORKDAY和WORKDAY.INTL函数进行此类操作。

编号	功能说明
1.	工作日返回指定工作日数之前或之后的日期的序列号
2.	国际工作日返回指定工作日数之前或之后的日期序列号，使用参数指示周末是哪一天以及多少天

编号

功能说明

工作日

返回指定工作日数之前或之后的日期的序列号

国际工作日

返回指定工作日数之前或之后的日期序列号，使用参数指示周末是哪一天以及多少天

例如，您可以使用函数 TODAY 和 WORKDAY指定从今天开始的第 15^个工作日（下面的屏幕截图拍摄于 2016 年 5 月 16 日^{）。}

假设2016年5月25日^和2016年6月1^日是假期。然后，您的计算将如下 -

定制周末的定义

默认情况下，周末为周六和周日，即两天。您还可以选择使用WORKDAY.INTL函数定义您的周末。您可以通过与下表中给出的周末天相对应的周末编号来指定您自己的周末。您不需要记住这些数字，因为当您开始输入该函数时，您会在下拉列表中看到数字列表和周末。

周末	周末号码
星期六星期天	1 或省略
星期天星期一	2
星期一星期二	3
周二周三	4
星期三星期四	5
星期四星期五	6
星期五星期六	7
仅限周日	11
仅限周一	12
仅限周二	13
仅限周三	14
仅限周四	15
仅限周五	16
仅限周六	17 号

假设，如果周末仅是星期五，则需要在 WORKDAY.INTL 函数中使用数字 16。

两个给定日期之间的工作日数

可能需要计算两个日期之间的工作日数，例如，在计算按天支付工资的合同员工的工资时。

您可以使用 Excel 函数NETWORKDAYS和NETWORKDAYS.INTL查找两个日期之间的工作日数。正如 WORKDAYS 和 WORKDAYS.INTL 的情况一样，NETWORKDAYS 和 NETWORKDAYS.INTL 允许您指定假期，而使用 NETWORKDAYS.INTL 您还可以指定周末。

编号	功能说明
1.	网络日返回两个日期之间的整个工作日数
2.	国际网络日使用参数返回两个日期之间的整个工作日数，以指示周末是哪一天以及有多少天

编号

功能说明

网络日

返回两个日期之间的整个工作日数

国际网络日

使用参数返回两个日期之间的整个工作日数，以指示周末是哪一天以及有多少天

您可以使用 TODAY 和 NETWORKDAYS 函数计算今天和另一个日期之间的工作日数。在下面的屏幕截图中，今天是2016年5月16日^，结束日期是2016年6月16日。2016年5月25^日^和2016年6月1日^是假期。