Excel working hour計算

Question

請問高手們,如果我要利用excel計算一段時間內屬於上班時間的工作時數該怎麼寫?

上班時間 星期一到五 8:10-17:30 (不含六日以及國定假日)

例如:
1. 2006/12/1 15:30 - 2006/12/4 14:00
(2006/12/1 15:30-17:30 + 12/4 8:10-14:00 = 7hr又50分 = 7.8 hr)
2. 2006/12/1 18:00 - 2006/12/4 19:00
(2006/12/4 8:10-17:30 = 8.4 hr)

謝謝jokelee24的回答
因為資料是一大堆(數百筆,只有前後兩個時間點)
如例1,我只有兩個時間,不會分段keyin
所以可能沒辦法利用您所提供的方式

例 3: 200612/1 8:30 - 2006/12/12 14:00
沒辦法一一分段key in

謝謝小芳

8:10-17:30 應是9小時20分沒錯
我忽略了中午12:00-13:00為休息時間
所以扣除中午為8.2HR

To 小芳,

謝謝您的回答

但仍有以下問題
1. 如果起迄時間為同一日時,帶出的工作時數為 ########
2. 如果起始時間為8:10之前 如起: 2006/12/1 07:10 迄 2006/12/4 19:00
計算出來的時間為0

另外

8:10-17:30 間隔應是9小時20分沒錯

但是我忘了中午休息時間 12:00-13:00
所以我要算的是 8.2HR

Answer 1

先確認一下8:10-17:30 應是9小時20分,不知您的8.4HR是否計算錯誤?,Anyway,若依您的資料,我新增了以下黃色網底的欄位去輔助運算:











起
迄
上班時段
下班時段
上班逾時
下班超時
工作時數
換算hr

2006/12/1 15:30
2006/12/4 14:00
0.340
0.729
1
0
7:50
7.83

2006/12/1 18:00
2006/12/4 19:00
0.340
0.729
1
1
9:20
9.33
欄位公式解說:
1.上/下班時段08:10先轉成excel 時間序列值之格式幫助加減,因此
上班公式 =TIME(8,10,0)=0.340
下班 公式=TIME(17,30,0) =0.729
2.上班是否逾時/下班是否超時邏輯判定,以輔助後續時數計算,計
算值為1時表示逾時/超時
上班逾時驗證公式:(E2儲存格)
=IF((HOUR(A2)+MINUTE(A2)/60)>(HOUR(C2)+MINUTE
(C2)/60),1,0)
下班超時驗證公式:(F2儲存格)
=IF((HOUR(B2)+MINUTE(B2)/60)<(HOUR(D2)+MINUTE
(D2)/60),0,1)
3.工作時數計算公式(G2儲存格,格式請改為"時間"格式)
=NETWORKDAYS(A2,B2)*(D2-C2)-IF(TIME(HOUR(A2),MINUTE
(A2),0)>D2,0,IF(E2=0,(D2-C2),TIME(HOUR(A2),MINUTE(A2),0)-
C2))-IF(F2=0,(D3-TIME(HOUR(B2),MINUTE(B2),0)),(D2-C2))
NETWORKDAYS函數可計算工作天數(扣除週六/週日)使用可在YAHOO知識中搜尋...或到Microsoft網頁/小幫手查到
而上述公式簡單來說是用工作天數換算成滿額的工作時數後扣除遲到早退之缺時部份
4.換算hr公式 (H2儲存格)
= HOUR(G2)+MINUTE(G2)/60
最後再複製E2~H2公式到其他E欄~H欄儲存格即可算出上表資料

Answer 2

基本上EXCEL是有兩種日期系統的,如果你沒有變動過excel那1900年的1月0日是等於0的,而每一天的值是加1的,例:1900/1/31日是等於31的,那小於一天就是小數點以下,一小時是等於0.041666666......
你的例一:日期跟時間是可以打在一個儲存格或分開,現在以合在一個儲存格來說:
A1=2006/12/1 15:30
B1=2006/12/1 17:30
C1=2006/12/4 08:10
D1=2006/12/4 14:00
E1=B1-A1+D1-C1=7:50 (如果時間格式不對,就去---格式---儲存格---數值--時間(選取你要的時間格式)
如果你要7.8小時,E1=(B1-A1+D1-C1)*24
如果格式不對,就去---格式---儲存格---