EXCEL試算表 邏輯寫法想不透

[l257065]

大家好
小弟最近找到新工作，在公司上班時得輔助事務小姐處理員工上班的工時

工作時間若有中午休息則總工作時間得扣除50分鐘，公司上班時間8:00 中午休息12:00~12:50 下班時間:16:50 加班:19:50 夜班到24:00

如8:00~19:50 工作時間為 710-50(有中午休息)=660分鐘
12:50~16:50 240分鐘

但小弟寫的EXCEL試算表寫到後面
16:50~24:00的時間會亂跳...多50分鐘，不然就是少50分鐘-.-

有EXCEL達人可以幫我嗎?

以下為2007 EXCEL 礙於商業機密，所以只剩下工時的部分
http://www.badongo.com/file/26402422

 

[j62u6]

先說明為何 上班時間>＝16:50要加50分鐘吧

 

[xxxEVA]

是只有中午休息12:00~12:50 會需要扣除嗎?
如果是的話扣中午就好
=HOUR(F3)*60+MINUTE(F3)+IF(AND(A3<=D3,B3>=D3),-50,0)
或是把時間扣除範圍直接定成D3 12:00~E3 12:50
=HOUR(F3)*60+MINUTE(F3)+IF(AND(A3<=D3,B3>=E3),-50,0)
但這些作法最怕他在中午休息時間給你打卡
最好弄些方法去抓去防範
另外加班會跨日的
要馬輸入者要輸入正確方法(EX:半夜一點要輸入25:00)
要不然就是函數裡面要考慮日期

 

[yoching]

一般~~~先算全工時出來。再來扣除是否有需要的條件扣除。
有三種方式，

一種是判斷扣除法。一種是兩段式相加法。再來就是時間扣除法。

你目前用的就是判斷扣除法。但因為時與分是分開的判斷。很難有好點的判斷機制，也就是有可能你要寫的判斷得要是多重的判斷情況。
沒用好就會「花花」。

多段式相加法。就是利用上班段時間的相加方式。如12:00之前為一段。12:50~16:50為一段19:50~24:00為一段。
不過這只是比較不用擔心扣除的問題。但是計算的公式也是非常的多。

我會比較推最後的計算方式。就是時間扣除的方法。
先將上班時間的分數及下班時間的分數由00:00起算。
也就是說假設
上班是 8:00 => 8*60+0 =>480
下班 19:50 => 16*60+50 = 1190
再來比較值為12:00 => 720 12:50=>770
那判斷時間就是
下班時間-上班時間-IF(上班時間 < 720 && 下班時間 > 770,50,0)
1190-480-50 => 660

如果有可能中午休息時間打卡的情況下呢???那就額外去加個判斷式。-IF(上班時間>=720 && 下班時間<=770,50-上班時間(分),0)

這樣主要的公式就為
下班時間-上班時間-IF(上班時間 < 720 && 下班時間 > 770,50,0)-IF(上班時間>=720 && 下班時間<=770,50-上班時間(分),0)
重新套用格式的化~~~其公式就是
(下班時*60+下班分)-(上班時*60+上班分)-IF((上班時*60+上班分) < 720 && (下班時*60+下班分) > 770,50,0)-IF((上班時*60+上班分)>=720 && (下班時*60+下班分)<=770,50-上班分,0)

這樣了解了嗎??

 

[l257065]

謝謝大家的意見，我在試看看瞜

成功後再回報