如何計算正常工時與加班工時

[JERRY]

煩請協助，如何計算正常工時與加班工時。
A B C D E F
G H I
1 自 至 正常工時 時薪 正常薪資 加班工時 時薪 加班薪資 合計薪資
2 0900 1700 7.5 100 750.00 0 200
0 750
3 0700 1700 7.5 100 750.00 2 200
400 1,150
4 0700 1900 7.5 100 750.00 4 200
800 1,550
5 1130 1700 5.0 100 500.00 0 200
0 500
6 1330 2100 3.5 100 350.00 4 200
800 1,150

1.工時(跨越中午須扣除午休0.5小時)，每日正常上班工時為7.5小時。
2.上班時間為0900，下班時間為1700。提早上班或延後下班皆視為加班時間。

[天道無常]

一、假設無大夜班，即不會從晚上作到隔天早上．

下面是算正常工時的方程式．加班工時還請你按這方程式自己修改一下．

=IF(FLOOR(B4/100,1)>=9,
(12-CEILING(B4/100,1)+(B4-FLOOR(B4/100,1)*100)/60),3)+IF(C4<=1700,
FLOOR(C4/100,1)-12 +
(C4/100-FLOOR(C4/100,1))*100/60,5)+IF(B4<1200,IF(C4>1200,-0.5),0)

方程式分為三部份：

一、算早上時數：

IF(FLOOR(B4/100,1)>=9, (12-CEILING(B4/100,1)+(B4-FLOOR(B4/100,1)*100)/60),3)

12-CEILING(B4/100,1)：計算早上工作時數
(B4-FLOOR(B4/100,1)*100)/60)：將分鐘換成小時

二、算下午時數

IF(C4<=1700, FLOOR(C4/100,1)-12 + (C4/100-FLOOR(C4/100,1))*100/60,5)

三、算是否跨過中午，若跨過中午則減零點五小時．

IF(B4<1200,IF(C4>1200,-0.5),0)

JERRY 提到:

[天道無常]

> 三、算是否跨過中午，若跨過中午則減零點五小時．
>
> IF(B4<1200,IF(C4>1200,-0.5),0)
>

抱歉！有點問題．這個要改成：

IF(B4<1200,IF(C4>1200,-0.5,0),0)

天道無常 提到:

[JERRY]

非常感謝您的回覆。

我所需要的條件如下：
正常工時：(包含以下六個條件)
IF(AND(D3>C3,C3>=900,D3>1300,D3<=1700),(((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60))-0.5), "WRONG")

IF(AND(D3>C3,C3>=900,D3<=1300),((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60)), "WRONG")

IF(AND(D3>C3,C3>=1300,D3>1300,D3<=1700),((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60)), "WRONG")

IF(AND(D3>C3,C3<900,D3>1300,D3<=1700),(((INT(D3/100)+MOD(D3,100)/60)-(INT(900/100)+MOD(900,100)/60))-0.5), "WRONG")

IF(AND(D3>C3,C3<900,D3<=1300),((INT(D3/100)+MOD(D3,100)/60)-(INT(900/100)+MOD(900,100)/60)), "WRONG")

IF(AND(D3>C3,C3<900,D3>1300,D3>1700),(((INT(1700/100)+MOD(1700,100)/60)-(INT(900/100)+MOD(900,100)/60))-0.5), "WRONG")

加班工時：(包含以下三個條件)
IF(AND(D3>C3,C3<900,D3>1300,D3<=1700),(((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60))), "WRONG")

IF(AND(D3>C3,C3<900,D3<=1300),((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60)), "WRONG")

IF(AND(D3>C3,C3<900,D3>1700),((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60))+((INT(D3/100)+MOD(D3,100)/60)-(INT(1700/100)+MOD(1700,100)/60)), "WRONG")

這些條件經過測試，符合需求。
只是不知如何分別整合至正常工時欄位與加班工時欄位。
煩請撥冗不吝指導。
謝謝!!

[天道無常]

可以將IF加到return value中．

if(condition, a,b)可以變成：

if(condition,if(condition,a,b),if(condition,c,d))

可以繼續加．上式中的a,b,c,d全都可以變成if statements.

JERRY 提到:

[JERRY]

