Excel時間和日期函數基礎

●日期和時間函數基礎日期是數據分析的重要組成部分。經常遇到這樣的提問：某一產品何時會到期？項目的完成周期是多長？這些問題和日期的正確估算，日期格式的正確輸入是息息相關的。設置易於理解的日期格式對於保證這些結果的正確理解非常重要。同時由於計算機程序解釋日期的規則比較複雜，所以在輸入日期時應儘可能的明確，以確保計算結果具有最高的精確性。了解日期的計算Microsoft Office Excel 將日期存儲為序列號（也可稱為序列值）。例如，在Microsoft OfficeExcel for Windows中，日期1900-1-1代表序列號1，2008-1-1則代表序列號39448，這是因為2008-1-1距1900-1-1有39 448天。同時Excel將時間存儲為小數，因為時間是組成天的一部分。日期和時間都可看作是數值，因此都可以進行加、減運算。要查看以序列號顯示的日期值和以小數顯示的時間值，可以通過修改包含日期或時間的單元格格式為「常規」來實現。兩位數年份的解釋要確保年份值按預計的方式解釋，則最好將年份值按四位數輸入（例如2008，而非08）。如果在文本格式的單元格中輸入兩位數年份的日期，或者在函數中輸入兩位數年份的日期作為參數時（如YEAR（"1/1/12"）），Excel對兩位年份的解釋如下：00至29解釋為2000至2029年。如輸入日期2/1/12，Excel將返回日期2012年2月1日。30至99解釋為1930至1999年。如輸入日期4/15/65，Excel將返回日期1965年4月15日。更改短日期格式為四位格式默認情況下，在工作簿中輸入日期時，顯示的都為兩位數年份。現在通過如下步驟將默認的日期格式更改為四位格式：從Windows菜單選擇「開始>控制面板」。雙擊「區域和語言選項」圖標，打開「區域和語言選項」對話框。在「區域和語言選項」對話框中選擇「區域選項」選項卡。單擊「自定義」按鈕，打開「自定義區域選項」對話框。單擊「日期」選項卡。在「短日期格式」列表中，選擇四位數年份的格式（"yyyy"）。單擊「確定」按鈕。

技巧：任意設置日期格式默認情況下，當在單元格輸入日期或時間的時候，Excel會按照系統短日期格式來顯示輸入的日期和時間。關於短日期格式的更改剛剛介紹過，這種通過控制面板更改後的日期除了控制Excel外，還會影響到其他多個應用程序。其實Excel內部也有好多日期和時間的格式，可以通過單擊「數字組」右下角的箭頭來顯示「設置單元格格式」對話框，再選擇「數字」選項卡下分類列表中的「日期」或「時間」項，便可看到它們相應的格式，如圖2-3所示，為日期的可選格式。

還有更方便的方法，就是利用自定義的日期和時間類數字格式代碼來定義需要的任何格式，如表2-1所示為「日期類自定義數字格式代碼」，表2-2為「時間類自定義數字格式代碼」。表2-1 日期類自定義數字格式代碼代 碼注 釋m使用沒有前導零的數字顯示月份（1~12）mm使用有前導零的數字顯示月份（01~12）mmm使用英文縮寫顯示月份（Jan~Dec）mmmm使用英文全稱顯示月份（January~December）mmmmm使用英文首字母顯示月份（J~D）d使用沒有前導零的數字顯示日期（1~31）dd使用有前導零的數字顯示日期（01~31）ddd使用英文縮寫來顯示星期幾（Sun~Sat）dddd使用英文全稱來顯示星期幾（Sunday~Saturday）aaaa使用中文來顯示星期幾（星期一~星期日）aaa使用中文來顯示星期幾（一~日），不顯示「星期」yy使用兩位數顯示年份（00~99）yyyy使用4位數來顯示年份（1900~9999）表2-2 時間類自定義數字格式代碼代 碼注 釋h使用沒有前導零的數字顯示小時（0~23）hh使用有前導零的數字顯示小時（00~23）m使用沒有前導零的數字顯示分鐘（0~59）mm使用有前導零的數字顯示分鐘（00~59）s使用沒有前導零的數字顯示秒（0~59）ss使用有前導零的數字顯示秒（00~59）[ ]顯示超出24的小時數或大於60的分鐘與秒數AM/PM使用12小時制來顯示的上午或下午知道了這些日期和時間的自定義數字格式代碼後，再也不必擔心在格式中找不到想要的日期和時間格式了，不論需要什麼類型的日期和時間格式，都可以通過「設置單元格格式」對話框選擇「數字>分類>自定義」來完成，如圖2-4所示。

在這個位置輸入任意日期和時間的自定義格式代碼圖2-5所示為一些常用的由自定義格式代碼創建的日期和時間格式，供讀者參考。

更改日期系統Excel共支持兩種日期系統：1900年日期系統和1904年日期系統。Microsoft Office Excel forWindows默認的日期系統為1900年日期系統。Microsoft Office Excel for the Macintosh默認的日期系統為1904年日期系統。如圖2-6所示，為兩個日期系統第一天和最後一天，以及其對應序列號的對照。

可以按照如下步驟來更改日期系統：打開Excel，單擊「 MicrosoftOffice按鈕」，選擇「Excel選項>高級>計算此工作簿時」。通過勾選「使用1904年日期系統」前面的複選框來修改日期系統，如圖2-7所示。單擊「確定」按鈕。如果選中複選框，則日期系統為1904年日期系統

提示：在講解本章內容時，選用的是1900年日期系統，所以沒有特殊說明的地方就都按照1900年日期系統來計算。
推薦閱讀：