日期時(shí)間對(duì)于我們現(xiàn)實(shí)的生活意味著什么？它是我們生活中點(diǎn)滴美好記憶，常?；匚?，還是痛苦過(guò)去，不堪回首，還是我們的身體被被毒藥氧氣一點(diǎn)點(diǎn)的被氧化的見(jiàn)證者，這些似乎是答案，卻有不太準(zhǔn)確，不過(guò)我可以負(fù)責(zé)任的告訴你，它在Excel中的的確確就是數(shù)字，只是用特殊的格式呈現(xiàn)而已。那它和數(shù)字之間的關(guān)系是遵守什么規(guī)則呢？他們又是怎么互相轉(zhuǎn)化的呢？帶著這些疑問(wèn)，開(kāi)始我們今天的內(nèi)容，不過(guò)在講這些問(wèn)題的之前，需要了解一下Excel的紀(jì)元時(shí)間是什么？它出現(xiàn)什么BUG卻又不加以修復(fù)呢？

紀(jì)元時(shí)間

 日期元年其實(shí)就是軟件支持最早的日期，這個(gè)也并非微軟的專利，linux系統(tǒng)的紀(jì)元時(shí)間就是1970年，因?yàn)槭墚?dāng)時(shí)硬件的限制，而系統(tǒng)的時(shí)間戳又是以秒為單位存儲(chǔ)的，換算下來(lái)也就夠存68.1年，根據(jù)最早出現(xiàn)計(jì)算機(jī)的年代就規(guī)定了1970年1月1日00:00:00 為L(zhǎng)inux的計(jì)算時(shí)間，而Excel則是以天為單位，同樣的存儲(chǔ)空間，它能存儲(chǔ)更長(zhǎng)時(shí)間日期，為了和當(dāng)時(shí)比較火的Lotus 1-2-3 電子表格軟件相互兼容，就將紀(jì)元時(shí)間定為1900年1月0日對(duì)應(yīng)數(shù)字0，而在MAC版的軟件中卻使用1904年1月0日為紀(jì)元時(shí)間，即對(duì)應(yīng)數(shù)字0。

為什么會(huì)采用兩個(gè)日期系統(tǒng)呢？因?yàn)長(zhǎng)otus 1-2-3在編程的時(shí)候?qū)?900年設(shè)定成閏年，事實(shí)上并不是，也導(dǎo)致現(xiàn)在的軟件中依然存在1900年2月29日這個(gè)現(xiàn)實(shí)中并不存在的日期，也是因?yàn)闅v史的遺留問(wèn)題導(dǎo)致1900年3月之前的返回返回星期天的有錯(cuò)誤，而Mac版正式發(fā)布的比較晚，為了規(guī)避這個(gè)錯(cuò)誤就采用1904年時(shí)間系統(tǒng)，直到2011版或2016版的Excel為了兼容window版的文件，默認(rèn)也使用1900的日期系統(tǒng)，如果你想使用1904的時(shí)間系統(tǒng)，具體操作如下圖：

MAC版Excel使用1904日期系統(tǒng)操作步驟

window系統(tǒng)的切換方法：【文件】->【選項(xiàng)】->高級(jí)選項(xiàng)卡下，你勾選使用1904日期系統(tǒng)（Y）（2016版）；

windows版Excel使用1904日期系統(tǒng)操作步驟

說(shuō)完了紀(jì)元時(shí)間的事，該聊聊日期，時(shí)間和數(shù)字之間的關(guān)系了，它們又遵循著什么規(guī)則來(lái)互相轉(zhuǎn)化呢？

規(guī)則

我們了解到Excel是用整數(shù)存儲(chǔ)正常日期并以天為單位，雖然可以延長(zhǎng)存儲(chǔ)的日期長(zhǎng)度，但也并不是無(wú)限的，它支持的最高日期為9999年12月31日，看著日期是不是有點(diǎn)眼熟啊，這不是一萬(wàn)年嗎？它換成數(shù)字是多少天呢？結(jié)果：2958465，也就是說(shuō)Excel支持0-2958465的數(shù)字轉(zhuǎn)化成日期，超出了這個(gè)范圍再以日期格式顯示就返回##，不論怎么拉寬單元格，會(huì)一直顯示#。

時(shí)間的規(guī)則比日期規(guī)則稍微復(fù)雜那么一丟丟，因?yàn)闀r(shí)間部分都是小數(shù)表示，1時(shí)：1/24,1分：1/(24*60),1秒：1/(24*60*60)=1/86400；那么問(wèn)題來(lái)了，我們?nèi)绻?jì)算時(shí)間的時(shí)候，需要精確小數(shù)點(diǎn)后多少位就可以？

我們根據(jù)上面的公式可以算出1秒=1/86400=1.15741E-05，公式變形一下1秒=1.157e-05*86400=1.157*0.864,從調(diào)整后的公式我們簡(jiǎn)單推理出，小數(shù)精度保留6小數(shù)就足以滿足 時(shí)間秒的需求了，保留位越多就會(huì)成為計(jì)算時(shí)的負(fù)擔(dān)，上面的公式就變?yōu)?秒=1.2e-05=0.000012,示意圖如下：

