エクセルで残業時間計算

質問

出勤時間、退勤時間が一定でない場合の時間計算をエクセルで行いたいのですが関数がいまいちわかりません。
所定労働時間　8時間
所定労働時間を超えた時間について時間外に自動計算し、尚且つ
22：00～5：00の間は深夜時間外に自動計算したいと思っています。

例
　　　Ａ１　　　Ａ２　　　　Ａ３　　　　　Ａ４　　　　　Ａ５
1　　出勤時間　退勤時間　　拘束時間　　　時間外　　　深夜時間外
2　　17：00　　　6：00　　　13：00　　　(式入力)　　　(式入力)
3　　1：00　　　14：00　　　13：00　　　(式入力)　　　(式入力)
4　　9：00　　　20：00　　　11：00　　　(式入力)　　　(式入力)

以上の条件です、よろしくお願いします。

回答No.2

KURUMITO

表は次のようになっているとします。
Ａ１セルに出勤時間、Ｂ1セルに退勤時間、Ｃ1セルに拘束時間、Ｄ1セルに時間外、Ｅ1セルに深夜時間外とそれぞれ項目名があり、それぞれの項目の下方にデータが入るとします。
Ｃ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(A2:B2)<>2,"",IF(B2>A1,B2-A2,B2+"24:00"-A2))

Ｄ1セルには次の式を入力し下方にオートフィルドラッグします。

=IF(COUNT(A2:B2)<>2,"",IF(C2<("8:00"*1+E2),0,C2-"8:00"-E2))

Ｅ2セルには次の式を入力し下方にオートフィルドラッグします。

=IF(COUNT(A2:B2)<>2,"",IF(C2>"8:00"*1,IF(AND(B2>A2,MIN("29:00",B2)>MAX(A2+"8:00","22:00")),MIN("29:00",B2)-MAX(A2+"8:00","22:00"),IF(AND(B2<A2,MIN("29:00",B2+"24:00")>MAX(A2+"8:00","22:00")),MIN("29:00",B2+"24:00")-MAX(A2+"8:00","22:00"),0))))

いずれのセルの表示も書式設定の表示形式で時間にします。
なお、この式では8時間の所定労働時間の間に深夜時間になっても時間外の時間になっていなければ、深夜時間には計算されないようになっています。したがって17：00から6：00までの勤務でも深夜時間外は4：00時間となります。

お礼

わかりにくい説明にも関わらず、わかりやすい説明ありがとうございました。
さっそく試した所重い通りの結果になりましたありがとうございます。

回答No.1

nine999

時間で入力すると内部データが日数単位になりますが、時間単位で計算するようにしました。
あとセル番号と合いませんので、A1からA5はAからE列と読み替えます。

まず、準備として、退勤時間を計算しやすいように変換します。（ここは日数単位なので、表示書式は時間で指定すること）
（G列）=IF(A3>B3,B3+1,B3)
これにより、0時をまたぐ場合の計算をやりやすくします。
H列とI列の1行目に深夜時間外の元となる時間を入力しておきます。
（H1）22:00
（I1）5:00
これを利用して、出勤時間が深夜より前かどうかを判断します
（H列）=IF(A2<$H$1,IF(G2>$H$1,1,0),0)
出勤時間とG列の退勤時間の間に22時があれば「1」と表示します。
次は終わりの時間です。
（I列）=IF(H2=1,IF(B2>$I$1,1,0),0)
両方が１ならば、深夜時間が中に含まれますが、Hが１で、Iが０なら、深夜の途中で退社です。
(E2)=IF(H2=1,8-IF(I2=1,0,B2-$I$1),0)

時間外を計算する前に拘束時間を時間単位で算出します。（*24をしなければ、日数単位）
(C2)=(G2-A2)*24
C2を利用する場合は表示書式は時間ではなく、数値に変更します。

深夜の時間を引いて、8時間を超える場合が時間外です。
(D2)=IF(C2-E2>8,C2-E2-8,0)

時間単位は24で割ると日数単位になり、表示形式を時間にすると「00:00」形式になりますが、8時間を24で割ると割りきれないため、表示がおかしくなりました。
時間単位にしておけば、時給計算にはそのまま使えますので、こうしておきます。