2.2.5 實用的日期計算函數

Excel中的一系列日期計算函數可以快速處理日期和時間數據。常見的日期計算函數包括獲取當前日期和時間的TODAY、NOW函數，返回日期的年份、月份、天數的YEAR、MONTH、DAY函數，返回時間的小時、分鐘、秒數的HOUR、MINUTE、SECOND函數，拼接日期的DATE函數和拼接時間的TIME函數，獲取星期幾的WEEKDAY函數和計算一年中第幾周的WEEKNUM函數，計算兩個日期間隔的年份、月份、天數、工作日的DATEDIF、DAYS、NETWORKDAYS函數等。日常工作中制作業務報表或業務分析時，需要在不同日期維度上對業務指標進行統計，因此必須熟練掌握日期計算函數對業務數據中的日期字段進行處理的方法。下面通過實例對常用的日期計算函數進行舉例說明。

1.TODAY函數

功能說明：返回當前日期的序列號（序列號是Excel用于日期和時間計算的日期-時間代碼）。如果在輸入該函數之前單元格格式為“常規”，Excel會將單元格格式更改為“日期”。若要顯示序列號，必須將單元格格式更改為“常規”或“數字”。在默認情況下，1900年1月1日的序列號為1，2018年1月1日的序列號為43,101，因為它距1900年1月1日有43,100天。

語法：TODAY()

參數：無參數。

示例：區域A2:A5是TODAY函數相關的公式，區域B2:B5是計算說明，區域C2:C5是計算結果。

公式與步驟：

● 示例一：單元格C2內輸入公式“=TODAY()”。

● 示例二：單元格C3內輸入公式“=TODAY()-1”。

● 示例三：單元格C4內輸入公式“=TODAY()+1”。

● 示例四：單元格C5內輸入公式“=YEAR(TODAY())-1990”，結果如圖2-76所示。

提示：TODAY函數返回的當前日期的序列號。如圖2-76所示的TODAY函數返回的日期結果為“2018-10-23”，因為編寫此函數功能介紹并截圖的日期是“2018-10-23”。

2.NOW函數

功能說明：返回當前日期和時間的序列號。

語法：NOW()

參數：無參數。

示例：區域A2:A5是NOW函數相關的公式，區域B2:B5是計算說明，區域C2:C5是計算結果。

公式與步驟：

● 示例一：單元格C2內輸入公式“=NOW()”。

● 示例二：單元格C3內輸入公式“=NOW()-0.5”。

● 示例三：單元格C4內輸入公式“=NOW()+0.5”。

● 示例四：單元格C5內輸入公式“=NOW()+3”，結果如圖2-77所示。

提示：NOW函數返回當前日期和時間的序列號。由于Excel里面數值1代表日期的一天（24小時），所以在某個日期和時間上面加數值1代表返回一天后的日期和時間，加0.5代表返回12小時后的日期和時間。

3.YEAR、MONTH、DAY函數

功能說明：YEAR返回對應于某個日期的年份，YEAR作為1900～9999的整數返回。MONTH返回日期（以序列數表示）中的月份，月份是介于1到12的整數。DAY返回以序列數表示的某日期的天數，天數是介于1到31的整數。

語法：YEAR(serial_number)、MONTH(serial_number)、DAY(serial_number)

參數：serial_number必需。要處理的日期。

示例：區域A2:A4分別是YEAR、MONTH、DAY函數相關的公式，區域B2:B4是計算說明，區域C2:C4是計算結果。

公式與步驟：

● 示例一：單元格C2內輸入公式“=YEAR("2018-05-01")”。

● 示例二：單元格C3內輸入公式“=MONTH("2018-05-01")”。

● 示例三：單元格C4內輸入公式“=DAY("2018-05-01")”，結果如圖2-78所示。

4.HOUR、MINUTE、SECOND函數

功能說明：HOUR返回時間值的小時數，小時是介于0到23的整數；MINUTE返回時間值的分鐘數，分鐘是一個介于0到59的整數；SECOND返回時間值的秒數，秒數是0到59的整數。

語法：HOUR(serial_number)、MINUTE(serial_number)、SECOND(serial_number)

參數：serial_number必需。要處理的日期。

示例：區域A2:A4分別是HOUR、MINUTE、SECOND函數相關的公式，區域B2:B4是計算說明，區域C2:C4是計算結果。

公式與步驟：

● 示例一：單元格C2內輸入公式“=HOUR("12:15:30")”。

● 示例二：單元格C3內輸入公式“=MINUTE("12:15:30")”。

