Spreadsheetで米国の日付表記を日本の日付表記に直す方法

Spreadsheetに限られないのですが、Importhtml等でサイトから取り込むと、米国・アメリカの日付表記が処理できない事があります。

下記例の様に、2022年1月3日ならば、”01/03/22″と表記され「2001年3月22日」と認識されます。

また一方で、2021年1月15日ならば、”01/15/21″と表記されますが、その場合は当然「2001年15月21日」とは認識されず、テキストと認識されます。

例)

Date 1 mo
01/03/22 0.05
01/04/22 0.06
01/05/22 0.05

 

Case1: 日付が1-12までに収まるケース

下記で動作します。

=datevalue(TEXT(A3,“dd/yy/mm”))

表示形式を変えるTEXT関数で順番入れ変えます。これだけですと文字列として認識されるので、再びDateValue関数で日付の関数とします。参照セルが「日付形式」である前提になります。

 

Case2:日付が13以上もカバーするケース

力技で書くと下記になります。

=date(2000+RIGHT(A8,2),LEFT(A8,2),MID(A8,4,2))

これですと1900年代非対応です。必要ならば、頑張ってIF分を足しましょう。

=date(If(value(RIGHT(A8,2))>year(today()2000),1900,2000)+RIGHT(A8,2),LEFT(A8,2),MID(A8,4,2))

 

Arrayformulaでの利用表記

結果、Arrayformulaを使って1セルの入力にするには下記の様な表記になりました。

=arrayformula(if(A:A=“”,“”,date(IF(value(RIGHT(A2:A,2))>year(today()2000),1900,2000)+RIGHT(A2:A,2),LEFT(A2:A,2),MID(A2:A,4,2))))

 

面倒なのでロケールを変更する

米国に住んでいる人はこの問題にはあたりませんので、ロケールを米国に変えてしまう方法があります。

日本のデータの取り扱いの問題がでます。

https://support.google.com/docs/answer/58515?hl=en