休憩時間の計算

パートの方の給与計算で休憩時間の計算式がうまくいきません
タイムカードどおりの時間をエクセルの表に入力して自動計算させようと思っているのですが、
例①：入18：17　出18：22　0：05
例②：入21：55　出22：05　0：10
例③：入22：28　出22：38　0：10
こんな感じの休憩のとり方です、22：00を過ぎた場合は時給額が変わりますので特に難しく
よろしくお願いします。

質問者からの補足コメント

• 説明不足ですみません。より具体的に再度質問させて頂きます。
  Ａ1出勤時間、Ｂ1退勤時間、Ｃ1休憩①入時間、Ｄ1休憩①終了時間、Ｅ1休憩②入時間、Ｆ1休憩②終了時間、G1・22時迄の休憩時間、H1・22時迄の勤務時間、I1・22時後の休憩時間、J1・22時後の勤務時間
  勤怠パターン例Ⅰ17：00～21：00、Ⅱ17：00～23：00、Ⅲ22：00～24：00勤怠は15分単位です
  休憩パターン例Ⅰ18：00～18：10、Ⅱ21：55～22：05、Ⅲ22：30～22：40休憩は2回とりますが時間はまちまちです。
  H1にはG1をJ1にはI1をそれぞれ引いた時間を出したいのですが、宜しくお願いします。

    補足日時：2017/01/26 12:45

No.4 ベストアンサー 回答者： yuji3690 回答日時： 2017/01/26 14:08

A~Fは入力でG~Jを自動計算したい。

というわけですね。
そして休憩は2回あると。

休憩は必ず2回あるのでしょうか？
22時より前に2回とか、22時より後に2回とかのパターンもあるのでしょうか？
（まぁこれらは空白でも0と計算されると思いますので、問題ないかと）
あと24時以降の場合計算がおかしくなる可能性もあります。仮に0:10の場合、24:10と入力すればおそらく大丈夫かと。
24時越えの可能性も分からないのでそのままにしてますが、必要があれば対処します。
同様に24時越えてからの出勤（早出含む）も考慮していません。
これについては何時からが通常勤務か分かりませんので、必要であるならその情報も合わせてお願いします。
また入力ミスにより、勤務時間外に休憩時間をとっている場合や、休憩時間の①②が重複している場合、入力が抜けている場合、出勤時間より退勤時間の方が早い場合、等についても正確に入力されているものとして計算されてしまいますので、そちらのミスについても判定が必要であるならば、入力が適切であるか判定する用にK列等で別途式を作るのがよろしいかと思います。

とりあえずやってみましょう。
G1：22時までの休憩時間
まずD1(①終了)が22時以前かどうか判断し、以前ならC1～D1の時間を計上、D1が22時越えの場合、C1(①開始)が22時以降かどうか判断し、以降なら0を計上、22時より前ならC1~22時の時間を計上。
同様にF1(②終了)とE1(②開始)でも判断し計上。
これらを合計したものをG1に表示する。
=IF(D1>22/24,IF(C1<22/24,22/24-C1,0),D1-C1)+IF(F1>22/24,IF(E1<22/24,22/24-E1,0),F1-E1)

H1：22時までの勤務時間（休憩時間を除く）
B1(退勤)が22時以前の場合、A1~B1の時間を計上。B1が22時越えの場合、A1(出勤)も22時以降の場合、0を計上。A1は22時より前の場合、A1~22時の時間を計上。
これから休憩時間=G1を除いたものをH1に表示する。
=IF(B1>22/24,IF(A1<22/24,22/24-A1,0),B1-A1)-G1

I1：22時以降の休憩時間
まずC1(①開始)が22時以降かどうか判断し、以降ならC1～D1の時間を計上、C1が22時より前の場合、D1(①終了)が22時以前かどうか判断し、以前なら0を計上、22時より後なら22時~D1の時間を計上。
同様にE1(②開始)とF1(②終了)でも判断し計上。
これらを合計したものをI1に表示する。
=IF(C1<22/24,IF(D1>22/24,D1-22/24,0),D1-C1)+IF(E1<22/24,IF(F1>22/24,F1-22/24,0),F1-E1)

J1：22時以降の勤務時間（休憩時間を除く）
A1(出勤)が22時以降の場合、A1~B1の時間を計上。A1が22時より前の場合、B1(退勤)も22時以前の場合、0を計上。B1は22時より後の場合、22時～B1の時間を計上。
これから休憩時間=I1を除いたものをJ1に表示する。
=IF(A1<22/24,IF(B1>22/24,B1-22/24,0),B1-A1)-I1

これらを必要な行数分コピーしてください。
ざっと作ったので実際にデータを入力して、ミスが無いか確かめてもらえればと思います。

この回答へのお礼

ご回答頂いた皆様ありがとうございました、特にyuji3690さんおかげで仕事量の軽減につながりました。

お礼日時：2017/01/28 11:43

No.3 回答者： yuji3690 回答日時： 2017/01/23 20:02

やりたいことがいまいち分かりません。

入の時刻が入ったセルを「入」
出の時刻が入ったセルを「出」と表記しますが、

出と入の時間で単に休憩時間を計算したいだけなら、休憩時間を表示させたいセルに
=「出」-「入」
と入れれば計算してくれます。（それぞれセルを指定するのですよ。A1とかB2とか）

それが22時より前か後ろかで分けたいならば、
22時より前の休憩時間を表示するセルに
=IF(「入」<22/24,IF(「出」>22/24,22/24-「入」,「出」-「入」),””)
これで、入が22時より前で出が22時より後②なら入から22時までの時間が表示され、
入が22時より前で出も22時以前①なら入から出までの時間が表示され、
入が22時以降③なら空白が表示されます。
””を0とすれば0:00と表示させることもできます。

22時より後の休憩時間を表示するセルには
=IF(「出」>22/24,IF(「入」<22/24,「出」-22/24,「出」-「入」),””)
これで、出が22時より後で入が22時より前②なら22時から出までの時間が表示され、
出が22時より後で入も22時以後③なら入から出までの時間が表示され、
出が22時以前①なら空白が表示されます。

やりたい事と説明が違っているようであれば、補足の情報を提示してください。

No.2 回答者： tatsu99 回答日時： 2017/01/23 18:56

セルの状態が添付の図のような状態だとすると

C2へ
＝B2-A2
と設定し、オートフィルで下へコピーします。

No.1 回答者： angkor_h 回答日時： 2017/01/23 18:52

「入」「出」とは、休憩時間の開始と終了であり、

また就業の終了時間と開始(再開)時間で良いでしょうか。

22時前、22時以降を別に計算されれば良いと思います。
終業時間ではなく休憩時間の計算であれば、
22時前の休憩時間=MIN(出時間、22)-MIN(入時間、22時)
22時後の休憩時間=MAX(出時間、22)-MAX(入時間、22時)

時給計算であれば、同様に終業時間を計算できます。