● 示例三：單元格C4內輸入公式“=SECOND("12:15:30")”，結果如圖2-79所示。

5.DATE函數

功能說明：返回表示特定日期的連續序列號。

語法：DATE(year,month,day)

參數：

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

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

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

示例：區域A2:A4分別是DATE函數公式，區域B2:B4是計算說明，區域C2:C4是計算結果。

公式與步驟：

● 示例一：單元格C2內輸入公式“=DATE(2018,5,10)”。

● 示例二：單元格C3內輸入公式“=DATE(2018,3,0)”。

● 示例三：單元格C4內輸入公式“=DATE(2018,RANDBETWEEN(7,8), RANDBE-TWEEN(1,31))”，結果如圖2-80所示。

6.TIME函數

功能說明：返回特定時間的十進制數字。

語法：TIME(hour,minute,second)

參數：

● hour必需。用0到32767的數字代表小時。

● minute必需。用0到32767的數字代表分鐘。

● second必需。用0到32767的數字代表秒。

示例：區域A2:A4分別是TIME函數公式，區域B2:B4是計算說明，區域C2:C4是計算結果。

公式與步驟：

● 示例一：單元格C2內輸入公式“=TIME(10,20,45)”。

● 示例二：單元格C3內輸入公式“=TIME(12,0,0)”。

● 示例三：單元格C4內輸入公式“=TIME(RANDBETWEEN(0,23),RANDBETWEEN (0,59),RANDBETWEEN(0,59))”，結果如圖2-81所示。

7.DATEDIF函數

功能說明：計算兩個日期之間間隔的年數、月數或天數。

語法：DATEDIF(start_date,end_date,unit)

參數：

● start_date必需。某個時間段的起始日期。

● end_date必需。某個時間段的結束日期。

● unit必需。要返回的計算類型。參數類型有“Y”“M”“D”“MD”“YM”“YD”。

示例：計算起始時間和結束時間之間的間隔年份、月份、天數。區域C2:C4分別是DATEDIF函數公式，區域D2:D4是計算說明，區域E2:E4是計算結果。

公式與步驟：

● 示例一：單元格E2內輸入公式“=DATEDIF(A2,B2,"Y")”。

● 示例二：單元格E3內輸入公式“=DATEDIF(A3,B3,"M")”。

● 示例三：單元格E4內輸入公式“=DATEDIF(A4,B4,"D")”，結果如圖2-82所示。

提示：

● 參數unit為“MD”表示start_date與end_date之間天數之差。忽略日期中的月份和年份。

● 參數unit為“YM”表示start_date與end_date之間月份之差。忽略日期中的天和年份

● 參數unit為“YD”表示start_date與end_date的日期部分之差。忽略日期中的年份。

以上是對日期計算函數的介紹，并采用示例對函數進行了功能講解，下面的日期計算函數案例一～案例二是日期計算函數的應用擴展。對于同一個實例采用了多種方法來解決。

8.日期計算函數案例一

案例說明：區域A2:A11是一組日期數據（范圍為2018/1/1～2018/12/31），需要計算不同日期對應的季度。

公式與步驟：

● 方法一：單元格C2內輸入公式“=IF(MONTH(A2)＜=3,1,IF(MONTH(A2)＜=6,2,IF (MONTH(A2)＜=9,3,4)))”，然后向下拖拽復制公式。

● 方法二：單元格D2內輸入公式“=CEILING(MONTH(A2)/3,1)”，然后向下拖拽復制公式。

● 方法三：單元格E2內輸入公式“=MATCH(MONTH(A2),{1,4,7,10},1)”，然后向下拖拽復制公式，結果如圖2-83所示。

9.日期計算函數案例二

案例說明：區域A2:A21是一組隨機生成的時間數據（范圍為00:00:00～23:59:59），需要統計不同時間段的時間數據個數（例如，[00:00:00～01:00:00），[1:00:00～2:00:00)區間的時間數據的個數）。

公式與步驟：

1）建立“區間下限”輔助字段，單元格B2內輸入公式“=HOUR(A2)”，然后向下拖拽復制公式。

2）建立“區間上限”輔助字段，單元格C2內輸入公式“=B2+1”，然后向下拖拽復制公式。

3）建立“拼接時間段”字段，單元格D2內輸入公式“="["&B2&":00:00～"&C2&":00:00)"”，然后向下拖拽復制公式。

4）單元格G2內輸入公式“=COUNTIFS(D:D,F2)”，然后向下拖拽復制公式，結果如圖2-84所示。