エクセル 夜勤対応のタイムカードの関数について

夕方から始まり、朝方に終わる夜勤の現場のタイムカードをエクセルで作成していますが、計算結果がおかしい箇所があるので詳しい方知恵をお貸しください。
シートは
A1=日付
B1=出勤時刻(24時間表記)
C1=退勤時刻(24時間表記)
D1=休憩時間(数値)・・0.25,0.5,0,75,1.00など
E1=拘束時間(数値)
F1=労働時間(数値)
G1=日勤労働時間(数値)・・・22:00から翌29:00間の労働以外でかつ、8時間以内の労働時間
H1=普通残業時間(数値)・・・22:00から翌29:00間の労働以外でかつ、8時間を超えた労働時間
I1=深夜労働時間(数値)・・・22:00から翌29:00間の労働でかつ、8時間以内の労働時間
J1=深夜残業時間(数値)・・・22:00から翌29:00間の労働でかつ、8時間を超えた労働時間
となっていまして、
BCD列2行目からは時間を入力していきます。
※休憩時間はすべて深夜帯の時間から差し引く設定です。

その他の列は関数を入力していまして、
E列2行目からの関数=IF(COUNT(B2:C2)<2,"",(C2-B2)*24)
F列2行目からの関数=IF(COUNT(B2:C2)<2,"",E2-D2)
G列2行目からの関数=IF(COUNT(B2:C2)<2,"",(MAX(0,MIN("8:00",MIN(C2,"22:00")-MAX(B2,"5:00"))*24)))
H列2行目からの関数=IF(COUNT(B2:C2)<2,"",(MAX(0,MIN(C2,"22:00")-B2)+MAX(0,C2-"29:00"))*24-G2)
I列2行目からの関数=IF(COUNT(B2:C2)<2,"",F2-G2-H2-J2)
J列2行目からの関数=IF(COUNT(B2:C2)<2,"",MAX(0,F2-8-H2))
以下オートフィルで入力しているのですが、

ここから質問の本題で、
夕方ではなく夜に出勤してくるスタッフを採用する場合もあり、例えば、

出勤22:00退勤29:15休憩0の場合だと→日勤労働0.25(29:00を超えた分)、普通残業0.00(全体で7.25のため)、深夜労働7.00、深夜残業0.00が正解なのですが、
日勤労働0.00、普通残業0.25(???)、深夜労働7.00、深夜残業0.00と29:00を超えた分が普通残業になってしまいます。

これをどう解消すればいいのか途方に暮れていまして、分かる方がいらっしゃれば教えて頂きたいです。宜しくお願い致します。

ちなみに、
出勤18:00退勤29:00休憩1.00の場合や
出勤15:00退勤23:00休憩0.00の場合だと
計算は正常になります。

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

• 

  皆様いろいろなアドバイスを頂き本当にありがとうございます。
  アドバイスを参考に別列を設けて更に休憩時間をどの時間帯でとるかも加味してシートを作り直してみました。私自身エクセルを触りだして数カ月のひよっこでどれが不要か必要かもちんぷんかんぷんなので完成したシートの何が間違っているか具体的に教えて頂けたら嬉しいです。
  
  作り直したシート
  A1=日付
  B1=出勤時刻
  C1=退勤時刻
  D1=休憩時間(日勤帯)・・NEW
  E1=休憩時間(深夜帯)・・NEW
  F1=休憩時間(29時以降から退勤まで)・・NEW
  G1=拘束時間
  2行目関数→=IF(COUNT(B2:C2)<2,"",(C2-B2)*24)
  H1=労働時間
  2行目関数→=IF(COUNT(B2:C2)<2,"",G2-SUM(D2:F2))
  つづく

    補足日時：2019/04/11 09:58

