Excel講座◆時刻・日付データへの理解を深める

それでは淡々と時刻・日付データについて述べてゆきます。

■日付・時刻データの仕組み

Excelに日付を入力する際、

セルに「2021/7/16」のように入力すると思いますが、

このとき、当該セルには、入力した日付を「44393」という数値に変換して、そのセルの値として設定する処理がなされます。

※44393については後ほど解説

どういうことかというと、

セルの見た目が「2021/7/16」になっているだけで、そのセルには「44393」と数値をキーボードから入力したのと同じになっているということです。

セルの本当の中身をもとに、画面上に表示する文字（数値）を変えているのです。これを表示形式といいます。

例えば、0.3という数値が入力されているセルを選択した状態で

この「%」ボタンをクリックすると、セルの表示が「30%」に変わりますが、セルに入力した「0.3」を「30%」という文字に書き換えているわけではなく、「0.3」はそのままに、見た目だけ「30%」に変えているわけです。

★なんで「44393」なのら？

日付データは「44393」のような数値として値が保持されるわけですが、なぜ「44393」なのでしょう。

それは、2021年7月16日は、1900年1月1日から数えて44393日目の日付だからです。

つまり、Excelでは日付データを、1900年1月1日を1として、そこから数えた日数で表すのです。

このように、日付データの本当の中身(44393)をシリアル値と言います。

★時刻のシリアル値と計算

時刻のシリアル値は、24時間を1とした数値(小数)で表します。

例えば、お昼の12時は1日(24h)の半分なので0.5です。

午前1時は1÷24で、0.041666...です。

午前0時15分のように分まで含めると少し計算手順が増えます。

・15分が何時間かを計算します。

→15/60 = 0.25時間

・0.25÷24を計算します。

→0.01041666...

これが0時15分のシリアル値です。

=(15/60)/24

のように入力すると1発で出せますね。

0時0分40秒の場合は、

まず秒を分になおし、分を時になおして計算します。

=((40/60)/60)/24

になりますね。

 

2021/7/16 14時00分のように、日付と時刻が一緒になっているもののシリアル値は、日付・時刻それぞれのシリアル値を足して

44393.583333333...

になります。

 

★日付データの計算

「2021/7/16」のような日付データも数値として値が保持されているので、足し算や引き算を行うことができます。

例えば、このようにすれば1ヶ月間の日数を計算することができます。

ちなみに、

「2021/3/1」では、3月1日の日数、のように見えてわかりづらいので「2021年3月」や「3月」にするべきですよね。

このようなときに表示形式の設定を行います。

★表示形式の設定

まず、「2021/3/1」セルを右クリックして、セルの書式設定を開きます。

次に、表示形式タブから「日付」に入りお好みの表示形式を選択し、OK。

すると、3月の表示形式が変わります。もちろん、見た目が変わっただけでセルの中身は「44393」のままです。

12ヶ月分のセルを範囲選択して右クリックからセルの書式設定をおこなっても、一挙に反映させることができます。

★数式中で日付の比較・計算を行う

IF関数を使って、セルA1の日付が2021年1月1日以上の日付か、それ以前の日付かを調べたいとき

=IF(A1>=2021/1/1,"2021年以上","2021年以前")

このような数式を入力しエンターすると

2020年の日付であるにも関わらず、「2021年以上」と表示されてしまいます。

お気づきでしょうか？

2021/1/1は

2021÷1÷1

という計算式なのです。

上記の計算式の結果は、「2021」。

IF関数の条件式中で、

A1>=2021/1/1

は、

43864>=2021

として評価されてしまうのです。

このようなとき、DATEVALUE関数を使います。

DATEVALUE関数とは、

"2020/2/3"のような日付を表す文字列を、割り算の計算式ではなく日付として扱い、シリアル値に変換してくれる関数です。

=IF(A1>=DATEVALUE("2021/1/1"),"2021年以上","2021年以前")

このように使います。

ちなみに、

=DATEVALUE("2021年3月2日")

=DATEVALUE("2021年5月")

という形でもいいですよ。

★数式中で時刻の比較・計算を行う

セルA1に入っている日時データが、14時以降かどうかを調べる場合のお話です。

時刻をシリアル値になおす場合は、

=14/24のような割り算を行えばいいのですが、

セルA1に入っている日時データが「2021/7/16 11:04」のように、

日付+時刻のデータの場合、

シリアル値は

44393.4611111....

ですから、

=14/24の計算結果である0.583333...と比較しても、

44393.46111... >= 0.583333...

これではうまく比較できるはずがありません。

時刻の差を計算するためには小数点以下の数値同士で比較する必要があります。

なので、日付のシリアル値(整数部)の影響を排除するため、

 IF関数条件式中の A1 を、

( A1-TRUNC(A1) )

に置き換え、セルA1から整数部を引いて小数点以下の数値のみ取得した値と、14/24=0.583333...を条件式中で比較することによって、時刻の以前・以降がちゃんと比較されるようにしましょう。

時刻・日付データへの理解を深める　終