期時間對于我們現實的生活意味著什么？它是我們生活中點滴美好記憶，常常回味，還是痛苦過去，不堪回首，還是我們的身體被被毒藥氧氣一點點的被氧化的見證者，這些似乎是答案，卻有不太準確，不過我可以負責任的告訴你，它在Excel中的的確確就是數字，只是用特殊的格式呈現而已。那它和數字之間的關系是遵守什么規則呢？他們又是怎么互相轉化的呢？帶著這些疑問，開始我們今天的內容，不過在講這些問題的之前，需要了解一下Excel的紀元時間是什么？它出現什么BUG卻又不加以修復呢？
紀元時間
日期元年其實就是軟件支持最早的日期，這個也并非微軟的專利，linux系統的紀元時間就是1970年，因為受當時硬件的限制，而系統的時間戳又是以秒為單位存儲的，換算下來也就夠存68.1年，根據最早出現計算機的年代就規定了1970年1月1日00:00:00 為Linux的計算時間，而Excel則是以天為單位，同樣的存儲空間，它能存儲更長時間日期，為了和當時比較火的Lotus 1-2-3 電子表格軟件相互兼容，就將紀元時間定為1900年1月0日對應數字0，而在MAC版的軟件中卻使用1904年1月0日為紀元時間，即對應數字0。
為什么會采用兩個日期系統呢？因為Lotus 1-2-3在編程的時候將1900年設定成閏年，事實上并不是，也導致現在的軟件中依然存在1900年2月29日這個現實中并不存在的日期，也是因為歷史的遺留問題導致1900年3月之前的返回返回星期天的有錯誤，而Mac版正式發布的比較晚，為了規避這個錯誤就采用1904年時間系統，直到2011版或2016版的Excel為了兼容window版的文件，默認也使用1900的日期系統，如果你想使用1904的時間系統，具體操作如下圖：
MAC版Excel使用1904日期系統操作步驟
???window系統的切換方法：【文件】->【選項】->高級選項卡下，你勾選使用1904日期系統（Y）（2016版）；
windows版Excel使用1904日期系統操作步驟
?說完了紀元時間的事，該聊聊日期，時間和數字之間的關系了，它們又遵循著什么規則來互相轉化呢？
規則
我們了解到Excel是用整數存儲正常日期并以天為單位，雖然可以延長存儲的日期長度，但也并不是無限的，它支持的最高日期為9999年12月31日，看著日期是不是有點眼熟啊，這不是一萬年嗎？它換成數字是多少天呢？結果：2958465，也就是說Excel支持0-2958465的數字轉化成日期，超出了這個范圍再以日期格式顯示就返回##，不論怎么拉寬單元格，會一直顯示#。
時間的規則比日期規則稍微復雜那么一丟丟，因為時間部分都是小數表示，1時：1/24,1分：1/(24*60),1秒：1/(24*60*60)=1/86400；那么問題來了，我們如果計算時間的時候，需要精確小數點后多少位就可以？
我們根據上面的公式可以算出1秒=1/86400=1.15741E-05，公式變形一下1秒=1.157e-05*86400=1.157*0.864,從調整后的公式我們簡單推理出，小數精度保留6小數就足以滿足 時間秒的需求了，保留位越多就會成為計算時的負擔，上面的公式就變為1秒=1.2e-05=0.000012,示意圖如下：
通過秒的轉化公式確認計算需要保留的精確小數位
在Excel中除了這些計算的規則外，如果想讓字符串設置日期格式轉化成日期還需滿足這些規則：
1）數字串的連接符必須為“-”或“/”,默認顯示格式為系統日期顯示格式，通常為2019/7/12；
注：小數點“.”并不是合法的日期連接符，因為它會和正常帶小數的數字引起混亂，這也是我們再輸入日期時應該注意的地方。
2）在輸入日期格式，需要注意幾個規則：年份，可輸入1位，2位或4位，輸入1位和2位數字時會自動增加前綴2000或20形成完整的年份數字，四位數字的有效區間為1900-9999不在范圍的內的都不能識別為日期格式；月份：1-12的數字，日期：根據月份和年是不是閏年規則，輸入日期必須為小日期，否則也不能識別為日期格式；如果我們省略年份的輸入，只輸入月份和日期，則會識別為系統當前年份加輸入的月份和日，比如：09/03則會識別為2019/9/3;
3)時間方面，連接符為“:”(英文),想識別為時間格式需輸入遵守時間的數字規則：小時：分：秒；
時間格式對數字要求需要符合時間的規則，比如小時為0-23 分和秒則：0-59，除此之外，Excel還支持一種情況的發生，如果其中一個時或分或秒出現一樣，都是可以在日期基數為1900年1月0日基礎上進行計算轉化成正常日期時間格式；比如輸入：8:25:358,回車后：8:30:58（默認為1900年1月0日且不顯示）;輸入60:34:30回車轉化為1900/1/2 12:34:32;如果出現2個或以上異常數字，則不能識別為日期。
日期時間自動轉換效果圖
?聊完日期的“潛”規則后，我們最后來編寫一下小數轉換時間的公式吧
小數轉時間公式
小數轉時間有兩種思路，一種截取整數法：
1.先將小數與24相乘，截取整數部分，
2.取的上一步乘積的小數部分與60相乘，截取整數部分，
3.再將上一步乘積的小數部分與60相乘，用round函數四舍五入取整得出秒數；
截取整數法的公式詳解圖
一種取余法：
1.可以同上，
2.與86400相乘，乘積取余3600（求除不到1小時的總秒數），然后除60，取整求除分鐘數
3.與86400相乘，乘積取余60，用round函數四舍五入取整就可以求出秒數。
兩種方法需要用的函數有：mod(取余函數),round（四舍五入取整函數），TRUNC(取整函數）
取余法公式詳解圖
這兩種求解的方法中，值的注意的地方，公式的trunc函數并不能使用int函數代替，因為我們在計算時間的時候，我們有可能會得到負數，用int的取整負數會導致其絕對值變大，這樣得出來的時間上就有出現錯誤。Excel中不是提供轉化方法了嗎？為什么我們還要自己制作公式呢？因為我們在日常使用中，比如計算加班時間，累計加班時長或其他涉及到時間計算的地方，我們就可以用N函數來講時間數列轉化成數字，計算完成后，在用用公式你逆轉成時間就可以，即便我們沒有其他的任何日期相關的函數，我們依然可以玩轉的時間日期，是不是很酷呢！
今天的文章我們先寫到這里的，希望能從閱讀中有所收獲，如果你遇到辦公或電腦上的問題，可以給我留言或私信我，我看到后，會第一時間回復你！在工作和學習的路上，你并不孤單，我們可以結伴而行！
據透視表：按不同日期格式匯總數據。
有這樣一份表格，老板要求按照不同的區域去匯總一下每個品類的金額。如果像這樣幾千行的表格用篩選就很麻煩，可以用透視表來完成。
·點擊任意一個單元格找到上方的插入，點擊數據透視表。單元格區域默認的就是整個表格，透視表放置的位置根據自己的需求可以選擇新工作表也可以選擇現有工作表，點擊確定，這樣就出現了一個數據透視表的雛形。
·這里想要去匯總哪一類數據就可以去拖拽哪一類的字段。比如說想要去看不同區域，那就將區域拖到行；想要按照產品類別拖到列，匯總的數據是金額就將金額拖到值這里。如果想要看時間那就將訂購日期也放到行的下面，這樣就出現了這樣的匯總表格。此時顯示的是每一天不同品類的匯總情況。
·如果還想要按照月份、年份、季度等等來去匯總，可以去修改日期格式。點擊任意一個單元格，鼠標右鍵出現一個組合，在這里可以去修改日期的格式。比如想要按照這個季度就點季度點擊確定，這樣每一天的日期就匯總成了季度。
·如果這是不同年份的還可以點擊右鍵，點擊組合，再添加一份的匯總，這樣點擊確定就是不同年份按季度來匯總的表格，是不是非常方便。
想學更多辦公技巧點擊下方鏈接解鎖。