【Excel】日付と時刻を1つのセルにまとめて表示する!Excelのシリアル値と書式の組み合わせ術

Excelで日付と時刻を別々のセルに入力し、それを1つのセルにまとめて表示したい場面があるでしょう。

しかし、単純に結合しようとすると、意図しない形式で表示されたり、計算ができなくなったりすることがあります。

この記事では、Excelのシリアル値の仕組みを理解し、セルの表示形式を適切に設定することで、日付と時刻を1つのセルにまとめて表示する具体的な方法を解説します。

これにより、データの管理や表示が見やすくなり、業務効率の向上につながります。

Excelにおける日付と時刻の内部表現

Excelでは、日付と時刻はそれぞれ数値(シリアル値)として内部的に処理されています。

日付は1900年1月1日を「1」として、そこからの経過日数を整数で表します。

時刻は、1日を「1」とした場合の割合を小数で表します。

例えば、「2023年10月27日 10時30分」は、Excel内部では「45224.4375」という数値として格納されています。

このシリアル値の仕組みを理解することが、日付と時刻を正しく表示・操作するための鍵となります。

方法1:セルの表示形式を組み合わせて日付と時刻をまとめる

この方法は、日付と時刻がそれぞれ別々のセルに入力されている場合に有効です。

最終的に1つのセルに日付と時刻を表示したい場合、そのセルに表示したい書式を設定します。

ここでは、日付と時刻がそれぞれA1セルとB1セルに入力されていると仮定して説明します。

1. 日付と時刻を結合したいセルを選択する
   例えば、C1セルを選択します。
2. 数式バーに数式を入力する
   C1セルに「=A1+B1」と入力します。Excelは日付と時刻のシリアル値を内部で加算します。
3. セルの表示形式を設定する
   C1セルを選択した状態で、右クリックして「セルの書式設定」を選びます。
   「表示形式」タブで「分類」から「ユーザー定義」を選択します。
   「種類」の入力ボックスに「yyyy/mm/dd hh:mm」と入力し、「OK」をクリックします。

この手順により、C1セルにはA1セルとB1セルの日付と時刻が結合された状態で表示されます。

書式設定の「種類」の部分は、必要に応じて「yyyy年m月d日 H時m分」のように変更できます。

Excel 2019やExcel 2021でも、この手順は同様に利用できます。

方法2:TEXT関数を使って日付と時刻を文字列として結合する

この方法は、日付と時刻を個別のセルから取り出し、指定した書式の文字列として1つのセルに結合したい場合に便利です。

特に、計算結果としてではなく、表示用のテキストとして結合したい場合に適しています。

日付がA1セル、時刻がB1セルに入力されていると仮定します。

1. 結合結果を表示したいセルを選択する
   例えば、C1セルを選択します。
2. TEXT関数を用いて数式を入力する
   C1セルに「=TEXT(A1,”yyyy/mm/dd”)&” “&TEXT(B1,”hh:mm”)」と入力します。
   TEXT関数は、第一引数に指定した値(A1やB1)を、第二引数で指定した書式(“yyyy/mm/dd”や”hh:mm”)の文字列に変換します。
   「&」は文字列を結合する演算子です。
   ここでは、日付と時刻の間に半角スペースを入れるために” “を追加しています。

この数式では、A1セルの日付を「yyyy/mm/dd」形式の文字列に、B1セルの時刻を「hh:mm」形式の文字列に変換し、それらをスペースで連結しています。

TEXT関数の書式指定子(yyyy, mm, dd, hh, mmなど)を変更することで、表示形式を自由にカスタマイズできます。

この方法は、Excel 2019やExcel 2021でも同様に使用可能です。

方法3:日付と時刻のシリアル値を直接結合して書式設定する

この方法は、日付と時刻が別々のセルにあり、それらをExcelの内部表現であるシリアル値として結合し、最終的に表示形式で整形する方法です。

方法1と似ていますが、こちらはより直接的にシリアル値の加算を利用します。

日付がA1セル、時刻がB1セルに入力されていると仮定します。

1. 日付と時刻を結合したいセルを選択する
   例えば、C1セルを選択します。
2. 数式バーに数式を入力する
   C1セルに「=A1+B1」と入力します。Excelは日付のシリアル値と時刻のシリアル値を加算し、新しいシリアル値を生成します。
3. セルの表示形式をユーザー定義で設定する
   C1セルを選択した状態で、右クリックし「セルの書式設定」を選択します。
   「表示形式」タブの「分類」から「ユーザー定義」を選びます。
   「種類」のボックスに、「yyyy/mm/dd hh:mm:ss」のように、表示したい日付と時刻の形式を正確に入力します。

この方法の利点は、Excelのシリアル値の加算に基づいているため、計算結果として正確な日付と時刻が得られることです。

