學不完．教不停．用不盡

網友問到一個 Excel 的問題：如何將日期格式例如：1975/01/01(西元年)，改顯示為0640101(民國年)格式？

在下圖中，西元年格式以『/』做為年、月、日的分隔，轉換為民國年格式時，要去除『/』符號。

 

【公式設計與解析】

方法一：取出公式取出年、月、日來組合

儲存格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。

(2) SUBSTITUTE(第(1)式,"/","")

利用 SUBSTITUTE 函數將第(1)式結果中的『/』去除。

(3) RIGHT("0"&第(2)式,7)

由於得到的民國年可能是 2 碼或是 3 碼，所以先將『0』字元串接民國年，再由字串右側取 3 碼，即可將 2 碼的民國年之前加上一碼『0』。

【延伸閱讀】

* Excel-將文字日期轉換為數值日期

* Excel-列出指定日期和星期幾的全年日期

* Excel-依據不日期日期計算時間差(DATE,TIME,INT)

* Excel-比較二個日期

* Excel-依據不日期日期計算時間差(DATE,TIME,TEXT,MID)

* Excel-依日期的星期幾排序

* Excel-以不同樣式顯示日期中的月份

* Excel-依日期的年和月排序

* Excel-串接含有日期的多個儲存格

* Excel-產生不包含星期六、日的日期

* Excel-根據日期和班別進行雙條件查詢

* Excel-在統計圖中將數列依日期反轉顯示

【補充說明】

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

  ​​   ​