天道無常大，
小弟不才，是否可以麻煩您做個示範
正常工時=??
或
加班工時=??

對IF函數，個人的理解是
IF(條件,符合條件下所顯示的值,不符合條件下所顯示的值)
對於您上述的教學，我無法融會貫通，在我的條件式中
哪一個可以當成條件

煩請撥冗開示!!
謝謝!!

[天道無常]

你說的是最簡單的IF.

IF(條件,符合條件下所顯示的值,不符合條件下所顯示的值)

上式之中，每個「顯示值」都可以換成IF函數（或其他函數）．

IF(3>2,IF(4>3,"5>4",0),0)將會顯示「5>4」．
IF(3>4,0,IF(4>5,0,"四不會大於五啦"))將會顯示「四不會大於五啦」．

也可能有下面這種：

IF(A4>B5,SUM(C5:D7),MONTH(E6))

加班工時的條件較簡單，就以它當例子．

第一步先將邏輯化簡，因為三個條件都有D3>C3以及C3<900，故化簡如下：

IF(AND(D3>C3,C3<900),xxx,"WRONG")

這個xxx要想辦法將三個條件（(D3>1300,D3<=1700),D3<=1300, D3>1700）放進去．

由簡單的先來：

IF(D3>1700,方程式三,yyy)

yyy要放入其他兩個條件：

IF(D3<=1300,方程式二,zzz)

zzz放入最後一個條件：

IF(AND(D3>1300,D3<=1700),方程式一,"WRONG")

代入結果，得到下式：

IF(AND(D3>C3,C3<900),IF(D3>1700,方程式三,IF(D3<=1300,方程式二,IF(AND(D3>
1300,D3<=1700),方程式一,"WRONG"))),"WRONG")

（　此處之方程式並非整個IF函數，而是符合條件時所顯示的值那一部份．

方程式一:

(((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60)))

方程式二:

((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60))

方程式三:

((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60))+
((INT(D3/100)+MOD(D3,100)/60)-(INT(1700/100)+MOD(1700,100)/60))

）

將方程式代入結果，最後得到下式：

IF(AND(D3>C3,C3<900),IF(D3>1700,((INT(900/100)+MOD(900,100)/60)-
(INT(C3/100)+MOD(C3,100)/60))+((INT(D3/100)+MOD(D3,100)/60)-
(INT(1700/100)+MOD(1700,100)/60)),IF(D3<=1300,(((INT(900/100)+
MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60))),
IF(AND(D3>1300,D3<=1700),(((INT(900/100)+MOD(900,100)/60)-
(INT(C3/100)+MOD(C3,100)/60))),"WRONG"))),"WRONG")

你給的這三個IF函數好像有問題，無法算出第六列的加班時數．

再者，INT(900/100)+MOD(900,100)/60根本就是常數，直接代以數字即可，何以要
煩勞Excel再算一次？

JERRY 提到:

[JERRY]

感謝天道無常大您的詳細教導。
依您的範例，我已完成加班工時如下：如有疏漏，還煩請指正，謝謝!!

加班工時：C3為上班時間，D3為下班時間
方程式１ 條件D3>C3
IF(D3>C3,xxx,0) 這個xxx要想辦法將三個條件（C3<900,D3>1700）放進去
IF(C3<900,方程式一,AAA)
IF(D3>1700,方程式二,0)
整合後方程式１：
IF(D3>C3,IF(C3<900,方程式一,IF(D3>1700,方程式二,0)),0)

方程式二： 條件D3>1700
IF(D3>1700,方程式二,0) 這個方程式二要想辦法將四個條件（AND(900<=C3,C3<1300),
AND(1300<=C3,C3<=1700),1700<C3, AND(900<=C3,C3<=1700)）放進去
IF(AND(900<=C3,C3<1300),方程式三,AAA)
IF(AND(1300<=C3,C3<=1700),方程式四,BBB)
IF(1700<C3,方程式五,CCC)
IF(AND(900<=C3,C3<=1700),方程式六,0)

