贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

廣告贊助

網友問到一個 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

創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(3) 人氣()


留言列表 (3)

發表留言
  • 南島阿煒
  • 看到圖片上的確定差一點竟按下去了,感謝無私的教學分享。
  • Ian
  • 老師您好:
    我有一個關於以經緯度查找最近點的問題,A欄 路徑名稱,B欄 緯度,C欄 經度,D欄 編號,查找的規則是: 路徑名稱必須相符後,再從中找出最近點的編號。懇請您賜較。謝謝您!
  • Qoo
  • =YEAR(A2)-1911&"/"&MONTH(A2)&"/"&DAY(A2)
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