エクセル 数式を教えてください！

エクセルの数式を教えてください！

エクセルでタイムカードを作成しているのですが、休憩時間が2枠ある場合(①通常時間帯②深夜時間帯)の各時間(拘束時間・労働時間・日勤時間・通常残業・深夜
時間・深夜残業)の算出数式が分かりません。

画像のように、休憩時間は①通常時間帯と②深夜時間帯(22:00～5:00)の2枠あり、それぞれ合計時間を数値で入力します。
各時間帯の休憩時間がそれぞれ、
・拘束時間・労働時間・日勤時間・通常残業・深夜時間・深夜残業に反映させるようにしたいのですが、休憩が2枠あるため数式がうまくいきません。。

深夜帯(深夜時間・深夜残業)は22:00～5:00で、1日の労働時間が8時間を超えた場合に残業となります。

画像のように、【拘束時間・労働時間・日勤時間・通常残業・深夜時間・深夜残業】に適切な数値（出勤退勤はh:mmで入力し、その他は15分=0.25単位の数値)で計算されるようにしたいです！

賢者の皆様、どうか宜しくお願い致します！

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

• 

  例えば、出勤時間(B列)=23:00、退勤時間(C列)=9:00、日勤帯休憩時間(D列)=0.5、深夜帯休憩時間(E列)=1.00だった場合、
  ・拘束時間(F列)は23:00〜9:00で10.00
  ・労働時間(G列)は10.00引くことの休憩時間(日勤帯・深夜帯合計)で8.50
  ・日勤時間(H列)は出勤時間23:00から8時間以内で、かつ朝5:00を超えてからの時間に日勤帯休憩時間をひいて2.50
  ・残業時間(I列)は出勤時間23:00から8時間を超えて、かつ深夜帯(22:00〜5:00)ではない労働時間なので1.00
  ・深夜時間(J列)は出勤時間23:00から8時間以内で、かつ朝5:00までの時間に深夜帯休憩時間をひいて5.00
  ・深夜残業時間(K列)は出勤時間23:00から8時間を超えて、かつ深夜帯(22:00〜5:00)の間の労働時間なので、0.00
  となるようにしたいです！

    補足日時：2017/08/08 08:30

• 現在は、15分を0.25と数値で計算させる方法がわからないので、出勤退勤はもちろん休憩時間もすべて0:30や1:00とh:mmで入力し、計算結果もh:mmで出る関数が入っています。
  
  拘束時間F4=IF(COUNT(B4:C4)<>2,"",IF(C4<B4,C4+1,C4)-B4)
  G4=IF(COUNT(B4:C4)<>2,"",F4-SUM(D4:E4))
  労働時間H4=IF(COUNT(B4:C4)<>2,"",MAX(0,MIN(IF(C4<B4,C4+1,C4),"22:00")-B4)+MAX(0,IF(C4<B4,C4+1,C4)-MIN(IF(C4<B4,C4+1,C4),"29:00"))-SUM(D4:E4))

    補足日時：2017/08/08 12:09

• 上の補足間違いました。
  F4拘束時間
  G4労働時間
  H4日勤時間
  です。
  
  残業時間I4=IF(COUNT(B4:C4)<>2,"",MAX(0,MIN(IF(C4<B4,C4+1,C4),"22:00")-(B4+D4+"8:00"))+MAX(0,IF(C4<B4,C4+1,C4)-MAX(B4+"8:00"+SUM(D4:E4),"29:00")))
  深夜時間J4=IF(COUNT(B4:C4)<>2,"",MAX(0,MIN(IF(C4<B4,C4+1,C4),"29:00")-MAX(B4,"22:00")-E4))
  深夜残業時間K4=IF(COUNT(B4:C4)<>2,"",MAX(0,MIN(IF(C4<B4,C4+1,C4),"29:00")-MAX(B4+"8:00"+SUM(D4:E4),"22:00")))

    補足日時：2017/08/08 12:14

• 上記の数式を入れているのですが、
  出勤23:00、退勤9:00(入力は33:00)、日勤帯休憩0:30、深夜帯休憩1:00の例で行くと、正しい計算結果が拘束=10:00、労働=8:30、日勤=2:30、残業=1:00、深夜時間=5:00、深夜残業=0:00のはずが、
  拘束=10:00、労働=8:30、日勤=2:30、残業=0:30、深夜時間=5:00、深夜残業=0:00と、残業時間がズレてしまいます。
  そこの原因が分かりません(Ｔ＿Ｔ)

    補足日時：2017/08/08 12:22

