2017年最全的excel函數大全6—日期和時間函數（上）

上次給大家分享了《2017年最全的excel函數大全（5）——邏輯函數》，這次分享給大家日期和時間函數（上）。

DATE 函數

返回特定日期的序列號

描述

DATE 函數返回表示特定日期的連續序列號。

用法

DATE(year,month,day)

DATE 函數用法具有下列參數：

ü Year：必需。year 參數的值可以包含一到四位數字。Excel 將根據計算機正在使用的日期系統來解釋 year 參數。默認情況下，Microsoft Excel for Windows 使用的是 1900 日期系統，這表示第一個日期為 1900 年 1 月 1 日。

提示: 為避免出現意外結果，請對 year 參數使用四位數字。例如，「07」可能意味著「1907」或「2007」。因此，使用四位數的年份可避免混淆。

· 如果 year 介於 0（零）到 1899 之間（包含這兩個值），則 Excel 會將該值與 1900 相加來計算年份。例如，DATE(108,1,2) 返回 2008 年 1 月 2 日 (1900+108)。

· 如果 year 介於 1900 到 9999 之間（包含這兩個值），則 Excel 將使用該數值作為年份。例如，DATE(2008,1,2) 將返回 2008 年 1 月 2 日。

· 如果 year 小於 0 或大於等於 10000，則 Excel 返回 錯誤值 #NUM!。

ü 月：必需。 一個正整數或負整數，表示一年中從 1 月至 12 月（一月到十二月）的各個月。

· 如果 month 大於 12，則 month 會從指定年份的第一個月開始加上該月份數。例如，DATE(2008,14,2) 返回表示 2009 年 2 月 2 日的序列數。

· 如果 month 小於 1，則 month 會從指定年份的第一個月開始減去該月份數，然後再加上 1 個月。例如，DATE(2008,-3,2) 返回表示 2007 年 9 月 2 日的序列號。

ü 日：必需。 一個正整數或負整數，表示一月中從 1 日到 31 日的各天。

· 如果 day 大於指定月中的天數，則 day 會從該月的第一天開始加上該天數。例如，DATE(2008,1,35) 返回表示 2008 年 2 月 4 日的序列數。

· 如果 day 小於 1，則 day 從指定月份的第一天開始減去該天數，然後再加上 1 天。例如，DATE(2008,1,-15) 返回表示 2007 年 12 月 16 日的序列號。

注意: Excel 可將日期存儲為連續序列號，以便能在計算中使用它們。1900 年 1 月 1 日的序列號為 1，2008 年 1 月 1 日的序列號為 39448，這是因為它與 1900 年 1 月 1 日之間相差 39,447 天。需要更改數字格式（設置單元格格式）以顯示正確的日期。

案例

案例 1

例如：=DATE(C2,A2,B2) 將單元格 C2 中的年、單元格 A2 中的月以及單元格 B2 中的日合併在一起，並將它們放入一個單元格內作為日期。以下案例顯示了單元格 D2 中的最終結果。

案例 2根據其他日期計算某個日期

可以使用 DATE 函數創建基於其他單元格中日期的一個日期。例如，可以使用 YEAR、MONTH 和 DAY 函數來創建基於另一個單元格的周年紀念日期。假設，某個員工第一天上班的日期為 2016 年 10 月 1 日，則可以使用 DATE 函數創建他上班 5 周年的紀念日期：

1. DATE 函數會創建一個日期。

2. =DATE(YEAR(C2)+5,MONTH(C2),DAY(C2))

3. YEAR 函數會查找單元格 C2 並從中提取「2012」。

4. 「+5」表示加上 5 年，並在單元格 D2 中創建「2017」作為周年紀念日的年。

5. MONTH 函數從單元格 C2 中提取「3」。這將在單元格 D2 中創建「3」作為月。

6. DAY 函數從單元格 C2 中提取「14」。這將在單元格 D2 中創建「14」作為天。

案例 3 將文本字元串和數字轉換為日期

有時Excel的日期是無法識別的。這可能是因為數字與典型的日期不相似，也可能因為數據被設置成了文本格式。如果是這種情況，則可以使用 DATE 函數將信息轉換成日期。例如，在下圖中，單元格 C2 包含採用以下格式的日期：YYYYMMDD。它也被設置成了文本格式。若要將其轉換成日期，則可以將 DATE 函數與 LEFT、MID 和 RIGHT 函數配合使用。

