民國年在A1, 將民國年轉成西元年後再儲存至A2
EX : A1 民國年為831112 ,結果在A2 為 19941112
在 Excel 中日期是一種數值,同一個日期不管以何種格式顯示,其背後儲存格的數值都是相同的。
所以必須先確定A1的格式是文字格式
如果A1不是文字格式,我們我們先利用TEXT(value,格式)將數字轉成文字
再用利用下面的公式將民國年轉成西元年
(1)A1如為數字格式,A2利用=TEXT(A1,"yymmdd")轉成831112文字格式
並且A2儲存格要利用格式將公式移除僅儲存值而且
(2)儲存格A2:
=TEXT(DATE(MID(A2,1,2)+1911,MID(A2,3,2),MID(A2,5,2)),"yyyymmdd")
MID(A2,1,3)+1911:取出儲存格A1日期字串的1-2碼再加1911,當為「年」。
MID(A2,3,2):取出儲存格A1日期字串的3-碼,當為「月」。
MID(A2,5,2):取出儲存格A1日期字串的5-6碼,當為「日」。
將上述的「年、月、日」代入 DATE 函數,轉換為 Excel 可接受的日期格式(其為一個數字)。
再藉由 TEXT 函數轉換為 yyyymmdd 格式,即「年四碼月二碼日二碼」的格式。
常會在資料整併上碰到同時存在民國年及西元年的狀況,這樣不僅檢視不易,亦無法做相差天數的計算,以下針對年份轉換及星期呈現為大家說明
一、西元年→民國年
=TEXT(B2,“yyyy“)-1911&“/“&TEXT(B2,“m/d“)
此公式代表將B2這個儲存格的西元年減去1911,中間加上“/“區隔,最後顯示月份與日期,結果便為109/3/1,而若想改以1090301呈現,則調整公式輸入
=TEXT(B2,“yyyy“)-1911&TEXT(B2,“mmdd“) 即可
二、民國年→西元年
=DATE(MID(C2,1,3)+1911,MID(C2,5,1),MID(C2,7,2))
代表從B2儲存格中,第1個字形(含)開始的3個字形加上1911,第5個字形(含)開始的1個字形、第7個字形(含)開始的2個字形則不動,要注意的是儲存格中的“/“也都算1個字形,所以如果原資料呈現的是“109/03/06“,公式就須改為
=DATE(MID(C2,1,3)+1911,MID(C2,5,2),MID(C2,8,2))
三、轉換為星期
=CHOOSE(WEEKDAY(G12,1),“週日“,“週一“,“週二“,“週三“,“週四“,“週五“,“週六“)
要注意的是weekday裡的函數1,在Excel裡是有對應的星期順序的
所以今天如果把函數改為2,那麼公式就要改為=CHOOSE(WEEKDAY(G12,2),“週一“,“週二“,“週三“,“週四“,“週五“,“週六“,“週日“),不然呈現的星期就會是錯誤的
另外” “中的內容也可任意變更,想改成星期O、禮拜O都可以唷
相關文章:
Excel|取代與變換文字-replace 與 substitute
Post Views: 8,466
Dear All,
我是一名稽核員。
之前公司要求將出生日期從「80.02.03」 轉換成「1991/02/03」。
民國年 & 西元年的出生日期數值的轉換。真的令人很頭痛。
練習檔: 點擊連結。
不過,找到方法轉換了。 完成如下:
認識函數:
> TEXT: 將選擇數字的格式,轉換成文字。
> DATE: 選擇數字,然後按照yyyy/mm/dd 排序。 如: =date(1921,3,2) → 得:1921/03/02
> MID: 7. EXCEL 教學:擷取、合併文字、分割 不用自己Key|LEFT / RIGHT / CONCATENATE (&)/MID。
教學1: 民國年 → 西元年。
> B2欄公式:=TEXT(DATE(MID(A2,1,2)+1911,MID(A2,4,2),MID(A2,7,2)),"yyyy/mm/dd")
> 先看這個: DATE(MID(A2,1,2)+1911,MID(A2,4,2),MID(A2,7,2))
1-1: MID(A2,1,2)+1911: 擷取A2欄位中,第1個字開始,共2個字。然後加上1911,即為「年」。
1-2: MID(A2,4,2): 擷取A2欄位中,第4個字元開始,共2個字元,即為「月」。
1-3: MID(A2,7,2): 擷取A2欄位中,第7個字元開始,共2個字元,即為「日」。
> 再利用TEXT函數將數值轉成 yyyy/mm/dd 格式,即「年(4碼)/月(2碼)/日(2碼)」的文字格式。
教學2: 西元年 → 民國年。
> 這次用到LEFT、RIGHT函數。
> C2 欄公式:=TEXT(DATE(LEFT(B2,4)-1911,MID(B2,6,2),RIGHT(B2,1)),"yy.mm.dd")
> 先看這個: DATE(LEFT(B2,4)-1911,MID(B2,6,2),RIGHT(B2,1))
2-1: LEFT(B2,4)-1911: 擷取B2欄位,左起數共4個字。然後減去1911,即為「年」。
2-2: MID(B2,6,2): 擷取B2欄位中,第6個字元開始,共2個字元,即為「月」。
2-3: RIGHT(B2,1): 擷取B2欄位中,右起數共2個字。,即為「日」。
> 再利用TEXT函數將數值轉成 yy.mm.dd 格式,即「年(4碼)/月(2碼)/日(2碼)」的文字格式。
後記:
> 彙整個人檔案時候,覺得非常好用。所以花了時間研究一下。
> 而且在計算個人年齡時,也會用到。