【初心者向き】エクセルで日付のデータ・形式を変換する方法まとめ

公開日: 日付

エクセルを使っていると、次のように、日付データを自分好みの形に整えたい場面が出てきます。

  • 日付を「2017/4/1」ではなく「2017年04月01日」、「17/4」と表記したい
  • 「20170401」というデータを「2017/4/1」という形に変換したい
  • 「2017 4 1」というデータが別々のセルに入っているのを「2017/4/1」という形に変換したい
  • インターネットバンキングでダウンロードしたCSVデータ内の日付を使いやすい形に変換したい
  • 文字列データをシリアル値に変換したい
  • シリアル値を文字列データに変換したい

そこで、このページでは、上記のような変換をするために必要な知識を解説していきます。

日付は、エクセルでどのように扱われているか?

まず、最初に知っておかないといけないのが、日付データが、エクセルでどのように扱われているか?です。

日付データは、エクセルでは(大きく分けて)次の3つの形式で取り扱われています。

パターン1:シリアル値

たとえば、エクセルを開いた直後の状態で、A1セルに「2017/4/1」と入れてみてください。

hidukehenkan_1_1

「2017/4/1」と表示されます。

ここで、A1セルの表示形式を「標準」に変えてみてください。

hidukehenkan_1_2

表示が「42826」に変わってしまいました。

実は、エクセルは日付が入力されると、勝手に、日付に対応した「数値」に変換して管理をしています。

この数値のことを「シリアル値」と言います。

※詳細はこちら → エクセルのシリアル値とは?それを使うメリットは?

パターン2:シリアル値以外のデータ

今度は、別のシートに、あらかじめ、A1セルの表示形式を「文字列」にしたうえで、「2017/4/1」と入れてみてください。

hidukehenkan_1_3

「パターン1」と同様に「2017/4/1」と表示されます。

ここまでは、一見同じ結果のように見えますが、A1セルの表示形式を「標準」に変えてみてもデータは変わりません。

今回のように「あらかじめ表示形式を文字列にして」日付データを入れることで、エクセルは入力された文字をそのまま記録してくれます

パターン3:複数セルを使ったデータ

「複数」のセルで、日付データを管理する方法もあります。

例えば、「2017/4/1」というデータを表現するために、

  • A1セル:2017
  • B1セル:4
  • C1セル:1

と入力するイメージです。

hidukehenkan_1_4

パターン1~パターン3までの関係を図解すると次のようになります。

  データの形式
シリアル値 その他
入力に使うセル 単一セル パターン1 パターン2
複数セル (該当なし) パターン3

あなたが日付データの「変換」をしようとするときには、どのパターンからどのパターンへの変換かを意識することで、適切な方法を選択できるようになります。

かなり大ざっぱに分けると、

  • 「パターン1」から「パターン1」へは、セルの表示形式を変えることで変換する
  • それ以外の場合は、様々な関数を駆使して変換する

ことになります。

日付変換の具体例

それでは、実際に日付変換の具体例を見ていきます。

西暦を和暦に変換したい

A1セルに入っている「2017/4/1」というデータを「平成29年4月1日」というデータに変換します。

1.元データの形式の確認

まず、元々のデータの形式を確認します。

A1セルの書式設定画面から、表示形式を「標準」にします。その結果、表示が「42826」に変われば、A1セルに入っているデータは「パターン1:シリアル値」であることがわかります。

hidukehenkan_1_1
↓セルの表示形式を「標準」に変更
hidukehenkan_1_2
2.変換後のデータ形式を決める

次に、変換後のデータ形式を決めます。

特に、こだわりがなければ「パターン1:シリアル値」を使うといいでしょう。

3.変換をする

今回のような「パターン1」から「パターン1」への変換の場合には、セルの書式設定の「表示形式」を変更するだけで、日付形式の変換をすることができます。

A1セルの上で右クリックし「セルの書式設定」→「表示形式」→「日付」から、好きな書式を選びます。

hidukehenkan_1_5

hidukehenkan_1_6

表示形式「日付」にない表示をさせたい

表示形式の「日付」の欄には、「2017/4」というような表示形式は存在していません。

このような表示のさせ方をしたい場合には、どうすればいいでしょうか?

1.元データの形式の確認

まず、元々のデータの形式を確認します。

A1セルの書式設定画面から、表示形式を「標準」にします。その結果、表示が「42826」に変われば、A1セルに入っているデータは「パターン1:シリアル値」であることがわかります。

hidukehenkan_1_1
↓セルの表示形式を「標準」に変更
hidukehenkan_1_2
2.変換後のデータ形式を決める

次に、変換後のデータ形式を決めます。

