海外から受け取ったExcelファイルの日付を綺麗に治したい

Excelによって汚される日付項目を綺麗に浄化する

各国のデータを受け取った際、日付の項目が日本の「年月日」の並びになっておらず困ったことありませんか。
受けとったファイル形式次第でそれぞれ対処が変わってきますので、備忘としてまとめます。

まずは世界の日付並び順を把握する

日本	年月日:YYYY/MM/DD	2020/03/10
アメリカ	月日年:MM/DD/YYYY	03/10/2020
イギリス	日月年:DD/MM/YYYY	10/03/2020

この例でもあるように、ファイル送付元の国のルールを確認しないと一見しただけでは3/10なのか10/3なのかがわかりません。
これを避けるためにMar-10-2020としてる場合もありますが、その場合はExcelも誤判定しにくいのであまり心配する必要はありません。

この記事ではすべて数値で記載された日付を受け取り、かつ送付元へ修正を依頼するのが難しい場合（力関係やITスキルの関係で）になんとか綺麗に治すための手順をまとめます

１．CSVやTXTファイルをExcelで開く

CSVファイルの場合

通常、Excelがインストールされているパソコンでダブルクリックすると自動的にExcelが起動して各列を勝手に解析・勝手に型を判断してくれます・・が、海外の日付型で「年月日」の並びになっていないファイルを開くと誤判定してしまう可能性があります。とはいえ、とりあえずダブルクリックで開き、Excelの努力を見てみましょう

-元データと同じ型の日付順で表示された場合

アメリカからのファイルの日付がすべて「MM/DD/YYYY」で表示された場合です。この場合は対処は簡単です。

＜対処方法＞

対象の列を選択（A,B,Cの列名をクリック）
リボンメニューより　「データ」タブ→区切り位置を選択
区切り位置指定ウィザード画面が出てきたら1/3 , 2/3は何も設定変えずに「次へ」で進める
「列のデータ形式」画面が出てきたら「日付型」を選択しリストボックスから送付元の形式を選択する（Y=年 M=月 D=日となっています）
結果を確認する。思った表示になっていない場合はセルの書式設定を変更

-日付によって異なる日付順で表示された場合

Excelによって日付項目が一番汚されるパターンがこれです。
西暦年を略して2桁でセットしているイギリス並びの日付で発生しがちです。

日本語版Excelで日本以外の並び順になっている日付を判定した場合、基本はアメリカ式と判断して日付変換されます。が、月日の組み合わせが実際に無い日付であった場合、元のデータをテキスト形式のまま表示するか、西暦の先頭2桁が不明ですといったエラーで表示されるかのどちらかとなります。

例

元データの数値	日本表示	想定されるExcel判断	Excelの表示
10/03/19	2019/03/10	アメリカ並びと判断	2019/03/10
15/03/19	2019/03/15	アメリカ並びと判断するも 15/03という日付は存在しない	15/03/19*

*はテキスト形式となっているか、日付判定エラーとなっているかのどちらかです(日付判定エラーならセル左上に緑色のマークが出ているはずです）

＜対処方法＞

元ファイルの拡張子をCSVからTXTに修正(ファイル名を変更で修正する。＊拡張子が出ていない場合はフォルダオプションから変更してください）
TXTに変更したファイルを右クリック→「プログラムから開く」→Excelを選択
全てのデータがA列に張り付くのでA列全体を選択（列名Aをクリック）
リボンメニューより　「データ」タブ→区切り位置を選択
区切り位置指定ウィザード1/3では「カンマやタブなどの区切り文字」を選択して「次へ」をクリック
2/3の区切り文字はカンマ「,」を選択して「次へ」をクリック
（クリック前に下部の表示で列が綺麗に分かれていることを確認）
「列のデータ形式」画面が出てきたら日付列を選択して「日付型」を選択し、リストボックスから送付元の形式を選択する（Y=年 M=月 D=日となっています）
その他項目は必要に応じて形式を選択（基本は標準のまま放置でかまいません）
結果を確認する。思った表示になっていない場合はセルの書式設定を変更

TXTファイルの場合

-日付によって異なる日付順で表示された場合　の　手順.2から進めてください。

２．受け取ったファイルがExcelファイル

-全ての日付が同じ並び順の場合

この場合はCSVの時と一緒です。ただ、一見して綺麗に見える場合でも実はExcelによって誤った日付に変換されている可能性がありますので、送付元に念のため確認した方が良いかもしれません。

私は香港から受け取ったファイル（香港はイギリスの並び順）で「10/03/19」となっていた日付を「2019/03/10」だと思って見ていたところ、後で確認したら「2019/10/03」だったということがあります。

香港の担当のPCのスクリーンショットを送付して貰ったところ、その画像ではExcel上に「03/10/19」と表示されていました。私のPCで開くタイミングで誤変換された可能性が高いです。（大きな勘違いなので改めて原因を探ってみます）

-日付によって異なる日付順で表示された場合

原因はCSVの時と一緒ですが、Excelで受け取っていることにより変換元からデータをただす方法（txtにしてから型を指定）ができません。

私が行った対処としては、この問題は「イギリスの日付順を日本のExcelで開いた場合」に発生するという前提条件あると仮定して関数で変換しました。

/*日付判定・変換の関数（A2のセルが日付だった場合）*/
=iferror(IF(DAY(A2)<=12,DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE("20"&RIGHT(A2,2),MID(A2,SEARCH("/",A2)+1,FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1),LEFT(A2,2))),"")

考え方としては「日が12以下の場合は月と日を逆転・勘違いした誤変換されているので修正、そうでない場合は元データと同じ並び順のデータが入っているのでそのままデータセット」という考えです。

もっとスマートな解決方法あると思うんですが、私のサーチ能力では見つけれませんでした。

というか、こんな状況がそもそもレアケースで送付元に修正依頼をだすかExcelで誤変換される前の元データの提供を依頼すればいいだけなんですよね。

私の場合、海外担当に基幹システムの出力機能を使ってデータ出力して貰ったのですが、その出力がExcel形式しか選択できずシステム担当者に依頼してもブラックボックス化してしまった保守切れの業務パッケージで生データの抽出方法がわからないと踏んだり蹴ったりな状況だったので、やむを得ず力業に出ています。

皆さんはこんな事態に陥る前にシステムリプレイスは定期的にしておきましょう。