EXCEL 時刻引き算で結果が###になってしまう

Question

お世話になります。

EXCELの時間の計算方法について質問させていただきます。

下記のように1日の従業員の作業状況を求めるEXCELを作成しています。

A1・・作業時間  (表示形式"時刻")  例 3:00:00(3時間)
B1・・空き時間  (表示形式"時刻")  例 4:00:00(4時間)

C1=A1-B1でその日の従業員の空時間の多い、少ないを求めています。

C1 (表示形式"時刻") の結果がプラスの場合は良いのですがマイナスの場合は#####となってしまいます。これを防ぐ良い方法はありますでしょうか。

どなたかお知恵をお借りできませんでしょうか。
よろしくお願い致します。

環境 Windows XP SP3  EXCEL2003

keithin · Accepted Answer

こんばんは。

エクセルは負の時間数を問題なく扱えますが、ふつーの設定では表示ができません。
ツールメニューのオプションの計算方法タブで「1904年」のチェックを入れておくと、負の時間も＃＃＃にならずに表示されるようにできます。

ただし、既に生データで「日付」を記入してあるエクセルブックでこの設定を触ると、「記入済みの日付が」４年と１日ずれた日付に変わってしまうので注意が必要です。先にチェックを入れたブックに、あとから日付時刻を記入して使う分には支障ありません。
同様にチェックを入れたブックと入れてない（通常設定の）ブックを混在して使っていると、間違いの元なのでそちらも注意してください。

basic_gg · Answer

C列をコピーしてC列に挿入すると、C列が#REF!に、D列が正常値になるとおもいます。
C1=A1-B1
D1=IF(COUNT(A1:B1)<>2,"",IF(C1<0,TEXT(ABS(C1),"-"&"h：mm：ｓｓ"),TEXT(C1,"h:mm：ｓｓ")))
にして、下にオートフィルします。
もちろんC列には"####"が表示されますが、D列は希望通りに表示されます。
C10=SUM(C1:C9)
D10=IF(C10="","",IF(C10<0,TEXT(ABS(C10),"-"&"h：mm：ｓｓ"),TEXT(C10,"h:mm：ｓｓ")))
にすると、合計を表示します。
この状態でC列を"表示しない"に設定します。
C列非表示がいやなら、作業列を離れた列にすればいい。
計算できるためには数値である必要があります。
つまり、時刻の書式設定で－を表さなければならない。
やり方を回答してくれた人がいます。
その人もそうですが、私も使いません。

chie65535 · Answer

セルの表示形式を時刻（h:mm:ss）にしている場合、

=IF(A1<B1,TEXT(B1-A1,"-h:mm:ss"),A1-B1)

と言う式を書く。

このままでは、A1の方が大きい場合は、そのまま時刻で表示されるので、右揃えで表示されるが、A1の方が小さい場合は、マイナス記号が付いて文字列として表示されるので、左揃えで表示される。

値によって右揃えになったり左揃えになったりしては困るので、セルの表示を「右揃え」にする。

セルの表示形式を「h:mm」などの秒無しに変えた場合は、式の中の「h:mm:ss」の部分を、表示形式に合わせて変更すること。

MarcoRossiItaly · Answer

セルの書式が時刻関係のものに設定されているとき、マイナスの時間は、皆さんのおっしゃっているとおり、表示することができません。（その書式では表示ができないだけで、実は正しい値を計算できてはいるのですが）

対処法としては、(1)時刻関係でないセルの書式に切り替える、(2)マイナスを回避する数式を書く、(3)マイナスの符号を付加した文字列を力ずくで合成する、といった工夫をするということになろうかと思います。

(1)
例えばセルの書式として「数値」を選び、「小数点以下の桁数」を適当な値に設定。次式により C1 セルには「-0.50000」（時間）が得られます。

A1　3:30
B1　4:00
C1　=24*(a1-b1)

以下では、例えば C1 セルの書式として「ユーザー定義」の「h:mm:ss」を設定するとしましょう。

(2)-1
マイナスになる場合は「0:00:00」と表示

C1　=max(,a1-b1)

(2)-2
マイナスになる場合は「-」という 1 文字を表示

C1　=if(a1<b1,"-",a1-b1)

以下では、C1 セルの書式は何でも構いません。

(3)
マイナスになる場合は時刻を表す文字列の先頭に力ずくで「-」の符号を付加

C1　=if(a1<b1,"-",)&text(abs(a1-b1),"h:mm:ss")

kagakusuki · Answer

ANo.5様の御回答に捕捉させて頂きます。

>休息時間＞作業時間の場合は、どちらかの数値が間違っている！

「休息時間＞作業時間」という事でしたら確かに間違っていますが、本件の質問内容は、「休息時間＞作業時間」ではなく、「空き時間＞作業時間」の場合に関する事ですから、間違ってはいないと思います。（休息時間と空き時間は異なります）

kagakusuki · Answer

>しかしこれですと表示は####になりませんが文字列なので集計ができず不効率で困っています。

> =IF(A1-B1<0,TEXT(ABS(A1-B1),"-"&"h：mm：ｓｓ"),TEXT(A1-B1,"h:mm：ｓｓ"))

　マイナスの時間も集計しなければならないという事ですね。
　それでしたら、適当な列を作業列として使用して、その作業列の値を集計すれば良いと思います。
　作業列の結果が表示されているのが見えてしまいますと見苦しくなりますので、適当な未使用のシート上の列を作業列として使用します。