1. DATE 函數會創建一個日期。

2. =DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))

3. LEFT 會在單元格 C2 中查找並從左起提取前 4 個字元。這將在單元格 D2 中創建「2014」作為轉換後日期的年。

4. MID 函數將在單元格 C2 中查找。它將從第 5 個字元開始，然後向右提取 2 個字元。這將在單元格 D2 中創建「03」作為轉換後日期的月。因為 D2 的格式設置為 Date，因此「0」不包括在最終結果中。

5. RIGHT 函數會在單元格 C2 中查找，然後從最右側開始向左提取前 2 個字元。這將在 D2 中創建「14」作為日期的日。

案例 4 按一定的天數加減日期

若要按一定的天數加減日期，只需向值或包含日期的單元格引用加上或減去天數即可。

在以下案例中，單元格 A5 包含我們想加上和減去 7 天（C5 中的值）的日期。

DATEDIF 函數

計算兩個日期之間的天數、月數或年數。

描述

計算兩個日期之間相隔的天數、月數或年數。警告：Excel 提供了 DATEDIF 函數，以便支持來自 Lotus 1-2-3 的舊版工作簿。在某些應用場景下，DATEDIF 函數計算結果可能並不正確。有關詳細信息，請參閱本文中的「已知問題」部分。

用法

DATEDIF(start_date,end_date,unit)

ü Start_date：用於表示時間段的第一個（即起始）日期的日期。 日期值有多種輸入方式：帶引號的文本字元串（例如 2001/1/30）、序列號（例如 36921，在商用 1900 日期系統時表示 2001 年 1 月 30 日）或其他公式或函數的結果（例如 DATEVALUE(2001/1/30)）。

ü End_date：用於表示時間段的最後一個（即結束）日期的日期。

ü Unit：要返回的信息類型：

其他

l 日期存儲為可用於計算的序列號。默認情況下，1899 年 12 月 31 日的序列號是 1，而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。

l DATEDIF 函數在用於計算年齡的公式中很有用。

案例

已知問題

「MD」參數可能導致出現負數、零或不準確的結果。若要計算上一完整月份后餘下的天數，可使用如下方法：

此公式從單元格 E17 中的原始結束日期 (5/6/2016) 減去當月第一天 (5/1/2016)。其原理如下：首先，DATE 函數會創建日期 5/1/2016。DATE 函數使用單元格 E17 中的年份和單元格 E17 中的月份創建日期。1 表示該月的第一天。DATE 函數的結果是 5/1/2016。然後，從單元格 E17 中的原始結束日期（即 5/6/2016）減去該日期。5/6/2016 減 5/1/2016 得 5 天。

DATEVALUE 函數

將文本格式的日期轉換為序列號

描述

DATEVALUE 函數將存儲為文本的日期轉換為 Excel 識別為日期的序列號。 例如，公式=DATEVALUE(1/1/2008) 返回 39448，即日期 2008-1-1 的序列號。 即使如此，請注意，計算機的系統日期設置可能會導致 DATEVALUE 函數的結果會與此案例不同。

如果工作表包含採用文本格式的日期並且要對這些日期進行篩選、排序、設置日期格式或執行日期計算，則 DATEVALUE 函數將十分有用。

用法

DATEVALUE(date_text)

DATEVALUE 函數用法具有下列參數：

ü Date_text 必需。代表採用 Excel 日期格式的日期的文本，或是對包含這種文本的單元格的引用。例如，用於表示日期的引號內的文本字元串 2008-1-30 或 30-Jan-2008。

· 使用 Microsoft Excel for Windows 中的默認日期系統時，參數 date_text 必須代表 1900 年 1 月 1 日和 9999 年 12 月 31 日之間的某個日期。 如果參數 date_text的值在此範圍之外， DATEVALUE函數將返回錯誤值 「#VALUE!。

· 如果省略參數 date_text 中的年份部分，則 DATEVALUE 函數會使用計算機內置時鐘的當前年份。 參數 date_text 中的時間信息將被忽略。

其他

