マイナスの時間計算で結果が「#####」

マイナスの時間計算ができない

時間を引き算すると『######』

時間を引き算すると、数式の結果がマイナスの場合『######』となってしまいます。
見た目は、セル幅がせまくて入りきらないときの「#####」と同じですが、セル幅を広げても解決しません。
Excelでは日付や時間を「シリアル値」で管理していますが、標準のシリアル値では、マイナスの時間を表示することができません。
解決方法は、オプションから設定する方法と、関数を使う方法の2通りです。
関数では、TEXT関数とABS関数とIF関数の３つを使います。

オプションから設定してマイナス時間を表示する

Excel 2007以降は、[オプション]ダイアログボックスを開き、詳細設定項目の下の方にある、次のブックを計算するときグループの、1904年から計算するチェックボックスをオンにします。

Excel 2003まではメニューから、ツール、オプションと選択し、

オプションダイアログボックスの[計算方法]タブをクリックし、1904年から計算するチェックボックスをオンにします。

関数を使って時間の引き算のマイナスを表示する

消火訓練にかかった時間を毎回記録して、改善の参考にするために、前回との時間差を出したいということで以下のような表にしました。

単純に前回の時間から今回の時間を引き算しているだけです。
前回よりも時間が多い場合は、上手く表示されますが、時間が短縮されてマイナスになるはずの箇所では、『######』となってしまいます。

これは、Excelが壊れたわけではないので、内部ではきちんと計算されているはずなんですね。けれど、マイナスになると表示できない・・・。
そこで、TEXT関数をつかいます。 TEXT関数の引数には数値と表示形式を持ってきますが、引数にABS（アブソルート）関数を使います。

他にABS関数の書式です。

早速『######』と表示されているセルに入力してみましょう。

表示形式を設定するところで、"mm:ss"と時間がプラスの場合だけ設定しているので、マイナスになっていません。

IF(B5>B6,"-mm:ss","mm:ss")

そこで式を以下のようにします。TEXT関数の引数の表示形式にIF関数を使った上の式をあてはめます。

=TEXT(ABS(B6-B5),IF(B5>B6,"-mm:ss","mm:ss"))

結果は、マイナス時間が表示され、このようになります。