2013年4月14日 星期日

Excel-時間轉換(以每半小時為單位)

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

每半小時發放津貼,剩餘的時間則撥為累計時數。例如︰
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 函數來修正。

沒有留言:

張貼留言

檢視其他文章

好康東東