2015年8月31日 星期一

Excel-轉換文字日期格式為數值日期格式(DATE,MID,TEXT,數值格式設定)

網友們高頻率會詢問到在 Excel 中如何來轉換日期格式,例如:1040702 轉換為 104/07/02。這類的問題該如何處理?
在 Excel 中,日期是一個數值,1040702 並不是一個標準的日期數值,如果拿來轉換後,若是以顯示的角度來看(格式),轉換後可以是數字,也可以是文字。而數字可以用來執行日期的運算。

【公式設計與解析】
(1)轉換為數值格式
儲存格C2:=DATE(MID(A2,1,3)+1911,MID(A2,4,2),MID(A2,6,2))
MID(A2,1,3):利用 MID 函數,由儲存格A2中取出1~3碼當為日期的年,但是日期是西元年表示,所以要在加上 1911。
MID(A2,4,2):利用 MID 函數,由儲存格A2中取出4~5碼當為日期的月。
MID(A2,6,2):利用 MID 函數,由儲存格A2中取出6~7碼當為日期的日。
將以上的三個值利用 DATE 函數轉換為一個日期,但得到的是一個西元年格式。
接著,設定儲存格A2的儲存格數值格式,先選取「日期」類別,再於行事曆類型中選取「中華民國曆」,然後選取「101/3/14」項。目前的結果是將 1040702 轉換為 104/7/2。
接下來,再於類別中選取「自訂」,將 [$-404]e/m/d;@ 修改為 [$-404]e/mm/dd;@。(mm 表示月要以 2 碼表示,dd 表示日要以 2 碼表示。)
複製儲存格C2,貼至儲存格C2:C16。

(2) 轉換為文字格式
儲存格C2:=MID(A2,1,3) & "/" & MID(A2,4,2) & "/" & MID(A2,6,2)
轉換結果為文字格式,所以如果拿來運算,會出現 #VALUE! 錯誤訊息。

(3) 轉換為文字格式
儲存格C2:=TEXT(DATE(MID(A2,1,3)+1911,MID(A2,4,2),MID(A2,6,2)),
"[$-404]e/mm/dd;@")
綜合 (1) 的觀念結合到公式中,透過 TEXT 函數來設定格式,其結果仍為文字格式。

沒有留言:

張貼留言

好康東東