【Excel/関数】小技～負の時刻を計算・表示する方法

エクセルの時刻形式では負の時刻は「そんなもんねんよ」とエラーになってしまう。だがしかし、勤務時間の前月との差を出してみたり、マイナスも表現したいときがある。

IF関数と、ABS関数と、TEXT関数を使うと時間表記でマイナスを表示することができます。

ABS関数は数値の絶対値を返す関数。（例えば　▲0.5→0.5、0.5→0.5）

TEXT関数はこの値をこの書式で表示してねと、指定できる関数。セルの書式設定と違って数値は「文字列」になるのでその後計算などには使用できない。

詳しくはこちらが分かりやすかったです。

E列に式を入れてみました。

=IF(C3<B3,TEXT(ABS(C3-B3),”▲h:mm:ss”,TEXT(C3-B3,”h:mm:ss”))

まず、差はC列からB列を引いて出しています。

C3<B3 つまりC3の方が小さい場合は負の値になります。

その場合はABSでC3-B3からマイナスを取り去った絶対値を出して書式をh:mm:ssにマイナスをつけたものにしています。なお、ここではマイナスを「▲」で表していますがもちろん「-」でも大丈夫です。

C3<B3 が偽の場合（つまりC3の方が大きい）はC3-B3がマイナスにならないのでそのまま”h:mm:ss”とします。

これで見た目は大丈夫になりました。

ただ、このE列は文字列なのでE列の値を使って計算をすることはできません。

「差」の平均を出したくなったとします。

もともとのシリアル値のD列で平均を出しました。しかし平均がマイナスだったため＃＃＃＃＃になってしまいました。

なので先ほどの式をちょっと修正して、E列にマイナスの時間が表示されるようにしました。

D7の値が0より小さいい場合は、D7の絶対値を”▲h:mm:ss”で表示、0以上の場合は”h:mm:ss”で表示します。

＃＃＃＃でもちゃんと時間としてのシリアル値の数値を保っているので、これで大丈夫なのですが、なんか見えないと不安なので、私は秒換算（分が最小単位の時は分換算）して計算し、それの結果を時間表示にしてあらわしたりしています。

D列はB列1月の時間を秒換算しています。

ちょっと面倒なのですが、

=HOUR(B3)*3600+MINUTE(B3)*60+SECOND(B3)

とします。

B列の値からHOUR関数で時刻を取り出し、秒換算のため3600をかける(1時間は3600秒なので)。

同様にB列の値からMINUTE関数で分を取り出し、秒換算のため60をかける(1分は60秒)。

同様にB列の値からSECOND関数で秒を取り出し、すべてを足すとB列の値が秒換算されます。

E列はD列と同様にC列の値を秒換算したものです。

で、秒換算した1月と2月を比較して差を出しています。列の秒を使って差の平均も出しています。

「B君、2月の方が1820秒遅くなってるね」とか言われても、「平均は305秒早くなってるね」とか言われてもピンとこないだろうと思い、G列で時間表記にしています。

例えばG7の平均の箇所の式は

=IF(F7<0,TEXT(TIME(0,0,ABS(F7)),”▲h:mm:ss”),TEXT(TIME(0,0,F7),”h:mm:ss”))

です。

F7の値が0より小さい場合はF7の値からマイナスをとった絶対値をTIME関数の秒のところにつっこみ、TEXT関数で時間表記にしています。

TIME関数は時、分、秒のところに数値を入れると時間のシリアル値に変換してくれます。「秒」のところに〇秒を突っ込むと、60秒を超えるところは勝手に計算して時・分にしてくれます。

そしてTIME関数でシリアル値にしたものをTEXT関数で時間表記にしています。

◆関数は使わずエクセルのオプションで負の時間を表示する方法

なお、エクセルのオプションで「1904年から計算する」にチェックを入れると、時間がマイナスでもエラーにならなくなります。

ただし、日付の計算方式が変わってしまうので、既に日付がどこかに入力されているエクセルの場合、注意が必要です。どこでなにがおこるか予想がつかない部分があるのであまりお勧めではないです。

以上です。

最後までお読みいただきありがとうございました。

時間についてはこちらもどうぞ！