2011年7月25日 星期一

Excel-轉換日期和時間的格式

在 Excel 的工作表中,可能為了輸入方便或是由其他資料來源取得,日期輸入為:20110101格式,來表示2011年1月1日;時間輸入為:0102格示,來表示1時2分。如何轉換為 Excel 可以運算的日期格式或是正確的表示方式呢?(參考下圖)

因為「20110101」和「0102」被 Excel 認為只是一個數字或是一串文字,所以要以TEXT函數來轉換:

儲存格B2:=TEXT(A2,"0000-00-00")

將原始日期轉換為「0000-00-00」格式,此轉換結果視日期為文字字串。

如果要拿日期來運算,則應修正為:

儲存格C2:=--TEXT(A2,"0000-00-00")

其中的「--」運算可將數字組成的文字轉換為可運算的數字。

同理,來處理時間部分。

儲存格E2:=TEXT(D2,"00\:00")

公式中的「\:」,乃用於強制顯示「:」,否則公式結果將會出錯。也可以使用「!:」,結果是相同的。

儲存格F2:=--TEXT(D2,"00\:00")

如果,你要反動作將B欄的日期轉換為A欄的表示法,而E欄的時間轉換為D欄的表示法,該如何處理?

儲存格A2:=SUBSTITUTE(B2,"-","")

儲存格D2:=SUBSTITUTE(E2,":","")

利用SUBSTITUTE函數將「-、:」去除(以空的字串取代)。

2 則留言:

  1. 您好, 小弟正在做一個計算超時工作時間的excel。有些問題想請教。

    我在A欄會以例如2013/1/1 的格式輸入日期, B欄及C欄是超時工作開始及終止的時間, 以方便的格式例如2030的格式。小弟打算在D欄(超時工作開始)、E欄(超時工作終止) 做轉換的工序, 而F 欄則做E、D欄的相減得出超時的時間。 想請您以下問題。

    1)如以我的輸入方式有否公式能做到日期和時間也在同一個儲存格出現,即如"2013/2/1 20:30"這樣? 我試過=CONCATENATE(A4," ",TEXT(B4,"00\:00")),, 但得出的結果是41279 20:30, 未能做到2013/2/1 20:30。

    2) 遇到超時開始時間是2300, 終止時間是0015,相減的話結果會是######,如不想加一欄日期在超時終止時間前去解決這問題,有否公式可在轉換時間的那欄或其他方法處理這個問題嗎?

    小弟未知在哪裡可以得到您聯絡,故在此留言,也希望告知如何可看到您的回覆。請別見怪,萬分感激!
    這是小弟的電郵︰keithyipht@gmail.com

    回覆刪除
    回覆
    1. 請參考: http://isvincent.blogspot.tw/2013/03/excel_29.html

      刪除

檢視其他文章

好康東東