エクセルで勤務時間の計算をしたい。

質問

もしかしたら同じような質問があったかもしれませんが
探せなかったのでお訊ねします。

勤務時間　8:30～17:30
休憩時間　9:00～9:15　12:00～13:00　15:00～15:15　17:30～17:45
の場合、単純に休憩時間を
0:15　1:00　0:15　0:15　と決めてマイナスするのではなく

8:30～17:30　のうち
9:00～9:15　12:00～13:00　15:00～15:15　17:30～17:45
に該当する時間は休憩時間

というような計算はできないでしょうか？
個人によって勤務時間も休憩時間もまちまちなので
遅刻・早退や早出・残業でこの時間帯にはまってしまうとき
15分　60分　などと休憩時間を定数にしてしまうととても不便です。
ちなみに１sheetに25人分くらいのデータを入力します。

私が言いたい事はわかっていただけるでしょうか。
どなたか解りやすく教えて下さい。お願いします。

回答No.3

sige1701 · ベストアンサー

すいませんG列が抜けていましたね
　　　A　　　B　　　C　　　D　　　E　　　F　　　　G
1　　8:30　17:30　1:30　　　　　9:00　9:15　0:15
2　　　　　　　　　　　　　　　　 12:00　13:00　1:00
3　　　　　　　　　　　　　　　　 15:00　15:15　0:15
4　　　　　　　　　　　　　　　　 17:30　17:45　0:15

E1:G4に対応表を作成
C1=SUMPRODUCT(($E$1:$E$4>=A1)*($F$1:$F$4<=B1),$G$1:$G$4)

補足

お手数掛けてすみません。
今日は時間が無かったので明日ちょっとやってみようと思います。

回答No.2

sige1701

A　　　B　　　C　　　D　　　E　　　F
1　　8:30　17:30　1:30　　　　　9:00　9:15
2　　　　　　　　　　　　　　　　 12:00　13:00
3　　　　　　　　　　　　　　　　 15:00　15:15
4　　　　　　　　　　　　　　　　 17:30　17:45
E1:F4に対応表を作成
C1=SUMPRODUCT(($E$1:$E$4>=A1)*($F$1:$F$4<=B1),$G$1:$G$4)

補足

回答ありがとうございます。

上記のとおりにやってみたのですが　G1:G4　は空白でいいのですか？

回答No.1

mshr1962

休憩時間の開始をセルに登録
A1=8:30 (通常始業時間)
A2=9:00
A3=12:00
A4=15:00
A5=17:30（通常終業時間の意味も含める）

D1=始業、E1=終業として
勤務時間=IF(OR(D1="",E1="",E1<D1),"",MAX(MIN(E1,$A$5),$A$1)-MAX(D1,$A$1)-(AND($A$2>D1,$A$2<E1)+AND($A$3>D1,$A$3<E1)*4+AND($A$4>D1,$A$4<E1))*"0:15")
早出=IF(OR(D1="",E1="",E1<D1),"",MIN(E1,$A$1)-MIN(D1,$A$1))
残業=IF(OR(D1="",E1="",E1<D1),"",MAX(MAX(E1,$A$5)-MAX(D1,$A$5+"0:15"),0))

補足

早速のお返事ありがとうございます。

個人によってはA1～A5まで使わない又はA6以上のセルが必要な場合があります。
例えばＡさんは、
A1=9:45（通常始業時間）
A2=12:00（休憩1時間）
A3=14:15（通常終業時間）

Ｂさんは
A1=9:00（早出の場合の休憩時間）
A2=9:15（通常始業時間）
A3=12:00（休憩1時間）
A4=14:15（通常終業時間）
A5=15:00（残業の場合の休憩時間）
A6=17:30（残業の場合の休憩時間）

というような感じで全部で18通りあるんですが
このような場合はどうすればよいでしょうか？