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

この記事は約4分で読めます。
記事内に広告が含まれています

操作画面

こんにちは、さち です。

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

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

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

スポンサーリンク

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

  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 をまたぐと計算が狂い実害が出ます。
気をつけましょう。

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

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

コメント

  1. 匿名 より:

    書式を [h]"時間" にするだけでよかったはずです。
    ttp://support.microsoft.com/kb/879837/ja

  2. うみの さち より:

    おぉ!やっぱり書式でやる方法あったんですね。
    (5.8時間みたいな)小数点以下が不要な場合ならその方法が簡単ですね。
    勉強になりました、ありがとうございました。

タイトルとURLをコピーしました