2015年4月28日 星期二

Excel-轉換含中文字的日期時間為數值格式

網友問到在 Excel 中產生特定的日期格式時出現了轉換上的問題。參考下圖,若日期時間為2015/3/1 下午 01:15:00,想要顯示為2015/3/1 13:15,該如何處理?

(1) 在文字中取出日期

=DATEVALUE(LEFT(A2,FIND(" ",A2)-1))

(2) 在文字中取出時間

IF(FIND("下午",A2),TIMEVALUE(RIGHT(A2,8))+0.5,TIMEVALUE(RIGHT(A2,8)))

(3) 合併日期和時間

儲存格B2:=DATEVALUE(LEFT(A2,FIND(" ",A2)-1))+IF(FIND("下午",A2),
TIMEVALUE(RIGHT(A2,8))+0.5,TIMEVALUE(RIGHT(A2,8)))

並且設定儲存格的數值格式:

 

進一步探討一下!參考下圖。

(1)

若日期和時間分別位於二個儲存格,而且原先就是數值格式,其輸入為標準的日期和時間格式,則只要把二個儲存格相加即可合併日期和時間,再以設定儲存格格式的方式來改變顯示結果。

(2)

若日期和時間分別位於二個儲存格,原先是文字格式,其輸入為標準的日期和時間格式,則只要把二個儲存格相加即可合併日期和時間(自動轉換為數值格式),再以設定儲存格格式的方式來改變顯示結果。

(3)

若是儲存格中含有中文字,則無法直接轉換為日期時間格式,必須再以公式來轉換。

儲存格E12:=MID(A10,1,FIND("年",A10)-1)

儲存格E13:=MID(A10,FIND("年",A10)+1,FIND("月",A10)-FIND("年",A10)-1)

儲存格E14:=MID(A10,FIND("月",A10)+1,FIND("號",A10)-FIND("月",A10)-1)

儲存格E15:=IF(FIND("下午",C10),TIMEVALUE(RIGHT(C10,8))+0.5,TIMEVALUE
(RIGHT(C10,8)))

儲存格E16:=DATE(MID(A10,1,FIND("年",A10)-1),MID(A10,FIND("年",A10)
+1,FIND("月",A10)-FIND("年",A10)-1),MID(A10,FIND("月",A10)+1,FIND
("號",A10)-FIND("月",A10)-1))+IF(FIND("下午",C10),TIMEVALUE(RIGHT(C10,8))
+0.5,TIMEVALUE(RIGHT(C10,8)))

沒有留言:

張貼留言

好康東東