2015年2月6日 星期五

Excel-民國年轉西元年(DATE,MID,TEXT)

有網友想要將在 Excel 資料表中的兩種民國年的表示法,轉換為西元年的表示法,該如何處理呢?(參考下圖)

觀察這兩種民國年的表示法,對 Excel 而言,其被認為二種「字串」,而非日期。在 Excel 中日期是一種數值,同一個日期不管以何種格式顯示,其背後儲存格的數值都是相同的。

儲存格B2:

=TEXT(DATE(MID(A2,1,3)+1911,MID(A2,5,2),MID(A2,8,2)),"yyyy/mm/dd")

MID(A3,1,3)+1911:取出儲存格A2日期字串的1-3碼再加1911,當為「年」。

MID(A2,5,2):取出儲存格A2日期字串的5-6碼,當為「月」。

MID(A2,8,2):取出儲存格A2日期字串的8-9碼,當為「日」。

將上述的「年、月、日」代入 DATE 函數,轉換為 Excel 可接受的日期格式(其為一個數字)。

再藉由 TEXT 函數轉換為 yyyy/mm/dd 格式,即「年四碼/月二碼/日二碼」的格式。

複製儲存格B2,貼至儲存格B2:B23。

同理,

儲存格E2:

=TEXT(DATE(MID(D2,1,3)+1911,MID(D2,5,2),MID(D2,8,2)),"yyyy/mm/dd")

 

【同場加映】

如果你的民國年是如下圖中字數不規則,則可以參考另一篇文章的說明:

http://isvincent.pixnet.net/blog/post/42874681

儲存格B2:=TEXT(DATE(LEFT(A2,FIND(".",A2)-1)+1911,MID(A2,FIND(".",A2)+1,
FIND(".",A2,FIND(".",A2)+1)-FIND(".",A2)-1),RIGHT(A2,LEN(A2)-FIND(".",A2,
FIND(".",A2)+1))),"yyyy/mm/dd")

image

沒有留言:

張貼留言

好康東東