Excelの日付変換、R8.1.7は通るのに2026.01.15が#VALUE!になる

複数の人が入力したExcelや、別々のシステムから出したCSVを1つにまとめると、同じ「日付」列なのに書き方がバラバラ、ということが起こります。この状態だと月別の集計ができません。

実際にどこまでが自動で直せて、どこが引っかかるのかを、Excelで一通り試した結果をまとめます。

まず、なぜ集計できないのか

セルに入っている日付には2種類あります。Excelが日付として認識している値と、単なる文字列です。見た目はほとんど同じですが、文字列のままだと月ごとの集計もフィルタも効きません。

見分け方は2つあります。

・何も書式を変えていない状態で、右に寄っていれば日付、左に寄っていれば文字列

・=ISNUMBER(A2) が FALSE を返せば文字列

セルの左上に緑の三角が出ているときも、文字列として入っている合図です。

試した結果

A列にそれぞれの書き方で入力し、=DATEVALUE(A2) を通しました。結果は次のとおりです。

【そのまま通ったもの】

2026/1/5 → 2026/01/05

2026-1-5 → 2026/01/05

2026年1月6日 → 2026/01/06

令和8年1月7日 → 2026/01/07

R8.1.7 → 2026/01/07

H31.4.30 → 2019/04/30

２０２６/１/５（全角） → 2026/01/05

【エラーになったもの】

2026.01.15 → #VALUE!

意外に思われるかもしれませんが、和暦の略記（R8.1.7）も全角も、そのまま通ります。日付として認識されないのは、ピリオド区切りだけでした。

なお和暦が通るのはWindowsの地域設定が日本語のときの挙動です。海外拠点のPCで開くと結果が変わることがあります。

ピリオド区切りだけ、ひと手間かける

2026.01.15 は、ピリオドをスラッシュに置き換えてから渡せば通ります。

=DATEVALUE(SUBSTITUTE(A2,".","/"))

前後に余分な空白が入っている可能性もあるので、実務ではまとめてこう書くのが安全です。

=IFERROR(DATEVALUE(SUBSTITUTE(ASC(TRIM(A2)),".","/")),"")

ASCは全角を半角に、TRIMは余分な空白を落とします。IFERRORで囲んでおくと、どうしても変換できなかった行だけが空欄で残るので、あとから目視で拾えます。

この式で、上に挙げた8種類はすべて日付に変換できました。

変換したあとにやること

DATEVALUEが返すのは数値なので、そのままでは「46027」のような表示になります。表示形式を「短い日付形式」に変えるか、=TEXT(...,"yyyy/mm/dd") で文字列に整えてください。

集計に使うなら、表示形式を変える方を選んでください。文字列に戻すと、また同じ問題が起きます。

元の列は消さない

変換は必ず新しい列に作り、元の列はそのまま残してください。変換に失敗した行を後から確認できなくなります。作業が終わって検算まで済んでから、元の列を非表示にするか削除します。

それでも手が止まるとき

ここまでは日付の話でしたが、実際に持ち込まれるファイルは、たいてい同時にほかの問題も抱えています。

・金額が「840円」「¥3,120」と文字列で入っていて合計が出ない

・同じ会社が「ＮＥＷ新宿店」「新宿 店」と別表記になっている

・明細の途中に小計行が挟まっている

・月ごとにシートが分かれている

1つずつなら関数で片付きますが、これが全部同時に来ると手数がかかります。行数が多い場合や毎月同じ作業が発生している場合は、外に出してしまうのも手です。

筆者は、こうした状態のExcelをそのまま受け取って、整形から集計表・グラフまで仕上げてお返しするサービスをココナラで出しています。手が止まっている方は下のサービスをご覧ください。

→サービスページ：バラバラなデータ、整形して集計表にします