2011年6月7日 星期二

Excel-日期和時間的格式設定

在取得的網頁連線的IP位址資料中,雖然有日期和時間的欄位,但是其格式並非 Excel 接受的日期時間格式(下圖左),該如何轉換成正確的格式呢(下圖右)?

儲存格E2:=DATEVALUE(LEFT(A2,LEN(A2)-3))

因為日期資料(A欄)的最後三個字是星期幾,所以透過LEFT函數將其濾除。再使用DATEVALUE將日期的文字轉換成正確的日期格式。

儲存格F2:=IF(LEFT(B2,2)="下午",TIMEVALUE(RIGHT(B2,LEN(B2)-3))+TIMEVALUE("12:00:00"),TIMEVALUE(RIGHT(B2,LEN(B2)-3)))

因為時間資料(B欄)的前三個字是上、下午加上一個空白,所以先使用RIGHT函數取出時間,再以LEFT函數判斷時間的前二個字是否為「下午」(反之為「上午」),如果是將取得的時間加上12小時「TIMEVALUE("12:00:00")」。

相關函數說明,請參閱微軟網站:

DATEVALUEhttp://office.microsoft.com/zh-tw/excel-help/HP010342404.aspx

DATEVALUE 函數會將儲存為文字的日期轉換成 Excel 識別為日期的序列值。

語法:DATEVALUE(date_text)

date_text:代表 Excel 日期格式之日期的文字,或參照包含代表 Excel 日期格式之日期文字的儲存格,例如,"1/30/2008" "30-Jan-2008" 就是代表日期的引號中文字字串。

TIMEVALUEhttp://office.microsoft.com/zh-tw/excel-help/HP010342956.aspx

傳回對應於時間文字字串的小數位數值。

語法:TIMEVALUE(time_text)

time_text:依 Microsoft Excel 任何一種時間格式表達的文字串。

 

沒有留言:

張貼留言

好康東東