エクセル 時刻（時間）計算について

質問

はじめて記載させていただきます。
エクセルで時刻の計算式を作りました。
添付画像も添付しておきます。ご精査していただきご回答をお願い致します。

日中作業分は○○時間　夜間作業分は○○時間と分けたい

C3 直接入力
D３ 直接入力
E3 D3-C3+(C3>D3)
F3 E3-G3
G3 IF(COUNT(C3:D3)<2,0,MAX(MIN(D3,"8:00"+(C3<D3))-MAX(C3,"20:00")+(C3>D3),0)+IF(C3<"8:00"*1,"8:00"-C3,0))

条件
1 8:00～20：00　日中作業分
2 20:00～8:00 　夜間作業分

★下記表の通り、開始4：00　終了7：00等になった場合うまく表示（計算されません）
※いろいろためしたのですが終了時間が8:00前の場合反映しないかもしれません

■現在の式では↓のように表示（計算）になってしまいます。
開始時刻 終了時刻 　total 　日中作業 　夜間作業
　　4:00　　 7:00 　3:00　 #### 4:00

■↓このように計算表示させたい

　　開始時刻 終了時刻 total 日中作業 夜間作業
4:00 7:00 3:00 0:00 3:00

よろしくお願い致します。

回答No.2

bunjii

>★下記表の通り、開始4：00　終了7：00等になった場合うまく表示（計算されません）
数式に誤りがあるため正しい計算ができないだけです。
G3=IF(COUNT(C3:D3)<2,0,MAX(MIN(D3,"8:00"+(C3<D3))-MAX(C3,"20:00")+(C3>D3),0)+IF(C3<"8:00"*1,"8:00"-C3,0))
外側のIF関数はC列とD列に数値が入力されているか否かをチェックしていますので目的の時間計算ではありません。
内側のIF関数は次のようになっていますので夜間作業の時間数がtotalより大きくなっています。従って、日中作業の時間数が負の値となりエラー表示になります。
IF(C3<"8:00"*1,"8:00"-C3,0)
D3（終了時刻）の値に関係なく"8:00"からC3（開始時刻）を差し引いています。
次のように修正すれば正しい値を求められます。
IF(C3<"8:00"*1,MIN(D3,"8:00")-C3,0))

回答No.1

Chiquilin

自分で整理できないなら 時刻をちゃんと入力させるようにした方がいい
です。入力で楽して計算で困るほど馬鹿げたことはありません。

=MOD(D3-C3,1)

=MAX(MIN(20/24,D3+(C3>D3)*(D3*2<=1))-MAX(8/24,C3),0)
+MAX(D3*(D3*2<=1)-8/24,0)

=E3-F3

お礼

早速のご回答、ご指摘いただきありがとうございます。
ご指摘のとおり直接入力で各受注者様が日報をまとめております。
入力ミスまた確認用で作成しようと考えました。

組み込んでみました。日中時間が2倍の時間で計算表示されておりました。
参考にさせていただき修正し使用させていただきます。
助かりました。　ありがとうございます。