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.6ベストアンサー
- 回答日時:
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も見ました。最初の回答の時に見ればよかったと後悔しました。
ありがとうございました。
No.5
- 回答日時:
原因はエクセルの誤差です。
極端な話ですが、例えば、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
- 回答日時:
「No.2の回答に寄せられた補足コメント」へのコメント、
「この方法」を設定したワークシートの任意のセルを選択して Ctrl+; 「エイヤッ!」と叩き付けたらどうなります?
そう、本日の日付「2018/4/15」が入力されますよね?
貴方の周囲の人の設定に合わせるために“1904年から計算する”のチェックを外してみてください。あら!「2018/4/15」が「2014/4/14」になったでしょ?
ビックリポンで混乱しない?貴方は経緯をご存じだけど、周囲の人はパニックに陥るかも。
詳しい説明は省略するけど、4年に一度の閏年に関係なきにしも非ずの問題なのです。
クワバラ、クワバラ・・・
No.3
- 回答日時:
No.1の回答者さんと同じことですが。
こちら↓のような書き方もあります。
=TEXT(ABS(E1-C1),IF(E1>C1,"-h:mm","h:mm"))
参考URL
https://kokodane.com/2013_waza_059.htm
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel2007での条件付き書式について 6 2023/05/02 10:56
- その他(Microsoft Office) エクセル 条件付き書式 日をまたぐ塗りつぶし 1 2023/01/13 18:00
- その他(Microsoft Office) Excel 勤務時間の計算について~(残業)有.無しの場合。 4 2022/07/17 21:03
- Excel(エクセル) セルに特定の色が出た時だけ、式を発動させたい 4 2022/06/17 10:32
- Excel(エクセル) 至急 Excel日付表示設定教えてください Excelの日付表示ですが、他者から送られてくるExce 6 2023/02/03 11:01
- ビジネスマナー・ビジネス文書 内定先に送るメールについて 3 2023/03/07 19:57
- その他(Microsoft Office) outlook vba 予定表 3 2022/11/20 14:51
- エアコン・クーラー・冷暖房機 日立のエアコンのリモコン 5 2023/04/01 00:25
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) 指定した値以上の中で最小値を出したい 7 2022/10/24 21:12
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
EXCELで○ヶ月を○年○ヶ月に変換...
-
リンク元の日付が空白の時リン...
-
Excelのセルに数字を入れるが、...
-
Excel2013 表示形式で「1,000...
-
斜線が入ったセルのカウント
-
【エクセル】数値にスラッシュ...
-
EXCELからCSVへ変換すると0が...
-
Excelで「20080707」を『2007/0...
-
エクセルで9と入力したら時刻...
-
EXCELで勝手に約分されてしまう
-
エクセルでアポストロフィーが...
-
西暦から、曜日を表示⇒何も入力...
-
Excelのセルの書式設定(100...
-
EXCEL (0.5)の表示の仕方
-
エクセル 4ケタの数字を年月に...
-
エクセルで生年月日の数字のみ...
-
エクセルで時間(○時△分)を入...
-
エクセルの曜日表示が正しく表...
-
Excelで日付を数字8桁で入力、...
-
末尾にPを付けたい
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
EXCELで○ヶ月を○年○ヶ月に変換...
-
リンク元の日付が空白の時リン...
-
エクセルの曜日表示が正しく表...
-
【エクセル】数値にスラッシュ...
-
Excel2013 表示形式で「1,000...
-
Excelで「20080707」を『2007/0...
-
Excelのセルに数字を入れるが、...
-
エクセルでアポストロフィーが...
-
斜線が入ったセルのカウント
-
EXCELからCSVへ変換すると0が...
-
エクセルで日付を全角にしたい
-
西暦から、曜日を表示⇒何も入力...
-
エクセルで9と入力したら時刻...
-
EXCELで勝手に約分されてしまう
-
エクセル 4ケタの数字を年月に...
-
Excel 時間の表示形式 セルと...
-
Excelのセルの書式設定(100...
-
EXCEL (0.5)の表示の仕方
-
エクセル 日付の表示を200...
-
Excel時間の入力方法
おすすめ情報
ご回答ありがとうございます。
早速実行したところうまくいきました。
ところで、「この方法、私は賛成しない」のはなぜだか教えていただけませんか?何か問題があるのかと心配になりますので。
質問の際に示した表ではうまくいきました。ところが、実際に使っている表では、終了予定時刻-終了実績時刻=0の時に0と表示されたり、-0と表示され、困っています。
実際の表は下記のとおりです。なお、(i)は4以上の行番号です。
B列 開始予定時刻(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))
このN列が先にお聞きしたものです。(”終了予定時刻-終了実績時刻”といっていました)。
実際に使っている表では、”所要時間の実績と予定の差”です。
質問の際に示した表ではうまくいきました。ところが、実際に使っている表では、終了予定時刻-終了実績時刻=0の時に0と表示されたり、-0と表示され、困っています。
実際の表は下記のとおりです。なお、(i)は4以上の行番号です。
B列 開始予定時刻(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))
このN列が先にお聞きしたものです。(”終了予定時刻-終了実績時刻”といっていました)。
実際に使っている表では、”所要時間の実績と予定の差”です。