【図で解説】エクセルで日付変換のいろは－表示形式変更や自動制御の手順を伝授します

ビジネスシーンではエクセルで日付を含む資料を作成したり、修正したりすることがありますよね。
表示形式は西暦なのか和暦なのか、文字列なのかシリアル値なのかユーザーを悩ませます。

ここではエクセルで日付の表示形式や文字列やシリアル値への変換方法を紹介しています。
うまく日付を変換できていないなら必見です。

ぜひこの記事を読んで、エクセルで日付を扱うことに慣れてくださいね！

1.エクセルで日付を扱おう

エクセルで日付を入力するときどのように入力しますか？
西暦で入力したり和暦で入力したり、または数字の羅列で入力したり、日付の形式は様々な形で表現できます。

さっそく日付変換の方法を解説したいところですが、そのためにもまずはエクセルで日付がどのように扱われるかを知りましょう。

1-1.シリアル値とは

エクセルは「2019/1/1」といった形が「2019年1月1日」であると認識できません。
たとえばエクセルに日付を入力してみます。

「5/6」と入力すると、自動的に「5月6日」と表示され、左上の数式バーには「2019/5/6」と表示されています。

セルを選択したまま右クリックのメニューを表示し、「セルの書式設定」を開きます。
すると分類は「ユーザー定義」で種類は「m”月”d”日”」が選択されていました。

これはエクセルが「5/6」を自動的に「2019/5/6」のことだと認識し、このような書式設定を割り当てているのです。
では分類を「標準」に変更すると、サンプルに「43591」と表示され、「OK」を押して適用すると、セルと数式バーにはそのまま「43951」が表示されました。

この私たちには一見何のことか分からない数字の羅列が、エクセルにとっては「2019/5/6」のことなのです。

逆に「43952」と入力して、書式設定を日付に変更すると「2019/5/7」と表示されます。

このようにエクセルが日付と認識する数字のことを「シリアル値」と呼びます。

特にどのシリアル値がいつの日付になるということは、まったく覚えなくていいです。
ここで覚えていただきたいのは、「エクセルは日付をシリアル値で認識する」ということです。

なぜ覚えていただきたいかというと、先ほど行った書式設定の変更で、日付の表示形式を適用する際にセル内の文字が「シリアル値かそれ以外か」で日付に変更できるかできないかが決まるからです。

シリアル値以外を説明しましょう。
たとえばもともと書式設定の分類が「文字列」のセルに「5/6」を入力します

すると先ほどと違い、数式バーも「5/6」となっています。
書式設定を開いて、ユーザー定義からでも日付からでもいいので書式を日付に変更してもこのままです。

これはエクセルがこの「5/6」を文字列と認識しているため、書式設定からでは書式を変更できないのです。

これが「シリアル値以外の日付」です。
他にも「20190506」といった数値も日付として認識しません。

対象のセルの値が「シリアル値かシリアル値以外か」を理解した上で、表示形式をどのように変更すればよいか判断することができます。

エクセルで日付を扱うのなら「シリアル値」という言葉と、それがどういうものを指すのかは覚えておきましょう。

1-2.日付に変換したくないときの対処法

日付変換する前に、もう一つ覚えたいことがあります。
ずばり「エクセルの自動変換の制御」です。

日付ではなく「5/6」や「5-6」などを入力したい場合でも、エクセルはそれを日付と認識してシリアル値に変換してしまいます。
そのため日付を扱う場合は、日付に変換しないようにすることも覚えておくとよいでしょう。

残念ながら日付変換をオフにすることはできないので、手動で行います。
方法は4通りあります。

1-2-1.セルの書式設定から「文字列」にする

対象のセルを選択し、右クリックでメニューを開き、「セルの書式設定」を開いて分類に「文字列」を選択します。

ショートカットを使う場合は、対象のセルを選択し、Ctrlキーを押しながら1キーを押して「セルの書式設定」を開きます。

するとセルは文字列と認識され、自動で変換されなくなります。
重要なのは「入力より前に書式設定する」ことです。

エクセルでは数値でなければ左寄りになるため、表示は左寄りになります。

1-2-2.値の前に「 （スペース）」を入力する

値の前に半角スペースを入力すると、日付と認識されません。
ただし半角スペースは表示され、値は文字列と認識され左寄りになります。

1-2-3.値の前に「’（アポストロフィ）」を入力する

値の前にアポストロフィを入力すると、日付と認識されません。
つまり「’5/6」です。

アポストロフィはセルの表示から消え、値は文字列と認識され左寄りになります。
数式バーにはアポストロフィは表示されます。

MATCH関数やVLOOKUP関数などの計算時にアポストロフィは無視されます。
そのため関数の計算に使用したい場合は、半角スペースではなくアポストロフィを使用します。

1-2-4.値の前に「0と （スペース）」を入力する（分数の場合）

分数の場合は値の前に0と半角スペースを入力することで、分数として認識されます。
「0 5/6」というように入力します。
0と半角スペースはセルの表示から消え、数式バーでは少数が表示されます。

このまま計算に使うことも可能です。
分数も少数も数値なので、セルの表示は右寄りです。

2.いろんな日付変換を覚えよう

日付の扱いについて、シリアル値と日付変換の制御を覚えたところで、ようやく日付変換に入りましょう。

ここでは「表示形式」から表示を変更する方法とシリアル値以外を日付に変換する方法を紹介します。
どのような場合でも、この2つの方法のどちらかに当てはまります。
どちらも覚えて日付の扱いをマスターしましょう！

