2016年12月21日 星期三

Excel-日期格式轉換(西元年轉民國年)(TEXT,SUBSTITUTE)

網友問到一個 Excel 的問題:如何將日期格式例如:1975/01/01(西元年),改顯示為0640101(民國年)格式?
在下圖中,西元年格式以『/』做為年、月、日的分隔,轉換為民國年格式時,要去除『/』符號。
Excel-日期格式轉換(西元年轉民國年)(TEXT,SUBSTITUTE)

【公式設計與解析】
方法一:取出公式取出年、月、日來組合
儲存格C2:=RIGHT("0"&(YEAR(A2)-1911),3)&RIGHT("0"&MONTH(A2),2)&
RIGHT("0"&DAY(A2),2)
複製儲存格C2,貼至儲存格C2:C17。
(1) RIGHT("0"&(YEAR(A2)-1911),3)
利用 YEAR 函數取儲存格A2中日期的『年』數,再減 1911,可得民國年數。由於得到的民國年可能是 2 碼或是 3 碼,所以先將『0』字元串接民國年,再由字串右側取 3 碼,即可將 2 碼的民國年之前加上一碼『0』。
(2) RIGHT("0"&MONTH(A2),2)
利用 MONTH 函數取儲存格A2中日期的『月』數。由於得到的月數可能是 1 碼或是 2 碼,所以先將『0』字元串接月數,再由字串右側取 2 碼,即可將 1 碼的月數之前加上一碼『0』。
(3) RIGHT("0"&DAY(A2),2)
利用 DAY 函數取儲存格A2中日期的『日』數。由於得到的日數可能是 1 碼或是 2 碼,所以先將『0』字元串接日數,再由字串右側取 2 碼,即可將 1 碼的日數之前加上一碼『0』。
(4) 最後,將第(1),(2),(3)式以『&』串接。

方法二:利用文字格式化取得年月日
儲存格C2:=RIGHT("0"&SUBSTITUTE(TEXT(A2,"[$-404]e/mm/dd;@"),"/",""),7)
複製儲存格C2,貼至儲存格C2:C17。
(1) TEXT(A2,"[$-404]e/mm/dd;@")
利用 TEXT 函數將儲存格A2的日期設定格式『[$-404]e/mm/dd;@』,可得民國年格式,例如:1975/01/01→64/01/01。
Excel-日期格式轉換(西元年轉民國年)(TEXT,SUBSTITUTE)
(2) SUBSTITUTE(第(1)式,"/","")
利用 SUBSTITUTE 函數將第(1)式結果中的『/』去除。
(3) RIGHT("0"&第(2)式,7)
由於得到的民國年可能是 2 碼或是 3 碼,所以先將『0』字元串接民國年,再由字串右側取 3 碼,即可將 2 碼的民國年之前加上一碼『0』。

【補充說明】
如果你只是要將西元年格式改成民國年格式,也可以在數值格式設定中選取[日期]類別,然後在[行事曆類型]下拉式清單中選取「中華民國曆」選項,即可得到民國年格式的日期。
image

沒有留言:

張貼留言

檢視其他文章

好康東東