2016年3月31日 星期四

Excel-民國年格式轉換為西元年格式(RIGHT,MID,LEFT,TEXT,DATE)

網友問到:如何在 Excel 工作表裡將一個資料清單中為民國年格式的日期轉換為西元年格式?
參考下圖左為民國年格式,轉換為西元年格式,如下圖右。
Excel-民國年格式轉換為西元年格式(RIGHT,MID,LEFT,TEXT,DATE)
【公式設計與解析】
藉著例子來練習:RIGHT、MID、LEFT、DATE、TEXT函數。
儲存格C2:=TEXT(DATE(LEFT(RIGHT("0"&A2,7),3)+1911,MID(
RIGHT("0"&A2,7),4,2),RIGHT(RIGHT("0"&A2,7),2)),"yyyy/mm/dd")
複製儲存格C2,往下各列貼上。
(1) RIGHT("0"&A2,7)
由於民國年在 100 年以前為 2 碼,100 年以後為3 碼,所以儲存格A2可能為 6 碼,也可能為 7 碼。RIGHT("0"&A2,7)可以保證取得 7 碼,若原先為 6 碼者,則第一碼為 0。
(2) LEFT(RIGHT("0"&A2,7),3)+1911
取得左邊 3 碼或 2 碼的民國年份,再加上 1911 轉換為西元年份。
(3) MID(RIGHT("0"&A2,7),4,2)
取得中間 2 碼為月份。
(4) RIGHT(RIGHT("0"&A2,7),2)
取得右邊 2 碼為日份。
(5) DATE(LEFT(第(1)式,第(2)式,第(3)式)
將取得的年、月、日轉換為日期格式(西元年)
(6) TEXT(DATE(LEFT(第(1)式,第(2)式,第(3)式),"yyyy/mm/dd")
將日期格式轉換為 yyyy/mm/dd,西元年 4 碼+月份 2 碼+日數 2 碼。

沒有留言:

張貼留言

檢視其他文章

好康東東