包含日期值的數據清理

---

---

您从不同来源获得的数据可能包含日期值。本章将介绍如何准备包含日期值的数据以进行分析。

您将学习以下内容：

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

日期格式

Excel支持以下两种方式的**日期**值：

• 序列号格式
• 不同的年-月-日格式

您可以转换：

• 序列号格式的**日期**到年-月-日格式的**日期**

• 年-月-日格式的**日期**到序列号格式的**日期**

序列号格式的日期

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

月-日-年格式的日期

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

• 以*(星号)开头的**日期**格式会响应为操作系统指定的区域日期和时间设置的更改

• 没有*(星号)的**日期**格式不受操作系统设置的影响

为了理解起见，您可以假设美国为区域设置。您可以为2016年6月8日的**日期**选择以下**日期**格式：

• *6/8/2016（受操作系统设置影响）
• *星期三，2016年6月8日（受操作系统设置影响）
• 6/8
• 6/8/16
• 06/08/16
• 8-Jun
• 8-Jun-16
• 08-Jun-16
• Jun-16
• June-16
• J
• J-16
• 6/8/2016
• 8-Jun-2016

如果您只输入两位数字来表示年份，并且：

• 数字为30或更高，Excel假设这些数字代表20世纪的年份。

• 数字低于30，Excel假设这些数字代表21世纪的年份。

例如，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** 函数进行此类操作。

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

假设2016年5月25日和2016年6月1日是节假日。那么，您的计算如下：

自定义周末定义

默认情况下，周末是星期六和星期日，即两天。您还可以使用**WORKDAY.INTL** 函数选择定义您的周末。您可以通过一个周末编号来指定您自己的周末，该编号对应于下表中给出的周末。您不必记住这些数字，因为当您开始键入函数时，您会在下拉列表中获得数字和周末的列表。

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

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

可能需要计算两个日期之间的工作日数，例如，计算按天计酬的合同工人的工资。

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

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

同样，周末被假定为星期六和星期日。您可以自己定义周末，并使用NETWORKDAYS.INTL函数计算两个日期之间的工作日数。在下面的屏幕截图中，只有星期五被定义为周末。

从日期中提取年、月、日

您可以使用excel函数DAY、MONTH和YEAR从日期列表中的每个日期中提取相应的日、月和年。

例如，考虑以下日期：

您可以从这些日期中提取日、月和年，如下所示：

从日期中提取星期几

您可以使用Excel WEEKDAY函数从日期列表中的每个日期中提取相应的星期几。

考虑上面给出的相同示例。

根据年、月、日获取日期

您的数据可能分别包含年份、月份和日期的信息。您需要组合这三个值来获取日期以执行任何计算。您可以使用DATE函数来获取日期值。

考虑以下数据：

使用DATE函数获取DATE值。

计算两个日期之间的年、月、日

您可能需要计算从给定日期开始经过的时间。您可能需要以年、月、日的方式获得此信息。一个简单的例子是计算一个人的当前年龄。它实际上是出生日期和今天之间的差值。您可以为此目的使用Excel DATEDIF、TODAY和CONCATENATE函数。

输出如下：