Excelでデータ集計をする際、元データの取得方法の違いなどによって、日付の表記方法が一貫していないことがあります。
たとえば、文字列で「2019/10/1」「2019年10月1日」「令和元年10月1日」といった様々な表記や、文字列中に空白が含まれるなど。
Excelでは、日付形式（シリアル値）に自動変換してくれるもの、単なる文字列と認識されるものがあり、期間集計の結果や並び替え等に問題が生じることもあります。
しかし、日付の表記方法が一貫していないデータを集計・分析する業務は時折発生。
日付に表記ゆれがあった場合の変換結果の違いについて、Excelの一般的な数式とPowerQueryで調べたので、その備忘録。
なお、使用のExcelは、Microsoft® Excel® for Microsoft 365 MSOです。
調べた３つの方法と主な特徴は次のとおり。
- ExcelのDATEVALUE関数（一般的な数式）
全角文字も日付形式に比較的可能。 - PowerQuery のデータ型変更
全角文字に弱い。
半角ならDATEVALUE関数より変換可能（空白含む、ピリオド型など）。 - PowerQuery のDate.From関数
上記「PowerQuery のデータ型変更」と同様
いずれの場合も、和暦の元年はシリアル値に適切に変換できないようなので、”１年”などに一括置換が必要と思われる。
なお、詳細の結果は下表のとおりなので、関心のある方はご参考に。
表 Excelの一般的な数式とPowerQueryでの日付文字列の変換結果
上記表の補足説明：
「日付」欄は、様々な表記ゆれのサンプル。年月日の区切文字が「スラッシュ」、「年・月・日」「ハイフン」「ピリオド」「スペース」や数字８桁のyyyymmddの形式。半角や全角の違い、西暦と和暦（令和元年、令和１年、R1）での違い、文字列中の空白有無のサンプル。
「日付」のサンプルデータを変換した結果を「ExcelのDATEVALUE関数」「PowerQuery のデータ型変更」「PowerQuery Date.From」の欄に記載している。この欄に「2019/10/1」が表示されているデータは適切にシリアル値に変換できている。空欄は変換できず。
以上、Excelの一般的な数式とPowerQueryでの変換結果の違いについて調べた結果でした。
変換できなかった日付データは、個別に変換処理等の追加が必要です。PowerQueryの場合、”元年”を”1年”に一括置換して、全角数字を半角数字に変換すれば日付置換は良い精度でできそうです。
参考：Text.Replace関数を使用して全角を半角に一括置換するときの注意点
Excelだと、TEXT 関数や、スペース削除などの組み合わせかと思います。
日付の表記方法を事前に統一されていると良いのですが、困難な時の備忘録でした。
PowerQuery のデータ型変更の手順
PowerQuery でデータ型の変更は次のとおり、とても簡単に可能です。
