エクセルを使って勤怠の集計をしています。

質問

エクセルを使って勤怠の集計をしています。
出社時間と退社時間を入力すれば、労働時間が出るような、関数式を教えて下さい。
開始時間　終了時間　総時間数　休憩時間　労働時間　通常時間　割増時間　
8:40　　　　17:15　　　　8:35　　　　1:10　　　　7:25　　　　7:25　　　0:00
12:50　　　 17:15　　　　4:25　　　　0:10　　　　4:15　　　　4:15　　　0:00
8:40　　　　19:15　　　　11:05　　　 1:40　　　　9:25　　　　8:00　　　1:25
8:40　　　　21:10　　　　12:30　　　 1:55　　　 10:35　　　　8:00　　　2:35

8:40～10:00、10:00から10分休憩、10:10～12:00、12:00から50分休憩
12:50～14:50、14:50から10分休憩、15:00～17:15、17:15から30分休憩
17:45～19:45、19:45から15分休憩、20:00～22:00、22:00～10分休憩
22:10から深夜残業となります。
割増時間は8時間以上の労働時間から付きます。
（10時、12:50からなど、途中から勤務が始まる人もいますので。）
現在手計算をしていますが、間違いが多くて困っています。
どうぞ宜しくお願いします。

回答No.1

KURUMITO

Ａ列からＧ列にかけてお示しのデータがあるとして、1行目は項目名で2行目からデータがあるとします。
総時間数の列でＣ2セルには次の式を入力します。

=IF(COUNT(A2:B2)<>2,"",B2-A2)

休憩時間の列でＤ2セルには次の式を入力します。

=IF(COUNT(A2:B2)<>2,"",IF(AND(A2<="8:40"*1,B2>="10:00"*1),MIN(B2-"10:00","0:10"),0)+IF(AND(A2<="10:10"*1,B2>="12:00"*1),MIN(B2-"12:00","0:50"),0)+IF(AND(A2<="12:50"*1,B2>="14:50"*1),MIN(B2-"14:50","0:10"),0)+IF(AND(A2<="15:00"*1,B2>="17:15"*1),MIN(B2-"17:15","0:30"),0)+IF(AND(A2<="17:45"*1,B2>="19:45"*1),MIN(B2-"19:45","0:15"),0)+IF(AND(A2<="20:00"*1,B2>="22:00"*1),MIN(B2-"22:00","0:10"),0))

この式では休憩時間に入った途中の時間で終了時間となるケースも考えた式となっています。
労働時間の列でＥ2セルには次の式を入力します。

=IF(C2="","",C2-D2)

通常時間のＦ2セルには次の式を入力します。

=IF(E2="","",MIN(E2,"8:00"))

割り増し時間のＧ2セルには次の式を入力します。

=IF(F2="","",IF(E2>"8:00"*1,E2-"8:00",0))

最後にＣ2セルからＧ2セルまでを選択して入力されているそれらの式を下方にオートフィルドラッグします。

お礼

今、回答いただきました数式を入れて、計算してみました。
　とっても簡単にできました。本当に感謝しています。凄いです！
　以前から、エクセルのスクールの先生をしている方にも、聞いていましたが、出来なくて、
　今回、回答を頂けるか半信半疑で、投稿しました。
　こんなに素晴しい計算式を考えて頂けるなんて、感激です。
　ありがとうございました。