錄入日期沒加斜線,如何用Excel把它轉換成加斜線的日期?
有個學員遇上一個問題向我求助:
我在輸入表格數據時,忘記給日期加斜線了,現在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所示。
關於作者
Office專家,領津Office達人,微軟認證專家,WPS稻殼兒簽約PPT設計師。
推薦閱讀:
※萌新學Power?BI?-?動人的報表從美美的切片器開始
※VLOOKUP函數與矩陣法風險分類
※挖掘R包關係網
※神馬?excel也能做項目管理?還是智能的!
※Excel VBA入門(四)流程式控制制2-循環控制
TAG:MicrosoftExcel | Excel公式 |