表示形式を調整することで、任意の日付と時刻の組み合わせを1つのセルで表現できます。

Excel 2019およびExcel 2021でも、この手順は変わりません。

日付と時刻のシリアル値に関する注意点

Excelで日付と時刻を扱う際には、シリアル値の特性を理解しておくことが重要です。

1900年問題について

Excelは、デフォルトで1900年を閏年として扱ってしまう「1900年問題」があります。

これは、Excel 97以前の互換性のために残っている仕様です。

通常の使用では問題になることは少ないですが、非常に古い日付を扱う場合や、他のシステムとのデータ連携で厳密な日付計算が必要な場合に注意が必要です。

Mac版Excelでは、この1900年問題は発生しません。

日付と時刻の書式設定が計算に影響しないこと

セルの表示形式を変更しても、Excel内部のシリアル値は変わりません。

つまり、「2023/10/27」と表示されていても、内部的には「45224」という数値です。

そのため、表示形式が「yyyy/mm/dd」になっていても、「=C1+1」のような計算を行えば、翌日の日付(シリアル値+1)が計算されます。

この特性を理解しておけば、表示と計算のずれに戸惑うことはありません。

TEXT関数と表示形式の違い

TEXT関数は、値を指定した書式の「文字列」に変換します。

一方、セルの表示形式は、あくまでセルの「見え方」を変更するもので、内部のデータ型(数値、文字列など)は変更しません。

したがって、TEXT関数で結合した結果は文字列になるため、そのセルを日付や時刻として直接計算に使うことはできません。

計算に利用したい場合は、方法1や方法3のように、セルの表示形式を調整するのが適切です。

よくある失敗例と対処法

結合すると「#####」と表示される

これは、セルの幅が結果を表示するのに足りない場合に発生します。

対処法は簡単で、列幅を広げることです。

セルの右端の境界線をダブルクリックするか、ドラッグして幅を調整してください。

日付と時刻が正しく結合されない

原因は、数式の誤り、または日付・時刻として認識されていないデータである可能性があります。

数式が「=A1+B1」や「=TEXT(A1,”yyyy/mm/dd”)&” “&TEXT(B1,”hh:mm”)」のようになっているか確認してください。

また、A1やB1セルに入力されている値が、Excelに日付や時刻として正しく認識されているか確認が必要です。

もし、単なる文字列として入力されている場合は、セルの書式設定を「日付」や「時刻」に変更するか、DATEVALUE関数やTIMEVALUE関数を使って数値に変換してから結合する必要があります。

結合した結果が計算に使えない(文字列になっている)

これは、TEXT関数を使って結合した場合に起こりやすい問題です。

TEXT関数は結果を文字列として返すため、そのセルを直接数値計算に使うことはできません。

計算に利用したい場合は、方法1や方法3のように、セルの表示形式を調整して結合してください。

あるいは、結合した文字列を再度DATEVALUE関数やTIMEVALUE関数、そして必要であればVALUE関数などを使って数値(シリアル値)に変換してから計算に使う必要があります。

比較表:セルの表示形式とTEXT関数による結合

項目	セルの表示形式による結合	TEXT関数による結合
目的	日付と時刻を1つのセルにまとめて表示する(データ型は数値)	日付と時刻を任意の書式の文字列として1つのセルに結合する
数式例	=A1+B1 (表示形式で調整)	=TEXT(A1,”yyyy/mm/dd”)&” “&TEXT(B1,”hh:mm”)
結果のデータ型	数値(日付・時刻のシリアル値)	文字列
計算への利用	可能(直接計算できる)	不可(文字列のため、変換が必要)
柔軟性	表示形式のカスタマイズは限定的	書式指定子により非常に柔軟な表現が可能
Excelバージョン	全バージョン共通	Excel 2013以降で推奨(旧バージョンでも利用可能だが、互換性に注意)

上記比較表は、セルの表示形式を調整して日付と時刻を1つのセルにまとめる方法と、TEXT関数を用いて文字列として結合する方法の違いを示しています。

どちらの方法を選択するかは、その後のデータの利用目的によって判断してください。

計算に利用する可能性がある場合は、セルの表示形式を調整する方法が適しています。

単に表示したいだけであれば、TEXT関数による柔軟な書式設定も有効な選択肢となります。

まとめ

Excelで日付と時刻を1つのセルにまとめて表示するには、セルの表示形式のカスタマイズやTEXT関数の活用が有効です。

Excelのシリアル値の仕組みを理解することで、これらの操作がさらにスムーズに行えるようになります。

今回解説した「セルの表示形式の変更」や「TEXT関数を用いた結合」を実践することで、データの表示を見やすくし、業務効率を高めることができます。

さらに、これらの知識を応用して、レポート作成やデータベース管理など、様々な場面でExcelをより活用していきましょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】