• 出勤12:00、退勤22:00、日勤帯休憩1:00、深夜帯休憩0:00の例で行くと、正しい計算結果が拘束=10:00、労働=9:00、日勤=8:00、残業=1:00、深夜時間=0:00、深夜残業=0:00のはずが、
  拘束=10:00、労働=9:00、日勤=9:00、残業=1:00、深夜時間=0:00、深夜残業=0:00と、次は日勤時間がズレてしまいます。
  ズレると言うよりも日勤＋残業=労働時間となっていません(Ｔ＿Ｔ)

    補足日時：2017/08/08 12:26

• 

  ☆数式に問題があると思うので、その問題を見つけて頂きたいのと、
  ☆そもそもの計算結果をh:mm形式ではなく、0:15=0.25、0:30=0.50、1:45=1.75と数値で計算されるような方法を教えてくださいませ。。この場合出勤・退勤はh:mmで、日勤帯休憩・深夜帯休憩は0.25単位の数値で入力します。

    補足日時：2017/08/08 12:30

No.1 回答者： chonami 回答日時： 2017/08/08 07:47

名前が違いますが、何度か質問されてますよね？なぜ回答がつかないかわかりますか？

項目名の説明だけされても、何をどう計算したいのか回答側は全く理解できないからです。
せめて手計算ならどうするのか位は書いてもらわないと表だけ載せられても困ります。
この項目はここからここを引いて～というような説明をしてください。

また、「うまくいきません」という表現は何がダメなのかわからず一番たちが悪いです。
こういう結果が欲しいからこうしたら、こうなりますという具体的な内容を書いて下さい。
それがなければ何度投稿しても同じです。

No.2 回答者： 銀鱗 回答日時： 2017/08/08 09:09

例：

　定時8:30～17:30
　休憩時間12:00～13:00
　8:30出社
　17:50退社
　とする。

　午前中の定時内の勤務時間と
　午後の定時内の勤務時間を
　それぞれ別に計算する。

　＝MAX(出社時間,"8:30")
これで8時30分より遅れて出社した場合は出社時間の基準がその時間になる。
8時30分以前に出社した場合は定時内に関して8時30分が基準になる。

　＝MIN(退社時間,"12:00")
これで12時前に退社した場合はその時間が有効になる。
12時以降に退社した場合は12時が有効になる。

　＝MIN(退社時間,"12:00")-MAX(出社時間,"8:30")
これでとりあえず午前中の勤務時間の計算ができる。

こういう考え方で計算すればいい。

15分単位に切り上げた時間にしたいのであれば、CEILING関数を使うと良い。（切り上げだよ。切り捨てではない）
　＝CEILING(MIN(退社時間,"12:00")-MAX(出社時間,"8:30"),"0:15")
こうなる。

あとは同じ考え方でよい。
エラーは出るだろうが、それは自身で解決してください。
（出社時間が12時以降。退社時間が午前0時を過ぎている。など）

※時間をダブルクォーテーション（”）で囲むと文字列としてExcelは認識します。
　そして数値として置き換えることのできる文字列は計算時に数値に置き換えてくれます。
　上記の数式ではその性質を利用して、分かりにくいシリアル値表記を回避しています。


・・・余談・・・
「タイムカード」を作っているのではなく、
「タイムカードの管理表」を作っているのでしょ。

あと、丸投げ(/・ω・)/⌒〇はマナーが悪いですよ。

No.3 回答者： fujillin 回答日時： 2017/08/08 09:59

こんにちは

https://oshiete.goo.ne.jp/qa/9864848.html
に回答の半分は既にでていると思います。

残りの部分は、一発で求めるのが難しければ、作業例を作成して深夜にかかる部分とそうでない部分を分割するなどしながら、最後にまとめて集計するような考え方をすれば、大分簡単になると思います。
（もちろん、一発で求めてもよいですが・・）

No.4 ベストアンサー 回答者： fujillin 回答日時： 2017/08/08 12:32

ANo3です。

＞15分を0.25と数値で計算させる方法がわからないので
シリアル値は1日が1.0となるように計算しています。

シリアル値の時間をTとするなら
　=HOUR(T)+MINUTE(T)/60
とすることで、時間単位（15分は0.25時間）に該当する値を得られます。
例えば、3：15 → 3.25 となります。

上記の1日=1.0(1日=24時間）を利用すれば、もっと簡単に
　=T*24
とすることでも、同様の結果を得ることができます。

※どちらの場合も、表示書式は「数値」や「標準」としておく必要があります。