l Excel 可將日期存儲為序列號，以便可以在計算中使用它們。 默認情況下，1900 年 1 月 1 日的序列號為 1，2008 年 1 月 1 日的序列號為 39,448，這是因為它距 1900 年 1 月 1 日有 39,447 天。

l 大部分函數都會自動將日期值轉換為序列數。

案例

DAY 函數

將序列號轉換為月份日期

描述

返回以序列數表示的某日期的天數。 天數是介於 1 到 31 之間的整數。

用法

DAY(serial_number)

DAY 函數用法具有下列參數：

ü Serial_number 必需。要查找的日期。應使用 DATE 函數輸入日期，或將日期作為其他公式或函數的結果輸入。例如，使用函數 DATE(2008,5,23) 輸入 2008 年 5 月 23 日。如果日期以文本形式輸入，則會出現問題。

其他

l Microsoft Excel 可將日期存儲為可用於計算的序列號。默認情況下，1900 年 1 月 1 日的序列號是 1，而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。

l 無論提供的日期值的顯示格式如何，YEAR、MONTH 和 DAY 函數返回的值都是公曆值。例如，如果提供的日期的顯示格式是回曆，則 YEAR、MONTH 和 DAY 函數返回的值將是與對應的公曆日期相關聯的值。

案例

DAYS 函數

返回兩個日期之間的天數

描述

返回兩個日期之間的天數。

用法

DAYS(end_date, start_date)

DAYS 函數用法具有以下參數。

ü End_date 必需。 Start_date 和 End_date 是用於計算期間天數的起止日期。

ü Start_date 必需。Start_date 和 End_date 是用於計算期間天數的起止日期。

注意: Excel 可將日期存儲為序列號，以便可以在計算中使用它們。 默認情況下，1900 年 1 月 1 日的序列號是 1，而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39447 天。

其他

l 如果兩個日期參數為數字，DAYS 使用 EndDate–StartDate 計算兩個日期之間的天數。

l 如果任何一個日期參數為文本，該參數將被視為 DATEVALUE(date_text) 並返回整型日期，而不是時間組件。

l 如果日期參數是超出有效日期範圍的數值，DAYS 返回 #NUM! 錯誤值。

l 如果日期參數是無法解析為字元串的有效日期，DAYS 返回 #VALUE! 錯誤值。

案例

DAYS360 函數

以一年 360 天為基準計算兩個日期間的天數

描述

按照一年 360 天的演算法（每個月以 30 天計，一年共計 12 個月），DAYS360 函數返回兩個日期間相差的天數，這在一些會計計算中將會用到。 如果財會系統是基於一年 12 個月，每月 30 天，可使用此函數幫助計算支付款項。

用法

DAYS360(start_date,end_date,[method])

DAYS360 函數用法具有下列參數：

ü Start_date、end_date 必需。 用於計算期間天數的起止日期。 如果 start_date 在 end_date 之後，則 DAYS360 函數將返回一個負數。 應使用 DATE 函數輸入日期，或者將從其他公式或函數派生日期。 例如，使用函數 DATE(2008,5,23) 以返回 2008 年 5 月 23 日。 如果日期以文本形式輸入，則會出現問題。

ü 方法 可選。 邏輯值，用於指定在計算中是採用美國方法 還是歐洲方法。

注意:Excel 可將日期存儲為序列號，以便可以在計算中使用它們。 默認情況下，1900 年 1 月 1 日的序列號為 1，2008 年 1 月 1 日的序列號為 39,448，這是因為它距 1900 年 1 月 1 日有 39,447 天。

案例

EDATE 函數

返回用於表示開始日期之前或之後月數的日期的序列號

描述

返回表示某個日期的序列號，該日期與指定日期 (start_date) 相隔（之前或之後）指示的月份數。 使用函數 EDATE 可以計算與發行日處於一月中同一天的到期日的日期。

用法

EDATE(start_date, months)

EDATE 函數用法具有以下參數：

ü Start_date 必需。一個代表開始日期的日期。應使用 DATE 函數輸入日期，或將日期作為其他公式或函數的結果輸入。例如，使用函數 DATE(2008,5,23) 輸入 2008 年 5 月 23 日。如果日期以文本形式輸入，則會出現問題。

ü Months必需。 start_date 之前或之後的月份數。 months 為正值將生成未來日期；為負值將生成過去日期。

其他

