錄入日期沒加斜線，如何用Excel把它轉換成加斜線的日期？

01-29

有個學員遇上一個問題向我求助：

我在輸入表格數據時，忘記給日期加斜線了，現在Excel不能識別，我也沒法計算工齡了，求谷月姐幫幫我啊！

在Excel中，輸入日期時，一定要把年月日分隔開來，例如2016-08-17或2016/8/17，結果這位學員輸入了20160817，導致Excel無法識別，如圖1。（還好他輸入了20160817，每個月份都是兩位，還容易識別，如果他輸入了2016817，那就麻煩了）。

↑↑↑圖1↑↑↑

解決這個問題，我也走了一點彎路，為了避免大家重蹈覆轍，我就把解決問題的整個過程都寫出來，與讀者一起討論。

一開始，我想用TEXT函數解決問題，TEXT函數可以轉換數字格式。於是，我在A2單元格中輸入公式「=--TEXT(A1,"yyyy/mm/dd")」，結果Excel提示出錯。

為什麼會出錯呢？我以前都是用TEXT函數處理數字格式問題，為什麼這一次就報錯呢？我突然想起「1900日期系統」，立馬恍然大悟：

TEXT只能用於處理數字（包括文本形式的數字），不能用於處理文本，所以首先要檢查A1單元格的格式是不是「數字」。
在Excel內部，是把日期存儲為序列號的，序列號是數字。1900/1/1對應數字1，1900/1/2對應數字2，…，9999/12/31對應2958465。在顯示上，則是把序列號數字表示為日期，例如1表示1900/1/1，2表示1900/1/2，…，2954865表示為9999/12/31。從2958466開始，Excel就無法把數字表示為日期了，如果強行把大於2954865的數字表示日期，Excel會報錯。
我用的公式「=--TEXT(A1,"yyyy/mm/dd")」，實際上是把A1中的數字表示為日期。A1中的數字20160817遠遠大於2954865，所以Excel會報錯。

一計不成，又生一計，我們可以用間接法解決：首先，用MID函數和&運算符，把數字20160817的數字轉換成文本「2016/08/17」；然後，再用DATEVALUE函數把文本「2016/08/17」轉換成對應日期的序列號；最後，再把這個序列號強行指定為日期格式即可。

操作步驟如下：

在C1單元格中輸入公式「=MID(A1,1,4)&"/"&MID(A1,5,2)&"/"&MID(A1,7,2)」。MID(A1,1,4)的作用是提取字元，A1代表從A1中提取，1代表從第1個字元開始，4代表連續提取4個字元，就是「2016」。MID(A1,5,2)代表從第5個字元開始連續提取2個字元，就是「08」。同理，MID(A1,7,2)是「17」。&的作用是把它前後的字元連在一起。這個公式的意思就是把2016、/、08、/、17連在一起，形成「2016/08/17」。如圖2所示。
↓↓↓圖2↓↓↓
這時候在C1單元格中生成了文本「2016/08/17」，然後，我們再在D1單元格中輸入「=DATEVALUE(C1)"。DATEVALUE函數的用途是把一組字元轉換成對應的日期序列號，這組字元必須以日期的格式輸入。得到42599，如圖3所示。
↓↓↓圖3↓↓↓
然後再把整個D列的格式設為「自定義」，格式代碼自己輸入「yyyy/mm/dd」。這樣，數字42599就變成了日期2016/08/17，如圖4所示。當然，你也可以直接設為「日期」。
↓↓↓圖4↓↓↓

問題解決。

然而，學員並不滿意，Ta說：

唉呀，多了個輔助列，不好看呀，谷月姐你能不能用一個公式解決問題啊？不要輔助列。

不要輔助列，也很簡單，只要我們把公式整合一下，就能消去輔助列。在D1單元格中，用C1單元格中的公式代替「C1」，即「=DATEVALUE(MID(A1,1,4)&"/"&MID(A1,5,2)&"/"&MID(A1,7,2))」，最後刪除原來的B、C兩列即可。如圖5所示。

↑↑↑圖5↑↑↑

關於作者

Office專家，領津Office達人，微軟認證專家，WPS稻殼兒簽約PPT設計師。