CSV、業務システム、Webフォーム、他国の拠点から受け取ったデータには、2026/6/12、2026-06-12、2026年6月12日、20260612、12.06.2026のような異なる日付表記が混在します。
見た目が日付でも、Excel内部では文字列のまま保存されていることがあります。文字列の日付は、月別集計、経過日数、並べ替え、ピボットテーブル、グラフの時系列軸で正しく扱えません。
日付形式の統一では、表示形式をそろえることと、文字列をExcelの日付値へ変換することを分けて考えます。本稿では一つのサンプル表を最後まで使い、DATEVALUE・DATE・TEXTSPLIT・LET・MAP・ISNUMBERを順に適用します。
| 基本 | =DATEVALUE(A2) |
|---|---|
| 8桁 | =LET(source,A2,converted,DATE(LEFT(source,4),MID(source,5,2),RIGHT(source,2)),IF(TEXT(converted,"yyyymmdd")=source,converted,NA())) |
| 海外形式 | =LET(parts,TEXTSPLIT(A2,"."),DATE(INDEX(parts,1,3),INDEX(parts,1,2),INDEX(parts,1,1))) |
| 一括処理 | =MAP(A2:A10,B2:B10,LAMBDA(value,type,...)) |
最初に、記事全体で使用するサンプルデータを作る
A列に「元データ」、B列に「形式」を入力します。標準的な日付だけでなく、8桁文字列、海外形式、日時、年月、非日付を含めます。
| A | B | |
|---|---|---|
| 1 | 元データ | 形式 |
| 2 | 2026/6/12 | 標準日付 |
| 3 | 2026-06-12 | ハイフン区切り |
| 4 | 2026年6月12日 | 和文日付 |
| 5 | 20260612 | 8桁文字列 |
| 6 | 12.06.2026 | 海外形式 |
| 7 | 2026/6/12 14:30 | 日時 |
| 8 | 2026/6 | 年月 |
| 9 | 2026.6.12 | ピリオド区切り |
| 10 | 未定 | 非日付 |
文字列として確実に入力するため、先頭にアポストロフィを付けるか、次のように数式で再現します。
| A2 | ="2026/6/12" |
|---|---|
| A3 | ="2026-06-12" |
| A4 | ="2026年6月12日" |
| A5 | ="20260612" |
| A6 | ="12.06.2026" |
| A7 | ="2026/6/12 14:30" |
| A8 | ="2026/6" |
| A9 | ="2026.6.12" |
| A10 | ="未定" |
以降はA2:A10を固定して使用します。C列へ基本変換、D列へ明示的な分解結果、E列へ用途別の最終結果、F列以降へ検証結果を追加します。
POINT
日付クレンジングでは、先に年・月・日の並び順と区切り文字を確認します。「12/06/2026」が12月6日か6月12日かは、表記だけでは決められない場合があります。
日付値と表示形式は別のものである
Excelの日付は、内部では連続したシリアル値として保存されます。セルの表示形式を変えることで、同じ日付値を「2026/6/12」「2026-06-12」「2026年6月12日」などへ表示できます。
一方、文字列の「2026/6/12」は見た目が同じでも、日付計算に使えないことがあります。最初に文字列を日付値へ変換し、そのあとで表示形式を統一します。
- 日付順ではなく文字列順に並ぶ
- 終了日－開始日で経過日数を計算できない
- YEAR・MONTH・DAY関数で正しく分解できない
- ピボットテーブルで年・月単位にグループ化できない
- グラフで時系列軸として認識されない
DATEVALUE関数で、現在の環境が解釈できる日付文字列を変換する
C1に「DATEVALUE結果」と入力し、C2へ次の数式を入力してC10までコピーします。
| C2 | =IFERROR(DATEVALUE(A2),"変換不可") |
|---|
DATEVALUEは、現在の地域設定で日付として解釈できる文字列を日付値へ変換します。今回の実測では、スラッシュ・ハイフン・和文日付・日時（時刻付き）・年月はDATEVALUEで変換できました。一方、8桁文字列・海外形式・ピリオド区切りはそのままでは変換不可でした。なお日時（A7）は時刻部分が無視されて日付値が返り、年月（A8）は「2026/6/1」として変換されます。
| A | B | C | |
|---|---|---|---|
| 1 | 元データ | 形式 | DATEVALUE結果 |
| 2 | 2026/6/12 | 標準日付 | 2026/6/12 |
| 3 | 2026-06-12 | ハイフン区切り | 2026/6/12 |
| 4 | 2026年6月12日 | 和文日付 | 2026/6/12 |
| 5 | 20260612 | 8桁文字列 | 変換不可 |
| 6 | 12.06.2026 | 海外形式 | 変換不可 |
| 7 | 2026/6/12 14:30 | 日時 | 2026/6/12 |
| 8 | 2026/6 | 年月 | 2026/6/1 |
| 9 | 2026.6.12 | ピリオド区切り | 変換不可 |
| 10 | 未定 | 非日付 | 変換不可 |
PRACTICAL NOTE
C列はDATEVALUEだけで変換できる行を確認する診断列です。「変換不可」という文字列が混在するため、そのまま集計用列として使用するものではありません。
💡 海外形式はDATEVALUEで誤変換される可能性がある
「12.06.2026」を日本語環境のExcelがDATEVALUEで解釈すると、「12月6日」として変換される可能性があります。変換不可になるとは限らず、誤った日付値が返ることがあるため、海外形式のデータはSTEP 5で示すTEXTSPLITによる明示的な分解で処理します。
8桁文字列と海外形式は、年・月・日を明示的に分解する
A5の「20260612」は、年・月・日の位置が固定されているため、各部分を切り出してDATE関数へ渡します。
| A5用 | =LET(source,A5,converted,DATE(LEFT(source,4),MID(source,5,2),RIGHT(source,2)),IF(TEXT(converted,"yyyymmdd")=source,converted,NA())) |
|---|
結果は2026年6月12日の日付値です。
A6の「12.06.2026」は、日・月・年の順に並んでいます。ピリオドで分割し、DATE関数へ年・月・日の順に渡します。
| A6用 | =LET(parts,TEXTSPLIT(A6,"."),DATE(INDEX(parts,1,3),INDEX(parts,1,2),INDEX(parts,1,1))) |
|---|
結果は2026年6月12日です。区切りを削除して推測するのではなく、各要素の意味を明示的に指定します。
💡 「12/06/2026」は地域によって意味が変わる
日・月・年なら2026年6月12日、月・日・年なら2026年12月6日です。データ提供元の仕様を確認してから変換規則を決めます。
日時と年月は、何を日付として残すかを決める
A7の「2026/6/12 14:30」は日時です。時刻を残すならVALUEで日時シリアルへ変換し、日付だけを残すならINTで小数部分を切り捨てます。
| 日時保持 | =VALUE(A7) |
|---|---|
| 日付のみ | =INT(VALUE(A7)) |
Excelでは日付が整数部分、時刻が小数部分として保存されます。
A8の「2026/6」には日がありません。DATEVALUEでも「2026/6/1」として変換できますが、今後「2026.6」や「2026年6月」のような形式が混在した場合はDATEVALUEが解釈できないことがあります。LEFT・TEXTAFTERで年と月を明示的に取り出してDATE関数に渡す方式は、形式が変わっても規則を修正しやすくなります。
| A8用 | =DATE(LEFT(A8,4),TEXTAFTER(A8,"/"),1) |
|---|
結果は2026年6月1日です。ただし、元データに日が存在したわけではありません。補完した日であることが分かるよう、形式列や補完フラグを残します。
※TEXTAFTER関数はMicrosoft 365およびExcel 2021以降で使用できます。旧バージョンでは
=DATE(LEFT(A8,4),MID(A8,6,LEN(A8)-5),1) のように代替できます。
IMPORTANT
年月データへ1日を補う処理は、月別集計のための便宜的な変換です。「実際に6月1日に発生した」と解釈することはできません。
形式列を使って、同じサンプル表に最終結果を作る
E1に「最終結果」と入力し、E2へ次の数式を入力してE10までコピーします。
| E2 | =LET(source,A2,type,B2,IFS(type="8桁文字列",LET(converted,DATE(LEFT(source,4),MID(source,5,2),RIGHT(source,2)),IF(TEXT(converted,"yyyymmdd")=source,converted,NA())),type="海外形式",LET(parts,TEXTSPLIT(source,"."),DATE(INDEX(parts,1,3),INDEX(parts,1,2),INDEX(parts,1,1))),type="日時",INT(VALUE(source)),type="年月",DATE(LEFT(source,4),TEXTAFTER(source,"/"),1),type="ピリオド区切り",DATEVALUE(SUBSTITUTE(source,".","/")),type="非日付",NA(),TRUE,DATEVALUE(source))) |
|---|
| A | B | E | |
|---|---|---|---|
| 1 | 元データ | 形式 | 最終結果 |
| 2 | 2026/6/12 | 標準日付 | 2026/6/12 |
| 3 | 2026-06-12 | ハイフン区切り | 2026/6/12 |
| 4 | 2026年6月12日 | 和文日付 | 2026/6/12 |
| 5 | 20260612 | 8桁文字列 | 2026/6/12 |
| 6 | 12.06.2026 | 海外形式 | 2026/6/12 |
| 7 | 2026/6/12 14:30 | 日時 | 2026/6/12 |
| 8 | 2026/6 | 年月 | 2026/6/1 |
| 9 | 2026.6.12 | ピリオド区切り | 2026/6/12 |
| 10 | 未定 | 非日付 | #N/A |
日付として扱える行は、すべてExcelの日付値へ変換されています。表示はセルの表示形式を「yyyy/m/d」などへ設定して統一します。
NOTE
この数式は形式列（B列）のラベルに依存します。「ピリオド区切り」（2026.6.12、年・月・日順）と「海外形式」（12.06.2026、日・月・年順）は同じピリオド区切りでも変換規則が異なります。ラベルが混在または誤入力された場合、誤変換が起きます。データ提供元の仕様を確認し、形式ラベルを正確に入力してから変換を実行してください。
LET・MAPで、形式別の変換規則を一か所へまとめる
STEP
8の数式では、元の文字列をsource、形式をtypeとして扱っています。海外形式ではTEXTSPLITの結果をpartsへ格納しています。
| source | 元の日付文字列 |
|---|---|
| type | 日付形式・処理区分 |
| parts | 海外形式を区切り文字で分割した配列 |
F1に「MAP一括処理」と入力し、F2へ次の数式を入力します。
| F2 | =MAP(A2:A10,B2:B10,LAMBDA(source,type,IFS(type="8桁文字列",LET(converted,DATE(LEFT(source,4),MID(source,5,2),RIGHT(source,2)),IF(TEXT(converted,"yyyymmdd")=source,converted,NA())),type="海外形式",LET(parts,TEXTSPLIT(source,"."),DATE(INDEX(parts,1,3),INDEX(parts,1,2),INDEX(parts,1,1))),type="日時",INT(VALUE(source)),type="年月",DATE(LEFT(source,4),TEXTAFTER(source,"/"),1),type="ピリオド区切り",DATEVALUE(SUBSTITUTE(source,".","/")),type="非日付",NA(),TRUE,DATEVALUE(source)))) |
|---|
結果はF2:F10へスピルします。変換規則を一つの数式へ集約できるため、形式を追加した場合も修正箇所を限定できます。
※MAP・TEXTSPLIT・TEXTAFTERを利用できないExcelでは、STEP 8の数式を各行へコピーし、海外形式や年月はLEFT・MID・RIGHTなどで分解してください。
変換後に、日付シリアル値として扱えるかを検証する
G1に「日付判定」と入力し、G2へ次の数式を入力してG10までコピーします。
| G2 | =IFERROR(IF(ISNUMBER(E2),"日付値","非日付"),"非日付") |
|---|
Excelの日付は内部では数値として保存されるため、ISNUMBERで数値化できたかを確認できます。ただし、ISNUMBERがTRUEでも、年月日の解釈が正しいことまでは保証されません。NA関数による#N/Aも含め、数値でない結果は「非日付」と表示します。
| A | B | E | G | |
|---|---|---|---|---|
| 1 | 元データ | 形式 | 最終結果 | 日付判定 |
| 2 | 2026/6/12 | 標準日付 | 2026/6/12 | 日付値 |
| 3 | 2026-06-12 | ハイフン区切り | 2026/6/12 | 日付値 |
| 4 | 2026年6月12日 | 和文日付 | 2026/6/12 | 日付値 |
| 5 | 20260612 | 8桁文字列 | 2026/6/12 | 日付値 |
| 6 | 12.06.2026 | 海外形式 | 2026/6/12 | 日付値 |
| 7 | 2026/6/12 14:30 | 日時 | 2026/6/12 | 日付値 |
| 8 | 2026/6 | 年月 | 2026/6/1 | 日付値 |
| 9 | 2026.6.12 | ピリオド区切り | 2026/6/12 | 日付値 |
| 10 | 未定 | 非日付 | #N/A | 非日付 |
最終結果が日付値になったら、セルの表示形式を用途に応じて統一します。
| 一般的 | yyyy/m/d |
|---|---|
| 桁固定 | yyyy/mm/dd |
| ISO風 | yyyy-mm-dd |
| 年月集計 | yyyy年m月 |
TEXT関数でも同じ見た目を作れますが、TEXTの戻り値は文字列です。計算や集計に使う列は日付値のまま保持し、表示形式だけを設定する方が安全です。
IMPORTANT
元データ、形式、変換後の日付値を別列で残してください。特に年月へ1日を補った行や、海外形式を解釈した行は、変換規則を後から確認できる状態にしておくことが重要です。
まとめ ―― 文字列を日付値へ変換してから、表示形式を統一する
本稿ではA2:A10の同じサンプルデータを使い、DATEVALUEによる基本変換、8桁文字列と海外形式の明示的な分解、日時と年月の処理、LET・MAPによる一括化、ISNUMBERによる検証まで進めました。
| 基本 | =DATEVALUE(A2) |
|---|---|
| 8桁 | =LET(source,A2,converted,DATE(LEFT(source,4),MID(source,5,2),RIGHT(source,2)),IF(TEXT(converted,"yyyymmdd")=source,converted,NA())) |
| 海外形式 | =LET(parts,TEXTSPLIT(A2,"."),DATE(INDEX(parts,1,3),INDEX(parts,1,2),INDEX(parts,1,1))) |
| 日時→日付 | =INT(VALUE(A2)) |
| 年月→月初 | =DATE(LEFT(A2,4),TEXTAFTER(A2,"/"),1) |
| 日付判定 | =IFERROR(IF(ISNUMBER(E2),"日付値","非日付"),"非日付") |
| MAP一括 | =MAP(A2:A10,B2:B10,LAMBDA(source,type,...)) |
- 標準的な日付文字列：DATEVALUEで日付値へ変換する。
- 8桁文字列：年・月・日を固定位置から切り出し、DATEで組み立てる。
- 海外形式：年月日の順序を確認し、TEXTSPLITなどで明示的に分解する。
- 日時：時刻を残すか、INTで日付だけにするかを決める。
- 年月：月初日を補う場合は、補完した事実を残す。
- 非日付：「未定」などを無理に日付へ変換せず、非日付として残す。
- 表示：日付値へ変換したあと、セルの表示形式で見た目を統一する。
- 検証：ISNUMBERで数値として変換されたかを確認し、年月日の妥当性は変換規則と元データの比較で別途確認する。
日付形式を統一する目的は、文字列の見た目を同じにすることではありません。年月日の意味を確認し、日付計算に使える値へ変換したうえで、表示だけを統一することが重要です。