　今仮に、A列とB列に元データを入力するシートがSheet1であり、Sheet2のA列を作業列として使用するものとします。
　まず、Sheet2のA1セルに次の関数を入力して下さい。

=IF(AND(COUNT(Sheet1!$A1,Sheet1!$B1)=2,Sheet1!$A1>=0,Sheet1!$B1>=0),Sheet1!$A1-Sheet1!$B1,"")

或いは

=IF(AND(COUNT(INDEX(Sheet1!$A:$A,ROW()),INDEX(Sheet1!$B:$B,ROW()))=2,INDEX(Sheet1!$A:$A,ROW())>=0,INDEX(Sheet1!$B:$B,ROW())>=0),INDEX(Sheet1!$A:$A,ROW())-INDEX(Sheet1!$B:$B,ROW()),"")

　次に、Sheet2のA1セルをコピーして、Sheet2のA2以下に貼り付けて下さい。
　これで、Sheet1のA列とB列の両方に時刻が入力されている場合には、「[作業時間]-[空き時間]」に相当する数値が表示され、それ以外の場合には、Sheet2のA列には何も表示されません。

　次に、Sheet1のC1セルに次の関数を入力して下さい。

=IF(ISNUMBER(Sheet2!$A1),IF(Sheet2!$A1<0,"-","")&TEXT(ABS(Sheet2!$A1),"[hh]:mm:ss"),"")

或いは

=IF(ISNUMBER(INDEX(Sheet2!$A:$A,ROW())),IF(INDEX(Sheet2!$A:$A,ROW())<0,"-","")&TEXT(ABS(INDEX(Sheet2!$A:$A,ROW())),"[hh]:mm:ss"),"")

　次に、Sheet1のC1セルをコピーして、Sheet1のC2以下に貼り付けて下さい。

　そして、Sheet1のC列の値を集計するのではなく、Sheet2のA列の値を集計するようにしますと、マイナスの値も集計に加える事が出来ます。
　尚、Sheet2のA列の書式設定の表示形式は、[標準]のままとした方が、表示が####とならずに済むため見やすくなりますが、表示形式を[時刻]として、マイナスの数値が####という表示となっているままであっても、表示が見えないだけで、そのまま集計する事は可能です。

JazzCorp · Answer

そのケース、時間数と時間数、２つの引き算は、純粋に算術的に考えるしかない。
それぞれが論理的に妥当な数値か、確認したうえで計算するしかない。
休息時間＞作業時間の場合は、どちらかの数値が間違っている！（＝の場合も同じようなもの？）
どちらが間違っているのかは断定できないので両方を確認することになる。

ここからは余談、
上に出てくる作業時間の計算でも、日付またがりの勤務時間帯があると、その計算は厄介？そうに思えるが、しかし、これは意外と簡単！、
Ａ１：出社時刻
Ｂ１：退社時刻
Ｃ１：休息時間」
２４時間表記で値の代償考えずに、論理的に組み立てた引き算に、「１」すなわち、２４Ｈｒ（＝１日）を補正する、これだけ、、、ホントかいな？？？、、、

作業時間＝（（Ｂ１－Ａ１）－Ｃ１）＋１

kagakusuki · Answer

Excelではマイナスの時間やマイナスの日付というものは扱えませんので、計算結果がマイナスの場合には#の繰り返しが表示されるだけとなります。
　ですから、計算結果がマイナスの場合には、時間が表示されない様に関数を工夫しなくてはなりません。

=IF(AND(ISNUMBER(TEXT($A1,"h:m:s")+0),ISNUMBER(TEXT($B1,"h:m:s")+0),$A1>$B1),$A1-$B1,"")

shintaro-2 · Answer

エクセルの時間表示は、
あくまでも年月の一部としての時間なので”負”という概念がありません。

皆さんお悩みのようで、okwaveの過去の質問にもあります。

例えば
http://oshiete.goo.ne.jp/qa/7790142.html
http://oshiete.goo.ne.jp/qa/7067806.html

webで検索してみてください。

big_egg · Answer

単純にセルの幅が足りないのではないかと。
一文字分程セルの幅を広げてみてはどうでしょうか。

EXCEL 時刻引き算で結果が###になってしまう

こんばんは。

C列をコピーしてC列に挿入すると、C列が#REF!に、D列が正常値になるとおもいます。

セルの表示形式を時刻（h:mm:ss）にしている場合、

セルの書式が時刻関係のものに設定されているとき、マイナスの時間は、皆さんのおっしゃっているとおり、表示することができません。

ANo.5様の御回答に捕捉させて頂きます。

>しかしこれですと表示は####になりませんが文字列なので集計ができず不効率で困っています。

そのケース、時間数と時間数、２つの引き算は、純粋に算術的に考えるしかない。

Excelではマイナスの時間やマイナスの日付というものは扱えませんので、計算結果がマイナスの場合には#の繰り返しが表示されるだけとなります。

エクセルの時間表示は、

単純にセルの幅が足りないのではないかと。

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

　ANo.5様の御回答に捕捉させて頂きます。

　Excelではマイナスの時間やマイナスの日付というものは扱えませんので、計算結果がマイナスの場合には#の繰り返しが表示されるだけとなります。