Excel 数据分析 - 清理包含日期值的数据

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

你会了解到 −

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

日期格式

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

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

你可以转换 −

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

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

序列格式的日期

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

月-日-年格式的日期

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

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

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

为了便于理解，您可以假定美国为区域设置。 您会发现以下日期格式可供选择，用于日期 - 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 假定数字代表二十世纪的年份。

• 数字小于 30，Excel 假定数字代表二十一世纪的年份。

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

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

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

• 单击单元格格式对话框中的数字标签。

• 点击类别下的日期。

• 选择区域设置。 可用的日期格式将显示为类型下的列表。

• 单击类型 下的格式 以在示例 旁边的框中查看预览。

选择格式后，单击确定。

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

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

• 使用格式化单元格对话框

• 使用 Excel DATEVALUE 函数

使用单元格格式对话框

• 单击单元格格式对话框中的数字标签。

• 点击类别下的常规。

使用 Excel DATEVALUE 函数

您可以使用 Excel 的 DATEVALUE 函数将 Date 转换为 Serial Number 格式。 您需要将 Date 参数括在""中。 例如，

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

获取今天的日期

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

TODAY () 函数用法的以下屏幕截图拍摄于 2016 年 5 月 16 日^th −

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

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

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

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

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

假设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 日和 1< sup>st 2016 年 6 月是假期。

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

从日期中提取年、月、日

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

例如，考虑以下日期 −

从每个日期中，您可以按如下方式提取日、月和年 −

从日期中提取星期几

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

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

从年月日获取日期

您的数据可能分别具有有关年、月和日的信息。 您需要获取结合这三个值的日期才能执行任何计算。 您可以使用 DATE 函数获取日期值。

考虑以下数据 −

使用 DATE 函数获取 DATE 值。

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

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

输出如下 −