Googleスプレッドシート 日付の読み取りがおかしい

スプレッドシートで入力した日付が正しく変換されないときの対処法をメモメモ…

たとえば次のような日付のデータがあったとします。

A: 09-05-1958
B: 02-10-1959
C: 12-05-1956
D: 08-23-1956
E: 09-19-1957

この A の日付はいったい何月何日を指しているでしょうか？

答えは簡単ですね。D と E を見れば mm-dd-yyyy (月-日-年)というアメリカ式の表記方法で並んでいることは明らかなので、A が指しているのは 5月9日 ではなく 9月5日 であるはずです。ちなみにこの日付は「ずうとるび」のメンバーの誕生日なのですがそれは今はどうでもいいです。

問題はこのデータをスプレッドシートに渡した場合です。実際に入力してみるとこんな結果になります。

A	1958年05月09日
B	1959年10月02日
C	1956月05月12日
D	08-23-1956
E	09-19-1957

A、B、C は自動的に日付として認識され、指定した表示形式に合わせて整形されているようです(形式はメニューの「表示形式」>「数字」から設定可能)。ただし月と日の順序が逆になってしまっていますね。一方 D と E は日付として認識されておらず入力された値がそのまま表示されています。

日付として扱われているセルはクリックするとカレンダーが表示される

どうやら日付を認識するときに dd-mm-yyyy (日-月-年)という並びで処理をしているため、今回のように mm-dd-yyyy (月-日-年)の表記だとうまく読み取れないことが原因のようです。

対処法その1　スプレッドシートの地域設定を変更する

最も手っ取り早い解決策です。今回の問題の原因は入力データの年月日の順序が想定している順番と違うことです。年月日の順序はファイルの地域設定に準拠しているため、これを「日本」から他の地域に変えることで正常に日付が認識されるようになります。

設定方法は、まずメニューバーから「ファイル」>「Google スプレッドシートの設定」をクリック。あとは「言語と地域」を適切な地域に変更すればOKです。今回の例では mm-dd-yyyy (月-日-年)というアメリカ式の表記方法で認識してほしいので「アメリカ合衆国」を選択します。

「言語と地域」を変更する

これだけで日付形式の判定方法が変わるはずです。ただしすでに日付データが入力されてしまっている場合は一旦削除して入力し直さないと反映されないので注意。

対処法その2　日付の自動整形をオフにする

続いて「とにかく入力したデータが勝手に日付になおされるのを防ぎたい！」という場合の対処法です。

まずデータを入力する前に対象のセルを選択します。その状態でメニューバーの「表示形式」>「数字」をクリックし「書式なしテキスト」を選んでください。とりあえずこれで勝手に入力データが整形されるのを防ぐことができます。

「表示形式」>「数字」>「書式なしテキスト」

地域を変更する方法だとファイル全体に影響が出てしまいますが、この方法なら対象のセルを変更するだけですみます。すでにデータを入力した後(自動整形されてしまった後)だとおそらくうまくいかないので注意しましょう。

対処法その3　関数を利用してデータを修正する

スプレッドシートで困ったときは関数とスクリプト(Google Apps Script)を使えばあらかた解決できます。ただし日付の取り扱いは Microsoft Excel と同じような仕様になっていて若干ややこしいので、予備知識がないと混乱するかもしれません。

スプレッドシートにおける日付のデータというのは、実際は「シリアル値」と呼ばれる整数値として扱われています。シリアル値は1899年12月31日を 1 とした連続する数字で

　1899年12月31日 … 1
　1900年01月01日 … 2
　1900年01月02日 … 3
　…
　1964年10月10日 … 23660
　…
　2020年07月24日 … 44036

というように数字と日付を対応させたものです。

たとえば適当なセルに「2」と入力して表示形式を日付に変更してみてください(「表示形式」>「数字」>「日付」)。「1900/01/01」のように表示されるはずです。指定された表示形式に従って、シリアル値が自動的に日付に変換されているんですね。セル内の値が「テキスト」データなのか、それとも内部ではシリアル値として扱われている「日付」データなのかをきちんと把握しておかないと、意図しない出力結果になってしまうので注意しましょう。

というわけで日付データの修正に役立ちそうな関数をいくつか紹介します。

REGEXREPLACE

REGEXREPLACE は正規表現を使ってテキストを置換するための関数です。たとえば

=REGEXREPLACE(A1, "(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2")

と書いた場合、A1 が「09-05-1958」であれば「1958-09-05」に置換されるようになります。

ただし、この関数はあくまでもテキストを対象としたもので、1つめのパラメータに数値を指定するとエラーになります。元のデータがすでに日付として認識されてしまっている場合、そのデータは内部的にはシリアル値＝数値として扱われるため、この関数だけでは扱えません。

TEXT

TEXT は数値を指定した形式でテキストに変換するための関数です。この「数値」の部分を普通の数字とみなすか日付とみなすかは形式の指定方法で変わってくるので、詳しくはヘルプページで確認してください。

たとえば A1 に「1958年05月09日」という日付データが入力されていた場合

=TEXT(A1, "yyyy-dd-mm")

と書けば「1958-09-05」というテキストが出力されます。日付がテキストとして出力されるというのがポイントです。

ISDATE

ISDATE は指定した値が日付かどうかを判定するための関数です。たとえば

=IF(ISDATE("2019年09月31日"), "日付です", "日付じゃないです")

と書いた場合、9月31日は存在しないので、この式全体が「日付じゃないです」という値になります。これを使えば「日付として認識されていればこっちの処理、無効な日付であれば別の処理…」というように場合分けができます。

DATEVALUE

DATEVALUE は日付っぽい文字列をシリアル値に変換するための関数です。日付を入力したときに日付が自動で変換されますが、それを手動で行いたいときに使います。変換ルールはスプレッドシートの「言語と地域」設定に依拠します。

TO_DATE

TO_DATE はシリアル値を日付に変換するための関数です。たとえば

=TO_DATE(123456)

と書けば「2238年01月03日」のように日付になおして出力されます。

以上、とりあえずこの辺の関数を駆使すれば好きなように日付のデータを整形できると思います。

たとえばこんな感じで関数を組み合わせると

=IF(ISDATE(B1), TEXT(B1, "yyyy-dd-mm"), REGEXREPLACE(B1, "(\d{2})-(\d{2})-(\d{4})", "$3-$1-$2"))

対象のセルが日付として認識されているかどうかによって処理を変えながら整形することができます。

A	1958年05月09日	1958-09-05
B	1959年10月02日	1959-02-10
C	1956月05月12日	1956-12-05
D	08-23-1956	1956-08-23
E	09-19-1957	1957-09-19

おかしかった日付が修正され、正しい yyyy-mm-dd (年-月-日)の形にバッチリ揃いました。まあ、もっと簡潔な方法もあるかもしれませんがとりあえず目的は達成したのでよしとしましょう。