• 

  つづき
  I1=日勤労働時間
  2行目関数→=M2+O2
  J1=普通残業時間
  2行目関数→=N2+P2
  K1=深夜労働時間
  2行目関数→=IF(R2=0,0,IF((R2-L2-E2)<0,0,R2-L2-E2))
  L1=深夜残業時間
  2行目関数→=IF(M2=0,0,IF(M2>=8,R2-E2,IF(R2-(8-Q2)-E2<0,0,R2-(8-Q2-E2))))
  M1=日勤実働時間・・NEW
  2行目関数→=IF(Q2=0,0,MIN(8,Q2-D2))
  N1=日勤残業時間・・NEW
  2行目関数→=IF(Q2=0,0,Q2-M2-D2)

    補足日時：2019/04/11 09:58

• 

  つづき２
  O1=29時～実働時間・・NEW
  2行目関数→=IF(S2=0,0,S2-F2-P2)
  P1=29時～残業時間・・NEW
  2行目関数→=IF(K2=0,0,IF((K2+(S2-F2))>=8,K2+(S2-F2)-8,IF(H2>=8,S2-F2,0)))
  Q1=日勤拘束時間・・NEW
  2行目関数→=IF(COUNT(B2:C2)<2,0,IF(B2*24<22,IF(C2*24<22,(C2-B2)*24,22-B2*24),0))
  R1=夜勤拘束時間・・NEW
  2行目関数→=IF(COUNT(B2:C2)<2,0,(MAX(0,MIN(C2*24,29)-MAX(B2*24,22))))
  S1=29時～拘束時間・・NEW
  2行目関数→=IFERROR(IF(C2*24<29,0,C2*24-29),0)

    補足日時：2019/04/11 09:59

No.1 回答者： lv4u 回答日時： 2019/04/11 06:35

通常、エクセルで複雑な処理を行う場合、セルに式や関数をいれるだけでは難しい（ほぼ無理？！な）ことがあります。

この場合は、ExcelVBAというエクセルに内蔵されているプログラムを使って、プログラミングで対応します。

昔、質問者さんのようにタイムカード集計をエクセルでやっている会社で、勤務時間計算のプログラムの修正をやったことあります。
複雑な時間計算を行うためか、１０００行弱くらいのプログラムになっていた気がします。

No.2 回答者： grumpy_the_dwarf 回答日時： 2019/04/11 06:39

=IF(COUNT(B2:C2)<2,"" のくだりが全部無駄。

適切な数式なら表示形式 0.00;; でゼロは非表示にできます。

G列は「5時から22時までのうち最大8時間」だけで、29時から46時までが忘れられています。それが今回発覚した29:15退勤の場合の原因ですね。

8時間以内とその後で分けて計算するので、「出勤から退勤または出勤8時間後の早い方まで」と「出勤8時間後または退勤の早い方から退勤まで」の中で0時から5時までと22時から29時までを深夜時間とし、引き算で残りを通常時間とするのが手っ取り早いと思います。列の並ぶ順番と計算する時に考える順番は違ってていいんです。

No.3 回答者： fakeflake 回答日時： 2019/04/11 08:52

総稼働時間も日勤と深夜で分けた方が良くないですか？そうすれば日勤総稼働または深夜総稼働どっちかがゼロと言う判定で、無関係な式が動くのを止めやすいです。

また深夜労働には早朝出勤もあり得るので、7時以前（要は29時以前）の出勤は深夜扱いになるし、20時出勤で33時(朝9時)までいた場合は残業が深夜と通常の両方にまたがりますが、今の式にはその配慮がないです。そのような出勤がなければいいんですけど。これを考慮するとしないでは、式の複雑さが大幅に違ってきます。

No.4 回答者： 銀鱗 回答日時： 2019/04/11 09:09

夜勤組みの場合の計算を別に設ける必要がある。

そんだけですよ。

もう一度条件を整理して考え直すことを勧めます。
たぶんそれだけで解決するんじゃないかな。

夜勤組みかどうかは出勤時間で判断するなど工夫しましょう。

・・・
あと、数式がめんどうなくらい長くなるので、別途作業列（K列以降）を設けることを勧めます。
日勤の場合の計算と、夜勤の場合の計算をどちらも作業列で行い、
出勤時間などで日勤か夜勤かを判断し、作業列で計算させた値を拾ってくるようにしてはいかがでしょう。
このほうが意図しない結果になったときの修正が容易に行えるというメリットがあります。