包含日期值的資料清理

---

---

您從不同來源獲得的資料可能包含日期值。本章將介紹如何準備包含日期值的資料以進行分析。

您將學習以下內容：

• 日期格式
  • 序列號格式的日期
  • 不同月-日-年格式的日期
• 將序列號格式的日期轉換為月-日-年格式
• 將月-日-年格式的日期轉換為序列號格式
• 獲取今天的日期
• 查詢指定天數後的工作日
• 自定義週末定義
• 兩個給定日期之間的工作日數
• 從日期中提取年、月、日
• 從日期中提取星期幾
• 根據年、月、日獲取日期
• 計算兩個日期之間的年、月、日數

日期格式

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函式。

輸出如下：