• Microsoft Excel 可將日期存儲為可用於計算的序列號。默認情況下，1900 年 1 月 1 日的序列號是 1，而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。

• 如果 start_date 不是有效日期，則 EDATE 返回 錯誤值 #VALUE!。

• 如果 months 不是整數，將截尾取整。

案例

EOMONTH 函數

返回指定月數之前或之後的月份的最後一天的序列號

描述

返回某個月份最後一天的序列號，該月份與 start_date 相隔（之後或之後）指示的月份數。 使用函數 EOMONTH 可以計算正好在特定月份中最後一天到期的到期日。

用法

EOMONTH(start_date, months)

EOMONTH 函數用法具有以下參數：

ü Start_date 必需。一個代表開始日期的日期。應使用 DATE 函數輸入日期，或將日期作為其他公式或函數的結果輸入。例如，使用函數 DATE(2008,5,23) 輸入 2008 年 5 月 23 日。如果日期以文本形式輸入，則會出現問題。

ü Months 必需。 start_date 之前或之後的月份數。 months 為正值將生成未來日期；為負值將生成過去日期。

注意: 如果 months 不是整數，將截尾取整。

其他

l Microsoft Excel 可將日期存儲為可用於計算的序列號。默認情況下，1900 年 1 月 1 日的序列號是 1，而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。

l 如果 start_date 不是有效日期，則 EOMONTH 返回 錯誤值 #NUM!。

l 如果 start_date 加 months 產生非法日期值，則 EOMONTH 返回 錯誤值 #NUM!。

案例

HOUR 函數

將序列號轉換為小時

描述

返回時間值的小時數。 小時數是介於 0 (12:00 A.M.) 到 23 (11:00 P.M.) 之間的整數。

用法

HOUR(serial_number)

HOUR 函數用法具有下列參數：

ü Serial_number 必需。 時間值，其中包含要查找的小時數。 時間值有多種輸入方式：帶引號的文本字元串（例如 6:45 PM）、十進位數（例如 0.78125 表示 6:45 PM）或其他公式或函數的結果（例如 TIMEVALUE(6:45 PM)）。

其他

• 時間值為日期值的一部分，並用十進位數表示（例如 12:00 PM 可表示為 0.5，因為此時是一天的一半）。

案例

ISOWEEKNUM 函數

返回給定日期在全年中的 ISO 周數

描述

返回給定日期在全年中的 ISO 周數。

用法

ISOWEEKNUM(date)

ISOWEEKNUM 函數用法具有下列參數：

ü 日期 必需。 日期是 Excel 用於日期和時間計算的日期-時間代碼。

其他

l Microsoft Excel 可將日期存儲為可用於計算的序列數字。 默認情況下，1900 年 1 月 1 日的序列數字是 1，而 2008 年 1 月 1 日的序列數字是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。

l 如果日期參數為無效數值，則 ISOWEEKNUM 將返回錯誤值 #NUM! 。

l 如果日期參數為無效日期類型，則 ISOWEEKNUM 將返回錯誤值 #VALUE! 。

案例

MINUTE 函數

將序列號轉換為分鐘

描述

返回時間值中的分鐘。 分鐘是一個介於 0 到 59 之間的整數。

用法

MINUTE(serial_number)

MINUTE 函數用法具有下列參數：

ü Serial_number 必需。 一個時間值，其中包含要查找的分鐘。 時間值有多種輸入方式：帶引號的文本字元串（例如 6:45 PM）、十進位數（例如 0.78125 表示 6:45 PM）或其他公式或函數的結果（例如 TIMEVALUE(6:45 PM)）。

其他

• 時間值為日期值的一部分，並用十進位數表示（例如 12:00 PM 可表示為 0.5，因為此時是一天的一半）。

案例

MONTH 函數

將序列號轉換為月

描述

返回日期（以序列數表示）中的月份。 月份是介於 1（一月）到 12（十二月）之間的整數。

用法

MONTH(serial_number)

MONTH 函數用法具有下列參數：

• Serial_number 必需。要查找的月份日期。應使用 DATE 函數輸入日期，或將日期作為其他公式或函數的結果輸入。例如，使用函數 DATE(2008,5,23) 輸入 2008 年 5 月 23 日。如果日期以文本形式輸入，則會出現問題。