通過(guò)秒的轉(zhuǎn)化公式確認(rèn)計(jì)算需要保留的精確小數(shù)位

在Excel中除了這些計(jì)算的規(guī)則外，如果想讓字符串設(shè)置日期格式轉(zhuǎn)化成日期還需滿足這些規(guī)則：

1）數(shù)字串的連接符必須為“-”或“/”,默認(rèn)顯示格式為系統(tǒng)日期顯示格式，通常為2019/7/12；

注：小數(shù)點(diǎn)“.”并不是合法的日期連接符，因?yàn)樗鼤?huì)和正常帶小數(shù)的數(shù)字引起混亂，這也是我們?cè)佥斎肴掌跁r(shí)應(yīng)該注意的地方。

2）在輸入日期格式，需要注意幾個(gè)規(guī)則：年份，可輸入1位，2位或4位，輸入1位和2位數(shù)字時(shí)會(huì)自動(dòng)增加前綴2000或20形成完整的年份數(shù)字，四位數(shù)字的有效區(qū)間為1900-9999不在范圍的內(nèi)的都不能識(shí)別為日期格式；月份：1-12的數(shù)字，日期：根據(jù)月份和年是不是閏年規(guī)則，輸入日期必須為小日期，否則也不能識(shí)別為日期格式；如果我們省略年份的輸入，只輸入月份和日期，則會(huì)識(shí)別為系統(tǒng)當(dāng)前年份加輸入的月份和日，比如：09/03則會(huì)識(shí)別為2019/9/3;

3)時(shí)間方面，連接符為“:”(英文),想識(shí)別為時(shí)間格式需輸入遵守時(shí)間的數(shù)字規(guī)則：小時(shí)：分：秒；

時(shí)間格式對(duì)數(shù)字要求需要符合時(shí)間的規(guī)則，比如小時(shí)為0-23 分和秒則：0-59，除此之外，Excel還支持一種情況的發(fā)生，如果其中一個(gè)時(shí)或分或秒出現(xiàn)一樣，都是可以在日期基數(shù)為1900年1月0日基礎(chǔ)上進(jìn)行計(jì)算轉(zhuǎn)化成正常日期時(shí)間格式；比如輸入：8:25:358,回車(chē)后：8:30:58（默認(rèn)為1900年1月0日且不顯示）;輸入60:34:30回車(chē)轉(zhuǎn)化為1900/1/2 12:34:32;如果出現(xiàn)2個(gè)或以上異常數(shù)字，則不能識(shí)別為日期。

日期時(shí)間自動(dòng)轉(zhuǎn)換效果圖

聊完日期的“潛”規(guī)則后，我們最后來(lái)編寫(xiě)一下小數(shù)轉(zhuǎn)換時(shí)間的公式吧

小數(shù)轉(zhuǎn)時(shí)間公式

小數(shù)轉(zhuǎn)時(shí)間有兩種思路，一種截取整數(shù)法：

1.先將小數(shù)與24相乘，截取整數(shù)部分，

2.取的上一步乘積的小數(shù)部分與60相乘，截取整數(shù)部分，

3.再將上一步乘積的小數(shù)部分與60相乘，用round函數(shù)四舍五入取整得出秒數(shù)；

截取整數(shù)法的公式詳解圖

一種取余法：

1.可以同上，

2.與86400相乘，乘積取余3600（求除不到1小時(shí)的總秒數(shù)），然后除60，取整求除分鐘數(shù)

3.與86400相乘，乘積取余60，用round函數(shù)四舍五入取整就可以求出秒數(shù)。

兩種方法需要用的函數(shù)有：mod(取余函數(shù)),round（四舍五入取整函數(shù)），TRUNC(取整函數(shù)）

取余法公式詳解圖

這兩種求解的方法中，值的注意的地方，公式的trunc函數(shù)并不能使用int函數(shù)代替，因?yàn)槲覀冊(cè)谟?jì)算時(shí)間的時(shí)候，我們有可能會(huì)得到負(fù)數(shù)，用int的取整負(fù)數(shù)會(huì)導(dǎo)致其絕對(duì)值變大，這樣得出來(lái)的時(shí)間上就有出現(xiàn)錯(cuò)誤。Excel中不是提供轉(zhuǎn)化方法了嗎？為什么我們還要自己制作公式呢？因?yàn)槲覀冊(cè)谌粘Ｊ褂弥?，比如?jì)算加班時(shí)間，累計(jì)加班時(shí)長(zhǎng)或其他涉及到時(shí)間計(jì)算的地方，我們就可以用N函數(shù)來(lái)講時(shí)間數(shù)列轉(zhuǎn)化成數(shù)字，計(jì)算完成后，在用用公式你逆轉(zhuǎn)成時(shí)間就可以，即便我們沒(méi)有其他的任何日期相關(guān)的函數(shù)，我們依然可以玩轉(zhuǎn)的時(shí)間日期，是不是很酷呢！

今天的文章我們先寫(xiě)到這里的，希望能從閱讀中有所收獲，如果你遇到辦公或電腦上的問(wèn)題，可以給我留言或私信我，我看到后，會(huì)第一時(shí)間回復(fù)你！在工作和學(xué)習(xí)的路上，你并不孤單，我們可以結(jié)伴而行！