エクセルで時間計算（再質問）

質問

本日の朝に質問させていただいたのですが、質問が分かりにくかったため再度質問させていただきます。
以前に皆さんの回答を参考にして、大変便利に利用させていただいていますが、もっと完璧なものにバージョンアップしたいので、よろしくお願いします。
B4に開始時間、C4に終了時間を入力した場合、５時から２２時まではD4に表示させ、２２時から５時まではE4に表示させています。
例えば、
B4に１９：２０、C4に２０：３０でD4に１：１０。
B4に２２：２０、C4に２３：３０でE4に１：１０。
B4に２１：２０、C4に２２：３０でD4に０：４０、E4に０：３０。
現在使っている式は、
C1に５：００、D1に２２：００を入力。
D4に=IF(OR(B4="",C4=""),"",IF(OR(B4>=$D$1,B4<$C$1),"",IF(OR(C4>$D$1,C4<=$C$1),$D$1,C4)-B4))
E4に=IF(OR(B4="",C4=""),"",IF(OR(B4>=$D$1,B4<$C$1),"",IF(OR(C4>$D$1,C4<=$C$1),$D$1,C4)-B4))
です。
これだと、B4に４：２０、C4に５：３０でD4に０：３０、E4に０：４０になりません。
また、I4に１を入力することによって、D4に表示させないでF4に、E4に表示させないでG4に表示させたいのですが、どうしたらいいでしょうか。
よろしくお願いします。

回答No.4

gloomie · ベストアンサー

解決されたのでしょうか？
1度に一つのセルで結果を表示しようとせず、作業列などを作られた方がいいかと思います。

また、日付をまたいで勤務することはあるのでしょうか？

そうであれば1度にやろうとするとかなり長い数式になってしまうと思いますが。
長い数式は後々いろいろな意味で大変です。
(メンテナンスをしたり他の人が見たりする場合)

お礼

ありがとうございます。
まだ、解決していません。
使用するのは、私一人です。
何をどうしたら良いのか・・・・。

回答No.9

gloomie

おはようございます。

