2013年3月8日 星期五

Excel-轉換時間分秒的表示法(SUBSTITUTE,TIMEVALUE)

有網友問到:如果有一個時間數列其分和秒是以「'、"」表示,這個數列的時間是以文字格式儲存,無法拿來進一步運算,如何將其轉換為真正的時間呢?

這個問題很實用,也看過有人使用這樣的公式:

儲存格B2:

=MID(A2,1,FIND("'",A2)-1)&"分"&MID(A2,FIND("'",A2)+1,FIND("""",A2)-FIND("'",A2)-1)&"秒"

找到「'」和「"」的位置,再以 MID 函數取出分和秒的數值。

不過我建議使用 SUBSTITUTE 函數會比較簡單:

儲存格C2:=SUBSTITUTE(SUBSTITUTE(A2,"'",":"),CHAR(34),"")

將「'」換成「分」,將「"」換成「秒」。

其中因為 SUBSTITUTE 函數無法直接置換「"」符號,所以將「"」符號以 CHAR(34) 替代(「"」的 ASCII 數值為 34)。

但是以上的轉換結果為文字,仍不是數值(時間),無法拿來正確的運算,所以要再轉換一次。

儲存格D2:=TIMEVALUE("0:"&SUBSTITUTE(SUBSTITUTE(A2,"'",":"),CHAR(34),""))

利用 TIMEVALUE 函數將文字轉換為時間,注意到公式中有將原來的時間之前串接「"0:"」字串,原因是 Excel 會將 7:6 視為 7 時 6 分,所以串接「"0:"」之後可以變為 0 時 7 分 6 秒。

再利用數值格式設定將其轉換為以「:」表示的時間,如此的時間就可以拿來運算了!

沒有留言:

張貼留言

好康東東