方程式三
=>IF(AND(D3>C3,D3>1700,900<=C3,C3<1300),((INT(D3/100)+MOD(D3,100)/60)-(INT(1700/100)+MOD(1700,100)/60)),0)
方程式四
=>IF(AND(D3>C3,D3>1700,1300<=C3,C3<=1700),((INT(D3/100)+MOD(D3,100)/60)-(INT(1700/100)+MOD(1700,100)/60)),0)
方程式五
=>IF(AND(D3>C3,D3>1700,1700<C3),((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),0)
方程式六
=>IF(AND(D3>C3,D3>1700,900<=C3,C3<=1700),((INT(D3/100)+MOD(D3,100)/60)-(INT(1700/100)+MOD(1700,100)/60)),0)
整合後方程式二：
IF(AND(900<=C3,C3<1300),方程式三, IF(AND(1300<=C3,C3<=1700),方程式四,
IF(1700<C3,方程式五,IF(AND(900<=C3,C3<=1700),方程式六,0))))

帶入後方程式二：
IF(AND(900<=C3,C3<1300),((INT(D3/100)+MOD(D3,100)/60)-(INT(1700/100)+MOD(1700,100)/60)),IF(AND(1300<=C3,C3<=1700),((INT(D3/100)+MOD(D3,100)/60)-(INT(1700/100)+MOD(1700,100)/60)),IF(1700<C3,((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),IF(AND(900<=C3,C3<=1700),((INT(D3/100)+MOD(D3,100)/60)-(INT(1700/100)+MOD(1700,100)/60)),0))))

方程式一： 條件C3<900
IF(C3<900,方程式一,0) 這個方程式一要想辦法將五個條件（D3<900,AND(900<=D3,D3<1300),AND(1300<=D3,D3<=1700),D3>1700,AND(900<=D3,D3<=1700)）放進去
IF(D3<900,方程式七,AAA)
IF(AND(900<=D3,D3<1300),方程式八,BBB)
IF(AND(1300<=D3,D3<=1700),方程式九,CCC)
IF(D3>1700,方程式十,DDD)
IF(AND(900<=D3,D3<=1700),方程式十一,0)

方程式七
=>IF(AND(D3>C3,C3<900,D3<900),((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),0)
方程式八
=>IF(AND(D3>C3,C3<900,900<=D3,D3<1300),((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),0)
方程式九
=>IF(AND(D3>C3,C3<900,1300<=D3,D3<=1700),((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),0)
方程式十
=>IF(AND(D3>C3,C3<900,D3>1700),((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60))+((INT(D3/100)+MOD(D3,100)/60)-(INT(1700/100)+MOD(1700,100)/60)),0)
方程式十一
=>IF(AND(D3>C3,C3<900,900<=D3,D3<=1700),((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),0)
整合後方程式一：
IF(D3<900,方程式七,IF(AND(900<=D3,D3<1300),方程式八,IF(AND(1300<=D3,D3<=1700),方程式九,IF(D3>1700,方程式十,IF(AND(900<=D3,D3<=1700),方程式十一,0)))))

帶入後方程式一：
IF(D3<900,((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),IF(AND(900<=D3,D3<1300),((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),IF(AND(1300<=D3,D3<=1700),((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),IF(D3>1700,((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60))+((INT(D3/100)+MOD(D3,100)/60)-(INT(1700/100)+MOD(1700,100)/60)),IF(AND(900<=D3,D3<=1700),((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),0)))))

帶入後方程式１：(此即為加班工時完成版)
IF(D3>C3,IF(C3<900,IF(D3<900,((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),IF(AND(900<=D3,D3<1300),((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),IF(AND(1300<=D3,D3<=1700),((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),IF(D3>1700,((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60))+((INT(D3/100)+MOD(D3,100)/60)-(INT(1700/100)+MOD(1700,100)/60)),IF(AND(900<=D3,D3<=1700),((INT(900/100)+MOD(900,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),0))))),IF(D3>1700,IF(AND(900<=C3,C3<1300),((INT(D3/100)+MOD(D3,100)/60)-(INT(1700/100)+MOD(1700,100)/60)),IF(AND(1300<=C3,C3<=1700),((INT(D3/100)+MOD(D3,100)/60)-(INT(1700/100)+MOD(1700,100)/60)),IF(1700<C3,((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),IF(AND(900<=C3,C3<=1700),((INT(D3/100)+MOD(D3,100)/60)-(INT(1700/100)+MOD(1700,100)/60)),0)))),0)),0)

[天道無常]

哇！八百多字元長的方程式！一般人看了大概會昏倒．

加班時間真的那麼複雜嗎？

由IF(D3>C3,xxx,0)看來，你們沒有大夜班，不會從晚上做到隔天早上．若是如
此，那應該很好算，只要兩個方程式即可．

算早上的加班時數：

IF(900-C3>0,INT((900-C3)/100)+MOD(900-C3,100)/60,0)　'九點以前的上班時數

算晚上的加班時數：

IF(D3-1700>0,INT((D3-1700)/100)+MOD((D3-1700),100)/60,0)　'五點以後的加
班時數

總加班時數即此二式相加．

IF(900-C3>0,INT((900-C3)/100)+MOD(900-C3,100)/60,0)+IF(D3-1700>0,INT((D3-1700)/100)+MOD((D3-1700),100)/60,0)

我將上式用到你那張表，完全符合，沒有問題．

[JERRY]

您真是太厲害了，把那一長串的程式，簡單化成兩行!!!
還有一個小小問題，我測試的結果
C=0830 D=1700 結果為1.16667，我需要的答案是0.5
因為我的工時計算是以最小單位0.5小時為基準。
問題發生在九點以前的程式。

[天道無常]

試試看這一個．

一、假設時間不為零．．
二、可以算任何分鐘數．若十五分鐘，結果會是0.75小時；二十分鐘則為0.6666小
時；四十五分鐘為0.25小時等等．

=IF(900-C9>0,INT((900-C9)/100)+IF(MOD(C9,100)<>0,(60-MOD(C9,100))/60,0))+IF(D9-1700>0,INT((D9-1700)/100)+IF(MOD(D9,100)<>0,(60-MOD(D9,100))/60,0))

JERRY 提到:

[JERRY]

上述公式在1700以後發生問題
C9=1700，D9=1715，所得加班工時不應為0.75
這部份純討論，應該不會以15分鐘為單位。

以下是正常工時的條件式，是否有整合的空間，還是只能依您前面所述的帶入法一一帶入
正常工時：
0900以前上班：C3<900
0900以前下班(不須扣午休時間0.5小時)、IF(AND(C3<900,D3<900),((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),0)
0900(含)以後~1200(含)下班(不須扣午休時間0.5小時)、IF(AND(D3>=900,D3<=1200),((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),0)
1200以後~1700(含)下班(須扣午休時間0.5小時)、IF(AND(D3>1200,D3<=1700),((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60))-0.5,0)
1700以後下班(須扣午休時間0.5小時)。IF(D3>1700,((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60))-0.5,0)

0900以後上班：C3>=900
0900以後~1200(含)下班(不須扣午休時間0.5小時)、IF(AND(D3>900,D3<=1200),((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),0)
1200以後~1700(含)下班(須扣午休時間0.5小時)、IF(AND(D3>1200,D3<=1700),((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60))-0.5,0)
1700以後下班(須扣午休時間0.5小時)。IF(D3>1700,((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60))-0.5,0)

1230以後上班：C3>1230
1230以後~1700(含)下班(不須扣午休時間0.5小時)、IF(AND(D3>1230,D3<=1700),((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),0)
1700以後下班(不須扣午休時間0.5小時)。IF(D3>1700,((INT(D3/100)+MOD(D3,100)/60)-(INT(C3/100)+MOD(C3,100)/60)),0)

1700以後上班：C3>1700
1700以後下班(不須扣午休時間0.5小時)。IF(D3>1700,0,0)

感謝您的撥冗回覆!!

[天道無常]

JERRY 提到:

> 上述公式在1700以後發生問題
> C9=1700，D9=1715，所得加班工時不應為0.75
> 這部份純討論，應該不會以15分鐘為單位。
>

試試這個：

=IF(900-C9>0,INT((900-C9)/100)+IF(MOD(C9,100)<>0,(60-MOD(C9,100))/60,0))+IF(D9-1700>0,INT((D9-1700)/100)+IF(MOD(D9,100)<>0,MOD(D9,100)/60,0))

> 以下是正常工時的條件式，是否有整合的空間，還是只能依您前面所述的帶入法一一帶入
> 正常工時：

請看我最前面的覆帖．