EXCELを使った予定表で、予定と実績の差を計算したいが、差が負になると答えが得られない。

EXCELを使った予定表です。
各作業の終了時刻の予定と実績の差を得たいのですが、計算値が負になる（予定より時間がかかった場合）場合は “＃＃＃＃＃”（“負であるか大きすぎる日付及び時間は＃＃＃＃＃と表示されます”）となり、答えが表示されません。
なお、セルの書式設定の表示形式において、分類は時刻、種類は13：30(例示)としています。

答えを得る方法（予定より時間がかかった場合には－符号をつけて）を教えてください。
ちなみに　実際の表のイメージは下の通りです。

C列　終了予定時刻 E列　終了実績時刻　　F列　作業名　　
G列　終了予定時刻-終了実績時刻

C1 7:30 　E1 7:30 　 F1 準備　　G1 0:00
C2 7:45 　 E2 7:40 　 F2 計画　 G2 0:05　
C3 8:35　 E3 8:40 　F3 会議　 G3 ＃＃＃＃

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

• ご回答ありがとうございます。
  早速実行したところうまくいきました。
  ところで、「この方法、私は賛成しない」のはなぜだか教えていただけませんか？何か問題があるのかと心配になりますので。

  No.2の回答に寄せられた補足コメントです。 補足日時：2018/04/14 21:03

• 質問の際に示した表ではうまくいきました。ところが、実際に使っている表では、終了予定時刻-終了実績時刻＝０の時に０と表示されたり、－0と表示され、困っています。
  　実際の表は下記のとおりです。なお、(i)は4以上の行番号です。
  B列　開始予定時刻（Ｂ(i)＝D(i-1)　ただし、B(4)に限り手入力)
  D列　終了予定時刻（D(i)＝B(i)+H(i))
  E列　終了実績時刻（手入力する。計算で得られた値ではない)　
  F列　作業名　H列　i行の作業の予定所要時間（手入力する。計算で得られた値ではない。）　
  I列　i行の作業の実績所要時間(E(i)-B(i))
  N列　所要時間の実績と予定の差（N(i)=I(i)-H(i))
  　このＮ列が先にお聞きしたものです。（”終了予定時刻-終了実績時刻”といっていました）。　
  　実際に使っている表では、”所要時間の実績と予定の差”です。

  No.1の回答に寄せられた補足コメントです。 補足日時：2018/04/18 03:55

• 質問の際に示した表ではうまくいきました。ところが、実際に使っている表では、終了予定時刻-終了実績時刻＝０の時に０と表示されたり、－0と表示され、困っています。
  　実際の表は下記のとおりです。なお、(i)は4以上の行番号です。
  B列　開始予定時刻（Ｂ(i)＝D(i-1)　ただし、B(4)に限り手入力)
  D列　終了予定時刻（D(i)＝B(i)+H(i))
  E列　終了実績時刻（手入力する。計算で得られた値ではない)　
  F列　作業名　H列　i行の作業の予定所要時間（手入力する。計算で得られた値ではない。）　
  I列　i行の作業の実績所要時間(E(i)-B(i))
  N列　所要時間の実績と予定の差（N(i)=I(i)-H(i))
  　このＮ列が先にお聞きしたものです。（”終了予定時刻-終了実績時刻”といっていました）。　
  　実際に使っている表では、”所要時間の実績と予定の差”です。

  No.3の回答に寄せられた補足コメントです。 補足日時：2018/04/18 04:01

No.6 ベストアンサー 回答者： enunokokoro 回答日時： 2018/04/18 09:53

No.3の回答者です。

No.5の回答者さんが書いているように、手入力した時刻と計算した
時刻の結果との誤差ですね。
誤差を最小にする方法としては幾つかありますが、N列の誤差程度
なら以下の数式でもよいかもしれませんね。

　=TEXT(ABS(I4-H4),IF(H4>TIMEVALUE(TEXT(I4,"h:mm:ss")),"-h:mm","h:mm"))

本当は、時刻の計算をしているすべての列で修正をすべきですが、
マイナスや0の表示になるセルがN列だけなので、上記の数式で対応
できると思います。

この回答へのお礼

=TEXT(ABS(I4-H4),IF(H4>TIMEVALUE(TEXT(I4,"h:mm:ss")),"-h:mm","h:mm"))
を所要時間差の欄にコピーすることでうまくいきました。
　最初は教えられた式を自分の実際に使っている表に適用すると、自分の操作ミスだと思いますが、うまくいきませんでした。なんだかんだと試行錯誤しているうちに、うまくいくようになりました。参考URLも見ました。最初の回答の時に見ればよかったと後悔しました。
ありがとうございました。

お礼日時：2018/04/19 19:42

No.7 回答者： GooUserラック 回答日時： 2018/04/18 10:35

No.5 の補足です。

エクセルの誤差の原因ですが、日時の基本単位は１日なのです。
なので１時間でも「0.04166666666…」となり正確な値を持てないのです。

この回答へのお礼

GooUserラック様
なるほど、初心者の私にもよくわかります。
ありがとうございました。

お礼日時：2018/04/19 18:51

No.5 回答者： GooUserラック 回答日時： 2018/04/18 04:29

原因はエクセルの誤差です。

極端な話ですが、例えば、C1セルに「7:00:00」D1セルに「7:00:00.01」と入力してみてください。数式バーを見てもどちらも「7:00:00」と表示されていて実際の値は違うけど同じに見えてしまうためこのようになります。
非常に面倒ですがとりあえず見た目での比較してみれば以下のような感じで可能だと思います。
「=IF(DATEVALUE(TEXT(C1,"h:mm"))>=DATEVALUE(TEXT(D1,"h:mm")),TEXT(DATEVALUE(TEXT(C1,"h:mm"))-DATEVALUE(TEXT(D1,"h:mm")),"h:mm"),"-"&TEXT(DATEVALUE(TEXT(D1,"h:mm"))-DATEVALUE(TEXT(C1,"h:mm")),"h:mm"))」

No.4 回答者： mike_g 回答日時： 2018/04/14 23:56

「No.2の回答に寄せられた補足コメント」へのコメント、

「この方法」を設定したワークシートの任意のセルを選択して Ctrl+; 「エイヤッ！」と叩き付けたらどうなります？
そう、本日の日付「2018/4/15」が入力されますよね？

貴方の周囲の人の設定に合わせるために“1904年から計算する”のチェックを外してみてください。あら！「2018/4/15」が「2014/4/14」になったでしょ？
ビックリポンで混乱しない？貴方は経緯をご存じだけど、周囲の人はパニックに陥るかも。
詳しい説明は省略するけど、４年に一度の閏年に関係なきにしも非ずの問題なのです。
クワバラ、クワバラ・・・

この回答へのお礼

mike_g様
なるほど、賛成できないわけが分かりました。
ありがとうございました。

お礼日時：2018/04/18 01:47

No.3 回答者： enunokokoro 回答日時： 2018/04/14 08:17

No.1の回答者さんと同じことですが。

こちら↓のような書き方もあります。
　=TEXT(ABS(E1-C1),IF(E1>C1,"-h:mm","h:mm"))
参考URL
https://kokodane.com/2013_waza_059.htm

No.2 回答者： mike_g 回答日時： 2018/04/14 07:16

添付図参照

イチバン簡単な方法を示しています。
でも、この方法、私は賛成しない。(*^_^*)

No.1 回答者： GooUserラック 回答日時： 2018/04/14 05:42

文字列に変換しても良いならば「=IF(C1>=D1,TEXT(C1-D1,"h:mm"),"-"&TEXT(D1-C1,"h:mm"))」で書式を右詰めにすれば良いと思います。