>5:00をまたぐ時間だけ表示されません。
とのことですが、前回も記述したように
D4にて2回目のIF文
IF(OR(B4>=$D$1,B4<$C$1),"",関数的にはこの場合、
「開始時間が22:00以降、もしくは5時前の場合は」空白。としています。
ですので、開始時間が0:00～4:59の場合は
終了時間が5:00以降であろうと通常の時間は計算されません。

自分的には関数を
「開始時間が22:00以降、もしくは終了時間が5時前」
に変更すればよいのではないかと思います。
そして、当てはまらなかった場合の式は
MIN($D$1,C4)-MAX($C$1,B4)にすればよいのではないでしょうか？
深夜時間はこれの応用でできるはずです。
(すべて反対ですから)

ところで前回も質問させていただいたのですが、
日付をまたいで労働することってないのでしょうか？
(数式を見る限り想定されて無いようですが)
でしたらこれは質問する際に伝えておいた方がよいと思います。
(自分は日付を超えた場合も考えていたので)

回答No.8

gloomie

すみません、午前中の回答ですが「お礼」の文中のご質問の部分を読み間違えていました。
一番最初にご提示いただいた数式に何を付け加えればよいか、ということですね(^^;

D4の式を見ますと、2番目のIF文で
「開始時間が深夜時間内に始まっている場合」と指定していますので、
B4に0:00～5:00,22:00～24:00の時間を記入するとすべて空白になりませんか？
この時間に始まっても例のように終了時間が通常時間内の場合は通常時間が発生しますよね？
また3番目のIF文で「終了時間が深夜時間内に終わっている場合」
22:00から開始時間を引いていますが、
労働時間が23:00～24:00の場合はどうでしょう？
22:00から23:00を引いていますのでマイナスになります。
エクセルではマイナスの時間は認識してくれませんのでエラーになってしまいます。

「何を加えたらよいか」ということですが、
自分は人の式に自分の考えを付け加えて説明する、
という作業が非常に苦手ですので難しいですね(^^;A

ご参考になりましたでしょうか？

お礼

何回も、ありがとうございます。
５：００をまたぐ時間だけ表示されません。
あとは、正常に表示されます。

回答No.7

gloomie

おはようございます。
ご返信が遅くなり、申し訳ありません。

>あと、今までの表だと、B4に４：２０、C4に５：３０でD4に０：３０、E4に０：４０にならず、表示されません。
とありますが、こちらではきちんと正しい結果が出ます。
どのような結果が返ってくるのでしょうか？なるべく詳しく教えてください。

また、自分の提示した式とmorito_55さんの入力された式を見直して頂いてもよろしいでしょうか？
もしかしたら入力ミスなどあるかもしれませんので・・・。

回答No.6

gloomie

ANo.4でご返答いただいてる分の補足を忘れていました。

>使用するのは、私一人です。
と書かれていますが、自分しか使用しない場合でもメンテナンスなど
あとで数式を見直す時が出てくると思います。
その際にズラズラっと長い数式だと見直す気もなくなりますよね？
分割してあり、なおかつコメントなどでどういうことをしているか残しておけば後々のためによいかと思います。

ただ、自分も人に偉そうなことを言えた立場ではありませんので、
参考程度ってことで・・・。

回答No.5

gloomie

作業列に関しては検討いただいてますか？
もし検討いただけているのでしたら・・・・

3列作業列を設けてください(例としてJ,K,L列に設けます)
J列に労働時間の合計
K列に開始時間が5:00以前の場合の残業時間
L列に就業時間が22:00以降の場合の残業時間
この列を表示したくないのであれば、表が完成した後は非表示にすればいいと思います。

J4に　=IF(B4<C4,C4-B4,1+C4-B4)
K4に　=IF(B4<$C$1,IF(C4<=$C$1,IF(B4>C4,(C4-B4)+TIMEVALUE("7:00"),C4-B4),$C$1-B4),0)
L4に　=IF(C4>$D$1,IF(B4>=$D$1,IF(B4<C4,C4-B4,(C4-B4)+TIMEVALUE("7:00")),C4-$D$1),0)

更に
D4に　=IF(I4="",J4-E4,"")
E4に　=IF(I4="",K4+L4,"")
F4に　=IF(I4=1,J4-G4,"")
G4に　=IF(I4=1,K4+L4,"")

これでお望みのものになりませんでしょうか？

お礼

ありがとうございます。
だんだん希望の表に近づいてきました。
あと、今までの表だと、B4に４：２０、C4に５：３０でD4に０：３０、E4に０：４０にならず、表示されません。
今までの計算式に何を加えたら良いのでしょうか。
よろしくお願いします。

回答No.3

BLUEPIXY

たびたびすみません。式が間違っていました。
D4
=IF(AND($C$1<=B4,B4<=$D$1),IF(AND($C$1<=C4,C4<=$D$1),C4,$D$1)-B4,IF(AND($C$1<=C4,C4<=$D$1),C4-$C$1,0))
E4
=C4-B4+IF((C4-B4)<0,"24:00:00",0)-D4
でした。

お礼

ありがとうございます。
試してみましたが、思った通りできません。
何度かやってみます。

回答No.2

BLUEPIXY

＃１ですが
すみません
A4：開始時間,B4：終了時間
C4：５～２２時の表示，D4：２２時～５時の表示
になっていました。
読みかえて下さい。

あと、ついでですが
００：００の時は、表示しないのなら、いちいちチェックするより
表示書式で
hh:mm;[赤];""
とかやれば、０の時表示されません。

回答No.1

BLUEPIXY

C4
=IF(AND($C$1<=A4,A4<=$D$1),$D$1-A4,0)+IF(AND($C$1<=B4,B4<=$D$1),B4-$C$1,0)
D4
=B4-A4+IF((B4-A4)<0,"24:00:00",0)-C4
でいいと思います。
I4の値で、表示するしないは、単にIFでくるめばよいです。
D4
=IF(I4=1,"",元の式)
F4
=IF(I4=1,元の式,"")
以下同じ