2013年3月29日 星期五

Excel-文字時間轉換為數字時間

有網友問到一個在 Excel 中,關於文字時間轉換為數字時間的問題。在下圖左的資料表中,開始和結束的時間並非以標準的時間表示法來輸入,導至無法用在正確的顯示和計算結束和開始的時間差距。要如何轉換才能在後續使用呢?(參考下圖右)

(一) 開始日期

儲存格F2:=B2+TIME(LEFT(C2,2),RIGHT(C2,2),)

因為原始的開始和結束時間為「文字」型態,所以使用 LEFT 函數取出開始時間的左側 2 個數字做為「時」,再以 RIGHT 函數取出開始時間的右側 2 個數字做為「分」,最後再帶入 TIME 函數轉換為時間的型態。

(二) 結束日期

儲存格G2:=IF(LEFT(D2,2)*1<LEFT(C2,2)*1,B2+1+TIME(LEFT(D2,2),RIGHT(D2,2),),B2+TIME(LEFT(D2,2),RIGHT(D2,2),))

原理同開始日期之做法,但因為結束時間有跨日的問題,所以判斷如果取得結束的時數小於開始的時數,則表示有跨日,則要將日期加上 1 天。

(三) 計算間距

儲存格H2:=G2-F2

因為開始時間和結束時間已經轉換為正確的時間型態,所以計算時間間距時,只要將兩數相減即為間距。

複製儲存格F2:H2,往下各列貼上。

其中的開始時間和結束時間的數值格式,自訂為「[$-409]yyyy/mm/dd hh:mm AM/PM;@」,以求格式的美觀及一致性。

image

2 則留言:

  1. 不好意思,看看這次能否全部顯示吧…

    ================================================
    感謝你的解答!現小弟還在處理一個問題解決不到,想請教你。先再感激幫忙。
    假設小弟公司的制度在一般情況下是超時未滿一小時,便把超時時數撥為累積時數(補時間),超時滿一小時便發放津貼(補錢),一小時後其後每半小時發放津貼,剩餘的時間則撥為累計時數。例子如下︰

    1)超時工作 55分鐘 = 55分鐘累計時數
    2)超時工作 1 小時 = 1 小時津貼
    3)超時工作 1 小時 45分鐘 = 1:30 小時津貼 + 15 分鐘累計時數
    4)超時工作 3 小時 25分鐘 = 3 小時津貼 + 25 分鐘累計時數

    也有個別情況會把全部超時工作時數全撥為 累計時數 或 津貼時數。

    現在小弟的excel 狀況如下︰
    A 欄 = 日期
    B 欄 = 超時開始時間 (以4 位數字輸入,例如︰2300)
    C 欄 = 超時終止時間 (以4 位數字輸入,例如︰0015)
    D 欄 = 轉化欄 (將B 欄轉化為時間,公式為 =$A3+TEXT(B3,"00\:00"), 結果為2013/01/01 11:00 PM)
    E 欄 = 轉化欄 (將C 欄轉化為時間,公式為 「= IF (TEXT(C3,"00\:00")=TIMEVALUE("01:00"),F3=TIMEVALUE("01:30"),F3=TIMEVALUE("02:00"),F3=TIMEVALUE("02:30"),F3=$P$1,F3<$P$2),F3-$P$1,IF(AND(F3>=$P$2,F3<$P$3),F3-$P$2,IF(AND(F3>=$P$3,F3<$P$4),F3-$P$3,IF(AND(F3>=$P$4,F3<$P$5),F3-$P$4,IF(AND(F3>=$P$5,F3<$P$6),F3-$P$5,F3)

    -------> 這兩種可以做到小弟想要的效果,但都只可以相減至2:30或3:00,再多的話就不能繼續下去,EXCEL 說有錯誤。


    請問小弟應如何解決這個問題?小弟認識的函數不多,還望指教,謝謝。
    =========================================================

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

      刪除

好康東東