介紹如何使用 Excel 的 SUBSTITUTE、LEFT、MID、RIGHT 函數自動將民國日期轉換為西元日期。
民國日期轉西元日期（格式固定）
假設我們現在有一些民國的日期資料，而其日期格式都很固定，年份是三位數，月與日都是兩位數，中間以句點分隔。
像這樣非常固定的民國日期資料，若想要轉換為西元年的日期，只要使用 LEFT、MID 與 RIGHT 函數，根據資料的位置將年、月、日取出後，將年份加上 1911 轉為西元年，再使用 DATE 建立日期資料即可。
=DATE(LEFT(A2,3)+1911,MID(A2,5,2),RIGHT(A2,2))
民國日期轉西元日期（格式不固定）
假設原始的民國日期資料格式不固定，也就是說年、月、日的位數不是固定的，從一位數到三位數都有，這種情況就會比較複雜。
遇到這種情況時，可以先將問題拆解成兩部分，首先將原始民國日期中的年月日分別取出，再將其轉換為西元日期。
這裡我們先在表格中增加轉換用的資料欄位，然後先將原始日期中的分隔符號（在這個例子中就是句點）取代為三個句點，目的在於讓我們在取出資料時多一些緩衝空間。
=SUBSTITUTE(A2,".","...")
使用 LEFT 函數將年份取出，因為年份有可能是一位數到三位數，所以這裡我們取其上限值三位數，將年份資料抓取出來，而如果遇到年份資料只有一位數或兩位數的時候，後方就會包含多餘的句點，所以最後再以 SUBSTITUTE 將句點去除，就可以得到乾淨的年份資料。
=SUBSTITUTE(LEFT(B2,3),".","")
在抓取月份時也是類似的概念，將所有可能的長度算出來之後，抓取最長的部分，然後再將句點去除。
=SUBSTITUTE(MID(B2,5,4),".","")
最後以相同的概念使用 RIGHT 函數從右方抓取日期資料。
=SUBSTITUTE(RIGHT(B2,2),".","")
將年、月、日都取出之後，將年份加上 1911，再以 DATE 函數建立日期資料即可。
=DATE(C2+1911,D2,E2)
這種方式需要很注意資料的長度變化與抓取的位置，如果感覺不好計算的話，可將分隔符號取代為長度更長一點的連續句點（例如二十個句點），算起來會更輕鬆，也不容易出錯。
發佈留言