其他

• Microsoft Excel 可將日期存儲為可用於計算的序列號。默認情況下，1900 年 1 月 1 日的序列號是 1，而 2008 年 1 月 1 日的序列號是 39448，這是因為它距 1900 年 1 月 1 日有 39448 天。

• 無論提供的日期值的顯示格式如何，YEAR、MONTH 和 DAY 函數返回的值都是公曆值。例如，如果提供的日期的顯示格式是回曆，則 YEAR、MONTH 和 DAY 函數返回的值將是與對應的公曆日期相關聯的值。

案例

NETWORKDAYS 函數

返回兩個日期間的完整工作日的天數

描述

返回參數 start_date 和 end_date 之間完整的工作日數值。 工作日不包括周末和專門指定的假期。 可以使用函數 NETWORKDAYS，根據某一特定時期內僱員的工作天數，計算其應計的報酬。

提示: 若要使用參數來指明周末的日期和天數，從而計算兩個日期間的全部工作日數，請使用 NETWORKDAYS.INTL 函數。

用法

NETWORKDAYS(start_date, end_date, [holidays])

NETWORKDAYS 函數用法具有下列參數：

ü Start_date 必需。 一個代表開始日期的日期。

ü End_date 必需。 一個代表終止日期的日期。

ü Holidays 可選。不在工作日曆中的一個或多個日期所構成的可選區域，例如：省/市/自治區和國家/地區的法定假日以及其他非法定假日。該列表可以是包含日期的單元格區域，或是表示日期的序列號的數組常量。

重要: 應使用 DATE 函數輸入日期，或者將日期作為其他公式或函數的結果輸入。 例如，使用函數 DATE(2012,5,23) 輸入 2012 年 5 月 23 日。 如果日期以文本形式輸入，則會出現問題。

其他

• Microsoft Excel 可將日期存儲為可用於計算的序列號。 默認情況下，1900 年 1 月 1 日的序列號為 1，2012 年 1 月 1 日的序列號為 40909，這是因為它距 1900 年 1 月 1 日有 40,909 天。

• 如果任一參數不是有效日期，則 NETWORKDAYS 返回 錯誤值 #VALUE!。

案例

NETWORKDAYS.INTL 函數

返回兩個日期之間的完整工作日的天數

描述

返回兩個日期之間的所有工作日數，使用參數指示哪些天是周末，以及有多少天是周末。 周末和任何指定為假期的日期不被視為工作日。

用法

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

NETWORKDAYS.INTL 函數用法具有以下參數：

ü start_date 和 end_date 必需。 要計算其差值的日期。 start_date 可以早於或晚於 end_date，也可以與它相同。

ü weekend 可選。 表示介於 start_date 和 end_date 之間但又不包括在所有工作日數中的周末日。 Weekend 是一個用於指定周末日的周末數字或字元串。

weekend 數值表示以下周末日：

周末字元串值的長度為七個字元，並且字元串中的每個字元表示一周中的一天（從星期一開始）。1表示非工作日，0表示工作日。 在字元串中僅允許使用字元1和 0。 使用 1111111 將始終返回 0。

例如，0000011 結果為星期六和星期日是周末。

ü holidays 可選。 一組可選的日期，表示要從工作日日曆中排除的一個或多個日期。 holidays 應是一個包含相關日期的單元格區域，或者是一個由表示這些日期的序列值構成的數組常量。 holidays 中的日期或序列值的順序可以是任意的。

其他

l 如果 start_date 晚於 end_date，則返回值將為負數，數量將是所有工作日的數量。

l 如果 start_date 在當前日期基準值的範圍之外，則 NETWORKDAYS.INTL 返回 錯誤值 #NUM!。

l 如果 end_date 在當前日期基準值的範圍之外，則 NETWORKDAYS.INTL 返回 錯誤值 #NUM!。

l 如果 weekend 字元串的長度無效或包含無效字元，則 NETWORKDAYS.INTL 返回 錯誤值 #VALUE!。

案例

以上是所有EXCEL的日期和時間函數（上）說明語法以及使用案例。這次分享中存在哪些疑問或者哪些不足，可以在下面進行評論。如果覺得不錯，可以分享給你的朋友，讓大家一起掌握這些excel的邏輯函數。