Excel 中大量的數據是日期相關的，例如，過去的銷量，員工考勤數據等等。我們經常需要處理日期，例如，得到兩個項目之間的天數，員工實際工作的天數等等。但是，在實際中，往往有很多學員或者客戶拿著這樣的問題來問我們，其中當然有大家不掌握的日期類函數，但是，很大一部分卻是由於對日期的不理解造成的。
我們今天就來介紹一下Excel中的日期相關的基本知識。至於那些強大的日期函數，我們在後面的文章中再介紹。
日期實際上就是數值！
在Excel中，日期實際上在內部存儲為數值。打開Excel，隨便在一個單元格中輸入一個日期，比如，2015-5-1，然後把該單元格格式修改為數值，就會看到該單元格的結果變成了一個數字：42125.00。這個數字就是日期“ 2015-5-1 ”在Excel內部的存儲值。這個數字代表了從1900-1-0開始，已經過了多少天了。同樣，如果你在單元格中輸入一個數字，然後把單元格格式修改為日期，就會看到，單元格中顯示了一個正常的日期。
在這裡要注意，數字0代表了1900-1-0。如果你在單元格中輸入0，然後修改單元格格式為日期，單元格中顯示的日期就是“ 1900-1-1 ”。
Excel 中任意的數值都可以轉化為日期，但是，反過來，不是任意的日期類型都可以轉換為數字。只有正常的日期可以通過修改單元格格式轉化為數值，但是錯誤的日期通過這個方式不能轉換為數值，你可以試試“ 2015-6-31 ”。這裡要提示你的是，如果你輸入“ 1900-1-0”這個日期，你會發現不能轉換為數值。
實際上，“ 1900-1-1 ”之前的日期都是錯誤的日期，因為Excel能支持的最早日期就是1900-1-1。（這些錯誤日期不能轉換為數值的原因是當你輸入錯誤日期時，Excel就把它當作了文本處理了。而如果是Excel能夠識別的正確日期，Excel會自動把它當作數值存儲）
一個小“ Bug ”
我們前面說的日期是以數值的形式存儲，數值代表了從1900-1-0後的第多少天。這個說法有一個小錯誤。如果你有萬年曆，實際上數一數從1900-1-0開始已經過了多少天，跟Excel中這個數值做對比，會發現比實際上多了1。
造成這個現象的原因是因為在Excel中1900年被當作了一個閏年，有1900-2-29這個日期。但是實際上，1900年並不是閏年。
這並不是一個無意中的bug，而是有意這麼做的。原因是為了與Lotus 123兼容。畢竟，在Excel之前，Lotus 123可是最流行的表格軟件。而且，我們離1900年很遠了，絕大部分數據都在1900-3-1之後，所以，這麼做的實際影響很小。
時間數據
Excel 中的日期和時間實際上是一種數據。前面說的日期代表了多少天。實際上這個數值還有小數部分。小數部分就代表了時間。
也就是1天24小時代表了1，0就是0點，上午6點，代表0.25，因為已經過了1天的1/4了。“ 2015-5-1 13:00 ”轉換為數值後，結果為42125.5416666667。
知道這些有什麼用？
我們理解了日期和時間在Excel中的存儲方式，可以幫助我們很簡單的處理數據。比如，如果我們有一個項目開始時間，和項目結束時間，想算一下項目一共進行了多少天，只要簡單的把兩個日期相減就可以了。
又或者，我們項目從2015-5-1開始，項目預計需要85天完成，那麼結束日期在幾月幾號呢，同樣只要在開始日期上加上85就行了。
為什麼這麼轉換的日期在有的Excel文件中是錯的呢？
有的朋友按照上面的處理，有時會得到錯誤的結果。這是為什麼呢？多數情況下，是因為實際上Excel有兩套日期系統。這兩套的處理方式是一樣的，不過一套就是從1900-1-0開始（就是我們上面講的），另外套台是從1904-1-0開始的。這個“ 1904系統”是為了兼容蘋果公司的Macintosh系統而開發的。每個Excel文件只能選擇一種日期系統，如果你的機器不是蘋果系統，應該多數都缺省支持1900系統。
當你拿到一個從蘋果系統來的Excel文件，其中的日期可能是1904開始的。你可以修改它。修改方法是（以Excel2013為例）:依次點擊“文件”，“選項”，“高級”，在大概後1/3處找到如下圖所示的選項，把√去掉就可以了。
輸入兩位數的年份
我們這裡有一個小困惑：如果我們在輸入年份的時候只輸入2位數的年份，Excel會怎麼做。例如，我們不輸入“ 2015-5-1 ”，而只輸入“ 15-5-1 ”，我們發現Excel自動的把日期變成了“ 2015-5-1 ”，那麼Excel是怎麼判斷年份的呢。
實際上，Excel有一個判斷的分界線，“ 30 ”，如果你輸入的年份小於30，那麼，Excel就會當作21世紀的年份，變成20XX，如果你輸入的年份大於等於30，那麼Excel就會當作20世紀的年份，變成19XX。
總結
當我們理解了Excel中的日期和時間數據是以數值格式的存儲的這個事實後，我們對Excel中與日期相關的數據處理會更靈活。這種靈活的處理當然離不開強大的日期函數。在後面的系列文章中，我們將為你介紹Excel中的日期函數。
沒有留言:
張貼留言