エクセル：開始，終了日から求めた期間を「時間(hour)」で表示

こんにちは、さち です。

先日、エクセルを使って
開始日時 と 終了日時 から期間を算出していました。

その期間の長さを「◯◯時間」と表示しようとしたのですが
書式設定から表示形式を変更するだけでは無理らしい。

本当に時間計算ってややこしいですよね…。

期間の長さを「◯◯時間」で表示したい

1. 「開始」と「終了」から「期間」を求めてみます。
   
2. 「終了」から「開始」を引き算します。
   
3. 計算結果は「10.04166667」となりました（表示形式は「標準」）。
   
4. 期間の長さを「時間」で表示したいので
   表示形式を「h"時間"」に変更してみました。
   しかし、結果は「1時間」となってしまいました。
   1時間なわけがないのでおかしいですね。
   

日付は「1900年1月1日」を起点にしている

期間の長さを「時間」で表示するには
計算結果である「10.04166667」が何を意味するのか
理解する必要がありそうです。
（方法だけ分かればいい人は次項まで読み飛ばして下さい）

エクセルでは、日付データを
「1900年1月1日」から何日目という形で記録しています。

例えば、起点の「1900/1/1」は1日目なので「1」。
1日後の「1900/1/2」は2日目なので「2」
同様に、1年後の「1901/1/1」は「367」なわけです。

実際、セルに「1」「2」「367」などと入力し
「セルの書式設定」から「表示形式」を「日付」にすると
上図と同じ日付に変換されることからも分かります。

ここで、先ほどの「2014/2/10 3:00」について考えてみましょう。
エクセルの形式に従うと、2014/2/10 は「41680」です。
さらに、今回は時間「3:00」があります。
1日は24時間なので 1÷24×3＝0.125 を足します。
つまり、「2014/2/10 3:00」は「41680.125」と表せるわけです。

同様に、「2014/2/20 4:00」は「41690.16666667」となります。
（1÷24×4 は 0.16666666… と割り切れないなのでこうなります）

だから 2014/2/10 3:00 から 2014/2/20 4:00 までの期間の長さは
41690.16666667－41680.125＝10.04166667 と計算されたわけです。

「時間」で表示するには「24」をかければいい

エクセルの日付データは
1900年1月1日を起点にした経過日数で記録されていましたね。

つまり、先ほどの計算結果「10.04166667」は
期間の長さを「日数」で表示したものということ。
「時間」で表示するには「24」をかければよいわけです。

計算結果は「241時間」となりました。
期間の長さは 10日と1時間 なので正解ですね。

同様に、「分」で表示したい場合は
「1440（＝24×60）」をかければよいですよね。

ね、簡単でしょ？

どうでもいいような、よくないような事

最後に少し脱線して
エクセルの日付に関する無駄な話をしましょう。

1901/1/1 は「367」と記録されているという話をしましたが
この「367」のような日付を表す値を「シリアル値」と言います。

このシリアル値、実は「0」からスタートしています。
そのため「0」と入力したセルの表示形式を日付に変更すると
「1900年1月0日」という謎の日付になります。

また、エクセルでの日付のカウントが始まる「1900年」ですが
厳密にはうるう年ではないので2月29日は存在しません。

前項で、あたかも「1901/1/1」は367日目だから
シリアル値は「367」のように書きましたが実は違います。
本来、1901/1/1 は366日目なので「366」となるべきですが
エクセルには幻の 1900/2/29 が存在するため「367」になります。
つまり、1900/3/1 以降はシリアル値＝経過日数とはなりません。

「こまけぇこたぁいいんだよ！」にも思えますが
1900/3/1 から 1900/2/28 を引いた結果は「2」となり時空が歪みます。
つまり、時間計算で 1900/2/29 をまたぐと計算が狂い実害が出ます。
気をつけましょう。

エクセルで時間計算をすると
表示形式が思いどおりにならなかったり
訳の分からない数値になったりすることがあるので
苦手に思っている人もいるかもしれません。

日時がどのように記録，計算されるかが分かれば
苦手意識もかなり克服できるはずですよ。