學不完．教不停．用不盡

有位網友問到如下圖的資料表中，記錄了日期和開始／終止時間，要根據這個資料表得到轉換後的開始／終止時間，及超時時數和剩餘時間。要求如下：

每半小時發放津貼，剩餘的時間則撥為累計時數。例如︰
1) 超時工作 55分鐘 = 55分鐘累計時數
2) 超時工作 1 小時 = 1 小時津貼
3) 超時工作 1 小時 45分鐘 = 1:30 小時津貼 + 15 分鐘累計時數
4) 超時工作 3 小時 25分鐘 = 3 小時津貼 + 25 分鐘累計時數

【重點提示】

在 Excel 的日期/時間處理，是以 1 代表一天，每一小時即為 1/24，每一分鐘即為 1/24/60。而 1/24/60 在使用時會有小數點位數產生無法精確的現象，必須要修正。

【輸入公式】

(1) 轉換開始時間

儲存格E2：=$B2+TEXT(C2,"00\:00")

藉由 TEXT 函數，轉換時間格式。

(2) 轉換終止時間

儲存格F2：=IF(VALUE(D2)<VALUE(C2),$B2+1+TEXT(D2,"00\:00"),$B2+TEXT(D2,"00\:00"))

先判斷如果終止時間小時起始時間，表示已經跨天了，所以日期要加 1，再藉由 TEXT 函數，轉換時間格式。

(3) 計算時數(以30分鐘為單位)

儲存格G2：=IF(INT((F2-E2)*24*60/30)/2<1,0,INT((F2-E2)*24*60/30)/2)

(F2-E2)*24*60：計算終止時間 - 開始時間的分鐘數。

INT((F2-E2)*24*60/30)/2)：計算上式的分鐘數為多少個30分鐘。

如果上式小於 1 (表示1小時內)，則顯示 0。

(4) 計算剩餘時間(以分鐘為單位)

儲存格H2：=ROUND((F2-E2)*24*60-G2*60,0)

(F2-E2)*24*60-G2*60：將所有時間減掉以30分鐘為單位的時間，即為剩餘時間。

使用 ROUND 函數的原因在於計算過程中可能產無法整除的狀況，會使結果發生錯誤，所以使用 ROUND 函數來修正。