2-1.シリアル値の日付の表示形式を変更する

シリアル値の日付の表示形式を変えたいときは、関数などを使わず「セルの書式設定」を利用しましょう。
セルの書式設定は入力済みのセルを選択して右クリックか、Ctrlキー+1キーで開きます。

2-1-1.西暦や和暦に変換する

日付を西暦に変更したい場合、セルの書式設定の分類「日付」から変更できます。
和暦も同様に分類「日付」のカレンダーの種類を「和暦」に変更すると、和暦形式の候補が表示されます。

2-1-2.自由に表示形式を変更する

西暦や和暦以外に「yyyymmdd」形式や「ddmm」形式など、分類「日付」では対応できない場合は、「ユーザー定義」を使用します。

ユーザー定義にはすでに様々な日付形式が入っています。
頭に「[$-ja-JP]」が付いている形式は、和暦のことです。

ユーザー定義は基になる表示形式を選択し、種類ラベルの下のテキストボックスで自由に新しい表示形式を入力します。

表示形式は基本的に年月日を表す「ymd」と文字列を囲む「”（ダブルクォーテーション）」を使用します。
「y」は年、「m」は月、「d」は日を表します。
たとえば「yyyy”年”m”月”d”日”」は「2019年5月6日」になります。

このような入力方法で気をつけたいのは、それぞれのアルファベットの数です。
先ほどの「yyyy”年”m”月”d”日”」を「yy”年”mm”月”dd”日”」に変更した場合、「19年05月06日」になります。

年の場合yの数が1～2個ならば下2桁、3～4個ならば4桁になります。

月（m）や日（d）の場合、1個ならば頭に「0」が付かず、2個なら「0」が付きます。
さらに3個ならば英語の省略になります。

たとえば「mmm-ddd」ならば「May-Mon」となります。
4個以上ならば、省略せず英語の月と曜日が表示されます。

2-2.シリアル値以外の表示形式を変更する

シリアル値以外の表示形式を変更する場合、セルの書式設定からでは設定できません。
その場合は関数を使用します。

関数を使うといくつも方法が出てきますが、ここではあまり多くの種類の関数を使わずに、シンプルに覚えやすい関数での日付変換を紹介します。

2-2-1.8桁や6桁の数字を日付にする

たとえばA3に入力された「20190506」を「2019年05月06日」としたい場合、MID関数を使います。

MID関数は文字列を区切って表示する関数です。
使い方は以下の通りです。

MID(文字列または対象セル番号, 開始位置, 文字数)

開始位置は1桁目を1として数え、文字数は開始位置から数えます。
「20190506」の年の「2019」は1桁目から始まる、4文字です。
そのため「MID(A3,1,4)」となります。

対象が6桁でも同じです。
MID関数の開始位置と文字数の数値を変更して対応できます。
また上記の例の“年”をスラッシュにしたり、ハイフンにしたりして自由に表示形式を変更しましょう。

3.シリアル値と文字列の互換

最後にシリアル値から文字列へ、文字列からシリアル値への変換ができるようになりましょう。
関数を使いますが、それほど難しくないので関数が苦手な人も試してみてくださいね。

3-1.シリアル値から文字列へ変換

シリアル値から文字列への変換は簡単です。
たとえばA1にシリアル値で「2019/5/6」が入っている場合、「=TEXT(A1, “yyyy/m/d”)」で文字列になります。

TEXT関数の使い方は以下の通りです。

表示形式はセルの書式設定のユーザー定義の種類をそのまま貼り付けてもかましません。
ただダブルクォーテーションで囲みましょう。

3-2.文字列からシリアル値に変換

反対に文字列からシリアル値にする場合、文字列がどのような入力かにより変換方法が変わります。

まずA1に入っている「2019/5/6」といった文字列でも日付と認識できる場合、「=VALUE(A1)」と入力します。
すると結果にシリアル値の「43591」が返ります。

VALUE関数の使い方は以下の通りです。

DATEVALUE関数も同じ使い方で同じ結果が返ります。
VALUE関数の方が変換対象の幅が広いので、VALUE関数でよいでしょう。

8桁の数字をシリアル値に変換したい場合、「2-2-1.8桁や6桁の数字を日付にする」で紹介したMID関数とVALUE関数を使用します。

「2-2-1.8桁や6桁の数字を日付にする」では例に「=MID(A3,1,4)&"年"&MID(A3,5,2)&"月"&MID(A3,7,2)&"日"」を紹介しました。
この式の年月日をスラッシュに変更します。
つまり「=MID(A3,1,4)&"/"&MID(A3,5,2)&"/"&MID(A3,7,2)」になります。

これで文字列が日付と目で見て認識できるようになりましたね。
そうなれば先ほどと一緒でVALUE関数を使ってシリアル値を求めます。

8桁の数字をMID関数で日付形式にして、それをそのままVALUE関数でシリアル値にしています。
これでシリアル値「43591」が返ります。

4.まとめ

エクセルで日付を入力することは、ビジネスシーンではよくあることです。

セルの書式設定の表示形式は便利な機能で、文字列で入力されていた場合も、書式設定を確認すればそれがシリアル値なのかそうでないのかが確認でき対応も判断できますよ。
表示形式を変更したい場合は、まずセルの書式設定を確認しましょう。

ほとんどの場合、書式設定で対応できるはずです。