時間の計算 関数を教えて下さい

質問

はじめまして！

どうしても分からなくなり、皆様にお力をお借りしたいと思います。

仕事で管理している機械の停止時間とその合計を入力だけで集計をいたいのですが
いつも使っているシートが悪いのか、夜勤の24：00をまたぐ計算式が間違っているのか？
よくわかりません。

　　　A 　　　　B 　　　　C 　　　　D 　　　　　　E
1 　8:30 　　8:30 　　20:30 　　20:30　　　　0:00
2 　8:30 　　8:30 　　17:00 　　20:30 　　　3:30
3 　8:30 　　7:30 　　18:00 　　20:30 　　　1:30
4 　8:30 　　20:30 　　20:30 　　20:30 　　　12:00
5
6
7 　20:30 　　20:30 　　8:30 　　8:30 　　　0:00
8 　20:30 　　21:30 　　6:00 　　8:30 　　　3:30
9 　20:30 　　22:00 　　8:30 　　8:30 　　　1:30
10 　20:30 　　8:30 　　8:30 　　8:30 　　　　?

「基本条件」
1、シートのA1～A4　A7～A10　と　D1～D4　D7～D10　は固定した数字

2、A＝引き継ぐ時間（定時）　　B=機械の稼働時刻　　C=機械の停止時刻　　D=引き継ぐ時間（定時）　　
　　E=機械の停止時間（C-B)

3、昼の勤務時間8：30～20：30までと夜の勤務20：30～8：30までの2交代で稼動の為
　　入力を2つに分けている。

4、機械を早い時間（B3のように１時間早く稼働している）に稼働する時もある。

5、上部の内容でE1セルに入力している式は
＝IF（（D1-C1）+（B1-A1）＜0,"-","")&TEXT(ABS((D1-C1)+(B1-A1),"hh:mm")

　　この式では昼の勤務では問題なく演算してくれるのですが、夜になると全くダメです。
　　E10には”-12：00”となり、”12：00”にするように修正すると、他の時間が”-”になります。

E10を出すにあたり意味は、機械が12時間止まっています（12：00）。としたいのですが（E4のように）
何かいい式などあれば教えてください。

回答No.5

minosennin · ベストアンサー

すでに試されているかも知れませんが、

集計シートで2行目以降にコピーする際、「Ctrlキー」を押したままドラッグしてみてください。

お礼

やっぱりそうでしたか！
思い出したのが遅くて質問してから何となくしてみたら

出来た感じで。本当にありがとうございます。
これからも宜しくお願いします。

回答No.4

minosennin

B7に19：00と入力するということは、規定稼働時間12:00に対して、実際稼働時間13:30ですから、機械の停止時間はなくむしろ超過稼働ですね。

規定稼働時間12:00－実際稼働時間13:30＝－1:30
「########」が表示されるのはこのマイナスのためです。

時間形式でマイナス表示は無理のようですので、もし、十進数でよいのなら関数式全体に＊24を追加して、表示形式を数値・少数2ケタに変更すれば、「－1:30」を「-1.50」と表示することは可能です。

=(IF(A1>D1,(D1+"24:00"-A1),(D1-A1))-IF(B1>C1,(C1+"24:00"-B1),(C1-B1)))*24

補足

回答ありがとうございます。
入力してみました。100％の結果を得られました。
ありがとうございます。表現が難しいですね。伝えたい事を文章化にするのは。
しかし、知識ある皆さんのだからこんなことかな・・・で公式を考えるなんて
やはり凄いと改めて感心と尊敬します。
出来ればあとひとつの疑問を解決して頂きたいです。

一日の停止時間の合計（E1+E7）・（E2+E8)・・・をF1、F2・・・に出して
そのF1のセルの数字を違うフォルダーの集計するシートにあるセルに
自動的に飛ばして行きたいのですが・・・
同じフォルダー内のシートならセルを指定して下にコピーすればいいだけ
と思うのですが、違うフォルダーのシートのA1に　＝”時間調査”F1、と
指定して、セルを下にコピーしても”F1”の数字だけがコピーされて
望んでいるF2、F3、・・・となりません。セルの右下の十字で公式のコピーが
出来たはずなんですが・・・？どうしてですか？違うフォルダーだから？
再度、御指導お願いします。

回答No.3

minosennin

一例ですが、次の式でE10は12:00と計算されます。

=IF(A1>D1,(D1+"24:00"-A1),(D1-A1))-IF(B1>C1,(C1+"24:00"-B1),(C1-B1))

なお、関数式はこの画面からexcelへコピペできます。

お礼

ありがとうございます。
確かに12：00となりました。が、例えばB7に19：00と入力すると
（１時間半前に早目に稼働する）答えが＃＃＃＃となります。
マイナスだからと思いますが、どのように修正すればいいでしょうか？

回答No.2

suz83238

=IF((D1-C1)+(B1-A1)<0,"-","")&TEXT(ABS((D1-C1)+(B1-A1)),"hh:mm")
この式のIF文で<0のとき"-"が-12:00の-部分ですので、
単に
=TEXT(ABS((D1-C1)+(B1-A1)),"hh:mm")
としてみては？
どうもIF文の意味が分からないけど。

お礼

ご指摘ありがとうございます。
ど素人のネットで探した使えそうな式をそのまま引用しただけの
ことなので、意味がイマイチわかっていないです。
申し訳ありません。
皆さんの意見を参考に勉強していきますので、また懲りずに
御指導してください。

回答No.1

yosifuji20

それはこのデータでは8:30が本日の午前か明日の午前か識別できないからです。
何らかの方法で翌朝の場合はそのことを式であらわさないにといけません。
一番簡単なのは
=IF(B1<A1,B1-A1+"24:00:00"、B1-A1)という式を付加することです。

要するにBがAより小のときはBは翌朝ということです。
この式を適当に入れれば問題は解決すると思いますが。

お礼

詳しい説明、ありがとうございます。
関数の事を何一つ分からずで、ネットからの調べだけで入力してきました。
そんなに意味があるとは・・・勉強になります。

ついでに聞きたいのですが、この式を適当に入れれば・・・とありますが
何をどうすれば良いかがわからずで、本当に申し訳ありませんが、
あと少しの教育をお願い致します。