最近學校註冊組同仁在處理新生資料時,告知常常會有日期處理的困擾。因為由系統輸出或要輸入的日期轉到 Excel 之後,不知要如何處理。因為日期有西元年、民國年的差別,生日的月和日也有是否要補足2碼的問題。

例如:

1.日期的民國年、月、日分於三個儲存格要轉換為西元年,並且月和日以2碼表示。

2.格式:94.01.08,要轉換為西元年,並且月和日以2碼表示。

3.格式:940108,要轉換為西元年,並且月和日以2碼表示。

4.格式:94.01.08,要轉換為西元年,並且月和日以2碼表示。

image

 

【公式設計與解析】

以下全部轉換為西元年並且月和日以2碼表示。

1. 日期的年、月、日分於三個儲存格

image

儲存格E2:=TEXT(DATE(1911+A2,B2,C2),"yyyy/mm/dd")

DATE 函數:用以將年、月、日的參數轉換為日期序號。

TEXT 函數:利用參數「yyyy/mm/dd」將日期的格式設定為年四碼、月二碼、日二碼。

 

2.格式:94.01.08

image

儲存格C9:=SUBSTITUTE(A7,".","")

SUBSTITUTE 函數:將儲存格A7裡的「.」置換為空字串「""」。

 

3.格式:940108

image

儲存格C12:

=TEXT(DATE(1911+MID(A12,1,2),MID(A12,3,2),MID(A12,4,2)),"yyyy/mm/dd")

MID 函數:設定在儲存格A12中的指定位置取出指定數量的字串。例如:MID(A12,3,2),在儲存格A12的第 3 個字元取出 2 個字。

 

4.格式:94.01.08

image

儲存格C17:

=TEXT(DATE(1911+MID(A17,1,2),MID(A17,4,2),MID(A17,7,2)),"yyyy/mm/dd")

 

【延伸學習】

Excel-西元年和民國年互換表示(TEXT,MID,DATE,SUBSTITUTE)

Excel-西元年和民國年互換表示(TEXT,MID,DATE,SUBSTITUTE)

Excel-西元年和民國年互換表示(MID,DATE)

Excel-民國年格式轉換為西元年格式(RIGHT,MID,LEFT,TEXT,DATE)

Excel-民國年轉西元年(SUBSTITUTE,REPT,DATE,LEFT,MID,RIGHT)

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

學不完.教不停.用不盡文章列表

arrow
arrow
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

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