Excelでは1つのセルに日付と時刻の両方のデータを入力することができます。このときのシリアル値は日付と時刻のシリアル値を足した値となります。
ところが、セルの表示形式によって日付の部分または時刻の部分だけを非表示にすることができるため、セルの表示のとおりにシリアル値が入力されていないことがあります。そのため、Excelでは日付または時刻が入力されているセルは、日付または時刻しか表示されていなかったとしても、常にシリアル値と表示形式を確認するように注意しなければなりません。
そこで、今回は、日付と時刻を合わせて入力した場合の取り扱いと、日付または時刻の部分だけを取り出す方法について出題します。
目次
- １．シリアル値について
- ２．日付＋時刻の表示形式
- ３．小数のシリアル値について
- ４．オートフィルによる連続データの入力
- ５．日付と時刻が隠れているかもしれない
- ６．1900年1月0日0時？
- ７．日付と時刻の分離
- ８．TODAY関数
- ９．NOW関数
１．シリアル値について
日付と時刻のシリアル値については別の記事で詳しく解説しています。それぞれこちらの記事をご覧ください。
２．日付＋時刻の表示形式
問題
セルA1に「2030/4/5 12:34」と入力しなさい。このときの表示形式を確認した上で、表示形式を標準にしなさい。
解説
まず、入力する前の表示形式を確認します。「標準」になっています。
セルA1に「2030/4/5 12:34」と入力します。右揃えになります。このように日付と時刻の間に半角のスペースを入れて入力することで、1つのセルに日付と時刻のデータを両方入力することができます。
表示形式を確認します。ユーザー定義「yyyy/m/d h:mm」が自動で設定されます（参考：【Excel】ユーザー定義表示形式のサンプルの意味を全部解説します）。
自動的に右揃えになるということは、Excelが数値データとして認識していることが分かります。表示形式を標準にします。47578.52361となります。
逆にこの小数を入力して表示形式をユーザー定義「yyyy/m/d h:mm」にすると、「2030/4/5 12:34」となります。
つまり、Excelでは、小数の47578.52361を入力するのと、日付時刻の2030/4/5 12:34を入力するのは全く同じということになります。表示形式で切り替えているだけです。
３．小数のシリアル値について
（１）日付のシリアル値
Excelは、1900年以降の日付を、1900年1月1日を1日目とする経過日数で表します。これをシリアル値といいます（参考：【Excel】日付の「シリアル値」を本気で理解するには何を練習するべきか？）。
2030/4/5は、47578日目にあたるため、シリアル値は47578となります。Excelで2030/4/5と入力するのと、整数の47578と入力するのは全く同じです。表示形式を標準にするか日付にするかの違いであり、切り替えは可能です。
（２）時刻のシリアル値
Excelのシリアル値は経過日数なので、1日経過するとシリアル値が1増えます。つまり、整数値の「１」は、24時間に相当します（参考：【Excel】時刻のシリアル値と時間計算の基本を理解するための練習問題）。
0.5は12時間、0.25は6時間となります。12時間34分を小数に換算すると0.52361となります（参考：【Excel】時間の掛け算、シリアル値と時間の換算、時間の合計を求めるときの注意点）。Excelで12:34と入力するのと小数で0.52361と入力するのは全く同じです。表示形式を標準にするか時刻にするかの違いであり、切り替えは可能です。
（３）日付＋時刻
整数の47578と小数の0.52361を合わせると、2030/4/5の12:34のシリアル値となります。したがって、前述のように、小数の47578.52361と「2030/4/5 12:34」は表示形式が異なるだけで、データとしては全く同じものなのです。
（４）足し算・引き算で確認してみよう
問題
セルA1に「2030/1/1」、セルA2に「18:00」と入力した。これらを足して「2030/1/1 18:00」になることを確認しなさい。また、引き算をするとどうなるか。
解説
セルA1とセルA2を足します。「2030/1/1 18:00」になります（もし、ならなければ表示形式が間違っている）。
シリアル値で考えると、2030年1月1日（シリアル値47484）に、18時間（シリアル値0.75）を加算して、2030年1月1日18時（シリアル値47484.75）になります。
今度は2030年1月1日から18時間を引きます。シリアル値で引き算をすると、47484-0.75=47483.25となりますから、前日の2029年12月31日の午前6時となります。
これは年越しの18時間前にあたります。
４．オートフィルによる連続データの入力
問題
次の図のように入力しなさい。
解説
セルA1～C1に「2030/4/5 12:34」と入力します。
セルA1をこのままオートフィルをすると1日ずつ増えます。シリアル値は1ずつ増えます。時刻は変わりません。
この増え方を変えるには最初に2つ入力する必要があります（参考：【Excel3級レベル】オートフィルを利用した表の作成手順と入力練習（初心者特訓Part5））。セルB2に「2030/4/7 12:34」と入力します。2つ選択します。
オートフィルをすると2日ずつ増えます。シリアル値は2ずつ増えます。時刻は変わりません。
3時間ずつ増やしたければ、3時間後の時刻を入力します。セルC2に「2030/4/5 15:34」と入力します。2つ選択します。
オートフィルをすると3時間ずつ増えます。シリアル値は3/24（=0.125）ずつ増えます。3時間ずつ足していき、24時を超えたら日付も変わります。
別解
セルB1に2を足します。
セルB2を、オートフィルをします。これでシリアル値が2ずつ増えるので、2日ずつ増えます。
セルB1に3/24を足します。0.125でも構いません。
セルB2を、オートフィルをします。これでシリアル値が3/24ずつ増えるので、3時間ずつ増えます。
５．日付と時刻が隠れているかもしれない
問題
A列には日付、B列には時刻が表示されている。
A列のシリアル値はすべて整数であると言えるか。また、B列のシリアル値はすべて1未満の小数であると言えるか。
解説
前述のとおり、日付のシリアル値は1900/1/1からの経過日数なので、原則として整数になります。日数が小数になるわけがありません。
A列には日付が表示されています。しかし、数式バーの表示には時刻が含まれています。
表示形式を標準にすると、小数になります。これは、日付以外に時刻のデータが含まれていることを表しています。
ところが、表示形式を日付（yyyy/m/d）にすると日付だけが表示され、時刻の部分（小数部分）は非表示となります。このように表示形式を変えることによって時刻の部分を非表示にすることができるため、セルの表示だけを見て、時刻のデータが無いと判断してはいけません。Excelの日付の表示は、常に時刻が「隠れているかもしれない」と思って見なければなりません。
また、時刻のシリアル値は「24時間＝１」として、24時間未満の時間を1未満の小数に換算して表したものです。したがって、0時または24時でない限り、時刻のシリアル値は必ず小数になるはずです。
表示形式を標準にすると、1以上の小数になります。整数部分があるということは、時刻以外に日付（日数）のデータが含まれていることを表しています。
ところが、表示形式を時刻（h:mm:ss）にすると時刻だけが表示され、日付の部分は非表示となります。セルの表示だけを見て、日数のデータが無い、24時間以上でないなどと安易に判断してはいけません。Excelの時刻の表示は、常に日付が「隠れているかもしれない」、「24時間以上かもしれない」と思って見なければなりません。
６．1900年1月0日0時？
問題
セルA1に「2034/5/6」、セルA2に「17:08」と入力した。これらの表示形式を「yyyy/m/d h:mm」にするとどうなるか。また、そのように表示される理由を述べなさい。
解説
セルA1に「2034/5/6」と入力します。直接入力しているので時刻のデータが含まれていないのは明らかです。シリアル値は整数です。
表示形式を「yyyy/m/d h:mm」にすると、「2034/5/6 0:00」となります。
日付と時刻のシリアル値は、小数点より左側の整数部分が日付を表し、右側の小数部分が時刻を表します。その時刻は「24時間＝１」として換算した小数です。したがって、小数部分が無ければ「時刻＝０」なので、午前0時を表すことになります。原則として、日付だけを入力した場合、その時刻は０時０分０秒です。
セルA2に「17:08」と入力します。直接入力しているので日付のデータが含まれていないのは明らかです。シリアル値は1未満の小数です。
表示形式を「yyyy/m/d h:mm」にすると、「1900/1/0 17:08」となります。
シリアル値は1900/1/1を1日目とする経過日数なので、0日目を1900/1/0と表します。もちろん、実際にはこんな日付は存在しませんが、Excelではシリアル値０にあたる日付を特別に「1900/1/0」と表示するきまりとなっています。そのため、シリアル値の整数部分が無ければ、日付＝０なので「1900/1/0」となります。
７．日付と時刻の分離
問題
A列に3時間おきの日付と時刻を入力した。日付のシリアル値だけを取り出しなさい。また、時刻のシリアル値だけを取り出しなさい。
解説
前述のように、日付と時刻を合わせた形式のデータは、シリアル値にあたる小数値を入力したのと全く同じであり、その整数部分（小数点の左側）は日付を表し、小数部分は時刻を表します。したがって、それぞれの部分を取り出すにはINT関数とMOD関数を使います（参考：【Excel関数】小数の整数部分と小数部分を取り出す方法と合計の求め方）。
INT関数で整数部分を求めます。小数部分が無くなるので「0時」になります。
- =INT(A2)
すべて０時になったので、シリアル値がすべて整数値になったといえます。したがって、日付だけの表示形式にしても差し支えありません。
MOD関数で小数部分だけを取り出します。整数部分が無くなるので「1900/1/0」になります。
- =MOD(A2,1)
日付のデータがすべてなくなったので、時刻だけの表示形式にしても差し支えありません。
＜補足＞
A列を日付の表示形式にして、時刻を非表示にすることは可能です。しかし、セルの表示として非表示になっているだけで、シリアル値が変わるわけではありません。小数のままです。
INT関数はシリアル値を整数値に変えるので、元のデータとは異なることに注意しなければなりません。
８．TODAY関数
（１）TODAYは０時を表す
問題
TODAY関数が返すシリアル値は整数値か。また、何時を表すか。
解説
TODAY関数を挿入します。日付だけが表示されます。
表示形式をユーザー定義「yyyy/m/d h:mm」にすると、時刻の部分は必ず「0:00」になります。
TODAY関数は現在の日付を表すシリアル値を返す関数ですが、時刻の無い整数値を返します。したがって、TODAY関数が返すシリアル値の時刻は午前0時です（前述）。
このことはショートカットキー（Ctrl＋セミコロン）で入力した場合も同じです（参考：【Excel関数】今日の日付を求めるTODAY関数と、CTRL＋セミコロンの違い）。日付しか入力していない場合の時刻は０時です。
（２）今日の6時
問題
TODAY関数を用いて、本日の午前6時（シリアル値）を求めなさい。
解説
TODAY関数を挿入します。日付だけが表示されます。日付だけの場合、その時刻は午前0時です。これに6時間を加算すればいいので、「+6/24」と入力します（参考：【Excel】24時間を超える時刻の足し算引き算、0時よりも前の時刻の処理）。
- =TODAY()+6/24
これで今日の午前6時になります。いっぱんに、今日のX時は「TODAY()+X/24」です。
９．NOW関数
問題
現在の時刻だけを表すシリアル値を求めなさい。
解説
NOW関数を挿入します。現在の日付と時刻が表示されます。時刻だけではないことに注意します。
ExcelのNOW関数は、現在の日付のシリアル値と時刻のシリアル値を足した数値を返します。このうち、日付のシリアル値の部分は今日の日付なのでTODAY関数と一致します。
したがって、NOWからTODAYを引けば、現在の時刻だけを表すシリアル値になります。
- =NOW()-TODAY()
別解
NOW関数で求めたシリアル値のうち小数部分が現在の時刻にあたります。したがって、MOD関数を用いて求めることもできます。
- =MOD(NOW(),1)
解説は以上です。
