ExcelやGoogleスプレッドシートで日付がシリアル化してしまう!?|「yyyy/mm/dd」表記にしたい

普段からよく使用するExcelやGoogleスプレッドシート。日付を入れて、金額などの様々な数値を入力されるかと思います。

しかし、日付を入れる際に「20160101」と入力し後から表示形式を日付に変更しようと思ったら…出てきた数字は「57096/06/23」!そんな経験をしていらっしゃる方はいらっしゃいませんか?

今日はシリアル化してしまった数字を日付で表示する方法や便利なワザをお伝えできたらと思います!

表示形式を日付で選択しているのになぜシリアル化してしまう?

ExcelやGoogleスプレッドシートでは日付に「シリアル値」という番号をつけて計算しています。1990年1月1日を「1」としてそこから番号を振り分けているため…1990年1月2日は「2」というよう表示されます。なので1990年1月1日から「20160101」たった日付⇒「57096/06/23」といった表示になってしまうのです。

関数を使用してまずは日付にしてみる

代表的な関数は「DATE」

=DATE(2016,1,1)とセルに入力すれば表示は「2016/01/01」となります。連番にするのであれば、先ほどの=DATE(2016,1,1)が入力されているセルの番号に「+1」としていくと次のセルには「2016/01/02」と表示されます。
例えば…
=A1+1 のような数式です。もしくはセルに「2016/01/01」と入力していただければ大丈夫なのですが…レポートをとってきた場合に「20160101」となってしまっているとこれは使えません。

「TEXT」関数が使える!

レポートなどをとってきて貼り付けた場合に、「20160101」となっている場合はありませんか?それを日付にしたい!そんなときに使用できる関数が「TEXT」です。

=TEXT(A1,”0000!/00!/00”)*1と入力して、表示形式を日付にすることで「2016/01/01」と表示させることができます。

ポイントは「!」や「*1」を使用することで、ExcelやGoogleスプレッドシートが日付をシリアル値ではなく数値データとして扱ってくれることです。数値データとして扱うので「0000/00/00」だけだと割り算になってしまうので、「!」や「-」「”」を使用することで割り算をするのを防いでくれます。

Googleスプレッドシートの場合

Excelの場合だと上記の
=TEXT(A1,”0000!/00!/00”)*1で大丈夫だったのですが、Googleスプレッドシートの場合はこれでは上手くいきませんでした。

Googleスプレッドシートでは
=TEXT(A1,”0000””/””00””/””0”)*1
もしくは、
=TEXT(A1,”0000-00-00”)*1

とすることで上手くいきました!

これでも上手くいかない場合は…

無理くりですが、文字を結合するといった関数で「MID」関数を使用するやり方もあります。

=(MID(A1,1,4)&”/”&MID(A1,5,2)&”/”&MID(A1,7,2))*1

このようにA1に入力されている「20160101」の1文字目から4つ数字をとってきなさい。といった関数になります。それを&で”/”をくっつけて日付に見せるといったやり方になります。または、一度秀丸などで編集するといったやり方も…少し手間ですが、どうしてもといった場合には一度試してみて下さいm(_ _)m

Excelでは使えるが、Googleスプレッドシートでは使えない!といったように少し違う部分がありますが参考にしていただければ幸いです。

このエントリーをはてなブックマークに追加