excel按身份證年齡排序「excel更改日期格式公式」

踩坑千千萬，一切宗旨都是為了讓爹們滿意。每天從天而降的線上反饋，沒有一點點防備，也沒有一絲顧慮，它就這樣出現，在我的世界裡…

這日又是平凡的、虛心接受用戶反饋的一天。領導熟練地從SqlServer資料庫中導出一份Excel文件發給了我，並吩咐我排查問題的原因。

面對龐大的數據表格，我快速瀏覽了一遍，確認首先需要處理的是client_time一列，內容是Unix時間戳格式，很明顯，Excel不支持Unix時間戳。

來看什麼是Unix時間戳Unix時間戳（Unix timestamp），或稱Unix時間（Unix time）、POSIX時間（POSIX time），是一種時間表達方式，定義為從格林威治時間1970年01月01日00時00分00秒起至現在的總秒數（或總毫秒數）。Unix時間戳不僅被使用在Unix系統、類Unix系統中，也在許多其他操作系統中被廣泛採用。

這好說，我上來就【右鍵-單元格格式-選時間分類-確定】走你~然後，我看到的結果是這樣的：

大意了，看來此法不通。

這有何難？用站長工具轉換一下不就好了！當我轉換到第3條的時候，我意識到這將是個浩大的工程…

站長工具是不支持批量轉換的，這幾千條數據，如此搞下去，我怕不是有個大病。

不懂就問，有一波人說，可以寫個程序用編程語言轉換，這…肯定可行，但時間成本有點高。

繼續不懂就問，很快我找到了一種更快速有效的解決方法。

接下來，讓我做個小結以備忘：

113位時間戳轉日期時間（毫秒）

首先

假設A2單元格內容為13位的時間戳，再選中B2單元格，在公式框中輸入

=TEXT((A2/1000+8*3600)/86400+70*365+19,”yyyy/mm/dd hh:mm:ss.000″)

按Enter鍵確認，此時能看到，B2單元格顯示了轉換後的日期時間格式2021/07/03 18:43:42.573。

接下來，選中B2單元格，下拉應用公式，整列搞定。

210位時間戳轉日期時間（秒）

10位時間戳的轉換方法與13位時間戳同理。唯一不同的是，此時的公式為

=TEXT((A2+8*3600)/86400+70*365+19,”yyyy/mm/dd hh:mm:ss”)

可能有人會問了：能不能將日期時間轉換成Unix時間戳呢？既然我的標題上說的是「互換」，答案當然是能的了。

3日期時間轉10位時間戳

話不多說，直接上公式

= INT((A2-70*365-19)*86400-8*3600)

其實就是把公式反過來，日期時間轉13位時間戳， 我就不贅述了。

學習是一個舉一反三的過程，如果只需解一時燃眉之急，記住以上公式也夠用。但如果想知其然且知其所以然，就得了解公式背後的原理了。

以10位時間戳（秒）轉日期為例。公式：

=TEXT((A2+8*3600)/86400+70*365+19,”yyyy/mm/dd hh:mm:ss”)

A2+8*3600：當前時區的時間（秒）(A2+8*3600)/86400：將秒轉換為天(A2+8*3600)/86400+70*365：加上1900年至1970年的70年(A2+8*3600)/86400+70*365+19：加上閏年多出來的天數19

Excel的日期實際上是序列值，它以1900-1-1=1為始，每過一天序列值加1；Unix時間戳是從1970-1-1 0:00:00開始到現在的秒數。

細心的人可能會發現，1900年至1970年共17個閏年，如果考慮到Excel將1900-1-1當作1，那麼公式最後應該加18才對，為什麼要加19呢？這是Excel的一個bug–把1900年也當作閏年了。

Tips：另有一點需注意，在Excel的【文件->工具->選項->重新計算】中，有個”使用1904日期系統”選項，如果勾選此選項，上面的公式應將70改為66，即:=TEXT((A2+8*3600)/86400+66*365+19,”yyyy/mm/dd hh:mm:ss”)

到這裡，完整的備註我就寫完了。

外面青色的天空，陰沉又安靜。我在等待一場夏日裡的傾盆大雨，和一個新的開始。