今回も、「パターン1:シリアル値」を使うといいでしょう。

3.変換をする

「ユーザ定義書式」を使って変換を行います。

A1セルの上で右クリックし「セルの書式設定」→「表示形式」→「ユーザー定義」を選択します。

種類欄に「yyyy/m」と入力します。

hidukehenkan_1_7

hidukehenkan_1_8

※「yyyy」=年を4桁の西暦で表示、「m」=月を表示 という意味になります。

これで、「年/月」という形式で、日付を表示することができました。

文字列の日付をシリアル値に変換したい

先ほどの2つの例は、元々のデータが「シリアル値」でした。

元々のデータが「文字列」の場合、シリアル値に変換すると、エクセルで便利に扱えることが多いので、シリアル値に変換してみます。

これは、「パターン2」から「パターン1」に変換に該当します。

1.元データの形式の確認

元々のデータが文字列かどうかは、表示形式を「標準」にしても、表示される値が変わりません。

hidukehenkan_1_3
※セルの表示形式を「標準」に変更しても表示が変わらない
2.変換を行う

それでは、先ほどのデータを「シリアル値」を使った日付表記に変換をします。

今回のように、元データの見た目が「日付データとして認識できる形」になっている場合には、value関数を使うだけで簡単にパターン1に変換ができます。

なお、value関数を入れた結果、よくわからない数値が表示される場合には、セルの表示形式を「日付」に変更すると、目的の日付が表示されます。

hidukehenkan_1_13
=value(A1)

年月日が個別に入力されているデータを、通常の日付に変換したい

ジャパンネット銀行のCSVデータのような「年・月・日」が別々のセルに入っているデータを、1つのセルにまとめます。

要は、「パターン3」→「パターン1」に変換したいということです。

この場合は、次のE1セルのように「date関数」を使って変換をかけます。

hidukehenkan_1_9
=date(A1, B1, C1)

8桁の日付を、通常の日付に変換したい

「20170401」というような、

  • 最初の4桁:年
  • 次の2桁:月
  • 最後の2桁:日

というデータ(=「パターン2」のデータ)を、「2017/4/1」という「パターン1」のデータに変換します。

「パターン2」→「パターン1」の変換は、元データの形に応じて、変換する方法は大きく変わりますが、大抵の場合、次の関数を使うと、うまくいきます。

  • value関数
  • date関数
  • left関数
  • mid関数
  • right関数
  • find関数
  • concatenate関数

今回の場合であれば、次のように、date関数・left関数・mid関数・right関数の4つを使うとうまくいきます。

hidukehenkan_1_10
=date(left(A1,4), mid(A1,5,2), right(A1,2) )

date関数は、「年」「月」「日」を指定すると、それに対応する「シリアル値」を計算する関数です。

そこで、date関数に、

「年」: A1セルの左4文字(=2017) left(A1,4)
「月」: A1セルの5文字目から2文字(=04) mid(A1,5,2)
「日」: A1セルの右2文字(=01) right(a1,2)

を入れることで、2017/4/1に対応する「シリアル値」を計算しています。

シリアル値を文字列データに変換したい

エクセルでデータ処理をする場合、日付をシリアル値ではなく、文字列のデータとして管理したい場合があります。

要は「パターン1」から「パターン2」に変換をしたいという場合です。

このような場合には、text関数を使います。

hidukehenkan_1_11
=text(A1, "YYYY/M/D")

text関数の使い方

text関数は、2つの引数(※)から構成されています。

※引数というのは、関数中のカンマで区切られている部分で、今回の関数だと、1つめの引数が「A1」、2つめの引数が「"YYYY/M/D"」です。

  • 1つめの引数:文字列形式に変換する「元」のセルを指定します
  • 2つめの引数:どのような形式で日付を表示させるかを決めます

2つめの引数の「形式」をどう書くかは、次の方法で簡単にわかります。

たとえば、A1セルと同じような形式を指定したい場合には、A1セルの上で右クリックをして「セルの書式設定」→「表示形式」→「ユーザ定義」を選択してください。

hidukehenkan_1_12

そして、種類欄に表示されている「文字」をそのまま、text関数の2つめの引数として指定することで、A1セルと「同じ」見た目の文字列データを生成することができます。

まとめ

エクセルで日付のデータ・形式を変換したい場合には、変換前・変換後のデータが「パターン1」~「パターン3」までのどれに該当するかを考えましょう。

どのパターンに該当するかがわかると、どのような手法で変換をかければいいか、だいたい、見当が付くようになります。


  • twitter
  • facebook
  • はてなブックマーク

経理事務のためのエクセル基礎講座動画マニュアル無料配布中

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!