Excelで文字列の日付が日付にならない原因|8桁の数字・ドット区切りを一括変換

スポンサーリンク

基幹システムのCSVや他部署の台帳から日付を貼り付けたのに、Excelで並べ替えも期間の計算もできない。次のような症状に心当たりはありませんか。

  • 日付が 20261101 のような8桁の数字で入ってきた
  • 2026.11.01 のドット区切りが、表示形式を日付にしても変わらない
  • 2026/11/01 と表示されているのに、なぜか左寄せのまま

見た目が日付でも、Excelが中身を「文字列」や「ただの数値」と見ていれば、日付の計算には使えません。放っておくと、月別の集計や締め日の計算を手作業でやり直す羽目になります。

この記事では、まず文字列の日付かどうかを判定します。そのうえで、Excelの文字列の日付を形式別に日付へ変換する方法をまとめました。列を一括で変換する式と、変換後の検算式も載せています。

対応バージョン: Microsoft 365 / Excel 2024・2021・2019・2016(Windows / Mac)を対象にしています。区切り位置ウィザード(「日付: YMD」の指定)はデスクトップ版の機能です。Excel for the web にはこのウィザードがないため、Web 版では DATE 関数などの数式で変換してください。範囲を1つの式でまとめて変換するスピルは、Microsoft 365 / Excel 2021 以降が対象。Excel 2019 以前では、先頭セルの式を下へフィルします。

Excelで文字列の日付が日付にならない原因を判定する

Excelの日付の正体は「シリアル値」と呼ばれる通し番号です。1900/1/1 を1として数え、2026/11/1 は 46327 という数値になります。日付の表示形式は、この数値をカレンダーの形で見せているだけなんです。

文字列の「2026/11/01」や、数値の 20261101 はシリアル値ではありません。だから並べ替えや集計で、日付として正しく扱われないというわけです。

症状チェック表(左寄せ・ISNUMBERがFALSE・表示形式を変えても変わらない)

確認には、空いている列に次の式を入れます。A2 は確認したいセルに読み替えてください。

=ISNUMBER(A2)

日付(シリアル値)なら TRUE、文字列なら FALSE が返ります。ISNUMBER の詳しい使い方は、ISNUMBER関数の使い方で解説しています。

症状確認方法Excelが見ている中身進む節
2026/11/01 が左寄せのままISNUMBER が FALSE文字列の日付2026/11/01 の節
20261101 が右寄せ表示形式を日付にすると「####」8桁のただの数値20261101 の節
20261101 が左寄せISNUMBER が FALSE8桁の文字列20261101 の節
2026.11.01 と表示されるISNUMBER が FALSE文字列2026.11.01 の節
一部のセルだけ変換できないLEN の文字数が多いスペース・全角・改行の混入2026/11/01 の節

左寄せでの判定は、配置を「標準」のままにしている列での目安です。中央揃えなどを設定済みの列では、ISNUMBER で確かめましょう。

右寄せの 20261101 を日付の表示形式にすると「####」が並ぶのは、シリアル値として大きすぎるため。Excelが扱える日付は 9999/12/31(シリアル値 2958465)までです。8桁の数値はこの上限を大きく超えるので、日付として表示できません。

文字列の日付は、並べ替えでも正体が出ます。文字の順に並ぶので、「2026/12/1」が「2026/2/1」より前に来ることがあるんです。

5桁の数字や日付ではない数値なら別の対処

症状が逆で、日付を貼り付けたら 46327 のような5桁の数字になった場合は原因が違います。こちらはExcelで日付が数字(シリアル値)になる原因と直し方を見てください。

金額や個数など、日付ではない数値が左寄せになっているケースもあります。その場合はExcelの文字列数値を一括変換する方法が近道です。

20261101(8桁の数字)を日付に変換する

基幹システムのCSVでは、日付が 20261101 のような8桁の数字で出力されることがあります。セルに数値として入っていても、文字列として入っていても、変換の考え方は同じです。

方法は2つ。

  • 区切り位置: 数式を使わずに、元の列をその場で変換する(デスクトップ版のみ)
  • DATE関数の式: Web 版でも使え、変換結果を検算しやすい

区切り位置で「日付: YMD」を選ぶ

区切り位置は、元のセルを上書きします。念のため、作業前に元の列をコピーしておくと安心です。

  1. 8桁の数字が入った列を1列だけ選択します。
  2. [データ]タブの[区切り位置]をクリックします。
  3. 1/3 の画面で[カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ]を選びます。
  4. [次へ]を押します。
  5. 2/3 の画面では、区切り文字のチェックをすべて外して[次へ]を押します。
  6. 3/3 の画面の[列のデータ形式]で[日付]を選び、右の一覧から[YMD]を選択します。
  7. [完了]を押します。

肝心なのは、最後の画面で「日付: YMD」を選ぶこと。[G/標準]のまま完了すると、20261101 は8桁の数値になるだけで、日付にはなりません。

日付として認識されたセルは右寄せになり、2026/11/1 のように表示されます。左寄せや数字のまま残ったセルがあれば、次の DATE 関数の方法で変換してください。

DATE+LEFT/MID/RIGHTで変換する

B2 に次の式を入れて、下へフィルします。

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

LEFT で先頭4文字を年、MID で5文字目から2文字を月、RIGHT で末尾2文字を日として取り出します。それを DATE 関数で1つの日付に組み立てる仕組みです。A2 が数値でも文字列でも、同じ式で動きます。

行A(元データ)B(変換後)
2202611012026/11/1
3202611152026/11/15
4202612032026/12/3
5202701052027/1/5

B列が 46327 のような数字で表示されたら、表示形式を[短い日付形式]にしてください。DATE の引数の詳細はDATE関数の使い方、文字の切り出し方はMID関数の使い方で解説しています。

変換できたら、元の列に値として戻しておきましょう。

  1. A列の表示形式を、先に[短い日付形式]にしておきます。
  2. B列の変換結果をコピーします。
  3. A2 を右クリックし、[貼り付けのオプション]の[値]を選びます。
  4. 作業用のB列を削除します。

スピルで列ごと一括変換(Microsoft 365・Excel 2021)

Microsoft 365 と Excel 2021 以降なら、範囲を指定した式を B2 に1つ入れるだけで済みます。結果が B5 まで自動で広がる仕組みで、これが「スピル」です。

=DATE(LEFT(A2:A5,4),MID(A2:A5,5,2),RIGHT(A2:A5,2))

空白の行が混ざると、その行は #VALUE! になります。範囲を広めに取りたいときは、空白を飛ばす IF を足しましょう。

=IF(A2:A100="","",DATE(LEFT(A2:A100,4),MID(A2:A100,5,2),RIGHT(A2:A100,2)))

結果が広がる先のセルに値が残っていると、#SPILL! エラーになります。B列を空けてから入力してください。

Excel 2019 以前はスピルに対応していません。1セル版の式を B2 に入れ、フィルハンドル(セル右下の小さな四角)をダブルクリックして下へコピーします。

表示形式を0000/00/00にするだけでは日付にならない

ユーザー定義の表示形式で 0000"/"00"/"00 を設定すると、20261101 は「2026/11/01」と表示されます。ただし、中身は 20261101 のまま。見た目が変わっただけで、日付にはなっていません。

では、20261130 に1を足すとどうなるでしょうか。結果は 20261131 になり、「2026/11/31」という存在しない日付が表示されます。ISNUMBER も TRUE を返すので、判定の式ではこのケースを見抜けません。

確かめるには、セルを選んで数式バーを見ます。20261101 と表示されていれば、見た目だけの日付です。TEXT 関数で “2026/11/01” の形に整えた結果も文字列なので、日付の計算には使えません。表示形式との違いはTEXT関数の使い方で確認できます。

2026.11.01(ドット区切り)が日付にならない

2026.11.01 のようなドット区切りは、日本語の環境では日付として読み取られず、文字列のまま残ることがある形式です。デスクトップ版なら置換か区切り位置、Web 版も含めて式で直すなら SUBSTITUTE と DATEVALUE を使います。

置換(Ctrl+H)で「.」を「/」にする

  1. ドット区切りの日付が入った列だけを選択します。
  2. Ctrl+H を押して[検索と置換]を開きます。
  3. [検索する文字列]に「.」を入力します。
  4. [置換後の文字列]に「/」を入力します。
  5. [すべて置換]を押します。

列を選ばずに実行すると、シート全体が置換の対象になります。小数の 1.5 まで「1/5」に書き換わり、データが壊れるので注意してください。

置換後に右寄せへ変わったセルは、日付として認識されています。左寄せのまま残る場合は、セルの表示形式が[文字列]になっていないか確認しましょう。直らないときは、後述の SUBSTITUTE+DATEVALUE の式に切り替えるのが確実です。

区切り位置で変換する

ドット区切りでも、20261101 の節と同じ区切り位置の手順を試せます。違いは2/3 の画面の扱いだけです。

2/3 の画面では、[その他]に「.」を入れないでください。ドットを区切り文字にすると、年・月・日が3列に分かれてしまいます。区切り文字のチェックはすべて外し、3/3 の画面で[日付]の[YMD]を選びます。

完了後に右寄せになったセルは変換済み。左寄せのまま残ったセルは、次の式で変換してください。

SUBSTITUTE+DATEVALUEで式にする

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

SUBSTITUTE で「2026.11.01」を「2026/11/01」に置き換え、DATEVALUE がそれをシリアル値に変換します。結果は数値で返るので、表示形式を[短い日付形式]にしてください。

月や日が1桁の「2026.1.5」も、「2026/1/5」になるため同じ式で扱えます。Microsoft 365 / Excel 2021 以降なら、範囲を指定してスピルで一括変換することも可能です。

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

置換の書き方はSUBSTITUTE関数の使い方、DATEVALUE が受け付ける文字列の形はDATEVALUE関数の使い方で詳しく解説しています。

地域設定・言語でDATEVALUEの結果が変わる

DATEVALUE は、パソコンの「地域の形式」に合わせて文字列を読み取ります。文字列の形が地域の形式と合わないと、#VALUE! になる仕様です。

そのため、同じファイルでも別のパソコンでは結果が変わるかもしれません。ドット区切りの文字列をそのまま DATEVALUE に渡したときの結果も、環境しだい。SUBSTITUTE で「/」に直してから渡すのが無難です。

海外拠点と共有するファイルなど、環境の違いが心配な場合は位置で切り出す式が向いています。月と日が2桁にそろった「2026.11.01」の形なら、次の式は地域の形式に左右されません。

=DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))

2026/11/01(スラッシュ区切り)なのに変換されない

スラッシュ区切りは本来、Excelが日付として読み取れる形です。それでも左寄せのままなら、入力した時点でセルの表示形式が[文字列]だった可能性があります。

1セルだけなら、表示形式を[標準]に変えてから、F2 キーを押して Enter で確定し直してください。数が多いときは、次の式で一括変換します。

=DATEVALUE(A2)

日付として読める文字列なら、VALUE 関数でも同じようにシリアル値に変換可能です。使い分けはVALUE関数の使い方にまとめています。

前後スペース・全角数字・改行が混ざっている

一部のセルだけ変換できないなら、目に見えない文字を疑いましょう。まずは文字数を数えます。

=LEN(A2)

「2026/11/01」の形なら10文字です。11以上なら、スペースや改行などの余計な文字が混ざっています。全角の「2026/11/01」は文字数が同じなので、数字の幅で見分けてください。

これらをまとめて取り除いてから変換するのが、次の式です。

=DATEVALUE(TRIM(CLEAN(ASC(A2))))
  • ASC: 全角の数字・スラッシュ・スペースを半角にする
  • CLEAN: 改行など、印刷できない文字を取り除く
  • TRIM: 前後の余分なスペースを取り除く

Webページからコピーした日付には、TRIM では消えない特殊なスペース(CHAR(160))が入っていることがあります。その場合は、先に SUBSTITUTE で消しておきましょう。

=DATEVALUE(TRIM(CLEAN(ASC(SUBSTITUTE(A2,CHAR(160),"")))))

存在しない日付(20261301)はエラーにならない

DATE 関数は、12を超える月を翌年に繰り上げます。日も同じで、その月の日数を超えた分は翌月に送られます。そのため、元データの入力ミスがエラーにならず、別の日付として通ってしまうんです。

元データDATE式の結果起きていること
202613012027/1/113月が翌年1月に繰り上がる
202611322026/12/211月32日が12月2日に繰り上がる
202602302026/3/22月30日が3月2日に繰り上がる

どの結果も正しいシリアル値なので、ISNUMBER は TRUE を返します。見抜くには、変換結果を元の8桁の形に戻して突き合わせましょう。

=TEXT(B2,"yyyymmdd")=A2&""

TRUE なら一致、FALSE なら元データがおかしい行です。20261301 の行では、変換結果が「20270101」に戻るため FALSE になります。全行の FALSE の数は、次の式でまとめて数えられます。

=SUMPRODUCT(--(TEXT(B2:B5,"yyyymmdd")<>A2:A5&""))

範囲はデータのある行に合わせてください。結果が 0 なら、全件が一致している証拠です。

DATEVALUE で変換した列は、変換できなかったセルが #VALUE! になります。元の件数と、数値になった件数の差を見れば取りこぼしがわかります。

=COUNTA(A2:A100)-COUNT(B2:B100)

2桁年(26/11/01)の扱い

年が2桁の文字列の日付には、Excelのエラーチェックが便利です。セルの左上に緑の三角が出ていたら、セルを選んで横に出る[!]のボタンを押してください。メニューから、2桁の年を 20XX または 19XX に変換する項目を選べます。

緑の三角が出ないときは、[ファイル]→[オプション]→[数式]を開きます。エラーチェックのルールで、2桁の年を含む文字列の日付がオンになっているか確認しましょう。

「26/11/01」のように年・月・日が2桁ずつそろった形なら、式で4桁の年に直すこともできます。

=DATE(2000+LEFT(A2,2),MID(A2,4,2),RIGHT(A2,2))

よくある質問

変換した日付が4年と1日ずれる

Excelには、1900年から数える日付システムと、1904年から数える日付システムがあります。両者の差は1,462日、つまり4年と1日。設定の違うブックから日付をコピーすると、このずれが起きます。

設定の場所は、[ファイル]→[オプション]→[詳細設定]にある[1904 年から計算する]です。直すときは、空きセルに 1462 と入力してコピーします。対象の日付を選び、[形式を選択して貼り付け]の[加算]か[減算]を選びましょう。

変換したのにピボットでグループ化できない

ピボットテーブルの元データに、変換し損ねた文字列のセルが1つでも残ると、日付のグループ化ができないことがあります。COUNTA と COUNT の差を見る検算式で、取りこぼしが0件か確かめてください。

元データを直したら、ピボットテーブルの更新も必要です。原因の切り分けはピボットテーブルで日付がグループ化できない原因と対処で詳しく解説しています。

0から始まる8桁(01112026)が7桁になった

月日年の順の 01112026(2026年1月11日)は、数値として読み込まれると先頭の0が消えて 1112026 になります。次の式で8桁に戻してから、月・日・年を切り出しましょう。

=DATE(RIGHT(TEXT(A2,"00000000"),4),LEFT(TEXT(A2,"00000000"),2),MID(TEXT(A2,"00000000"),3,2))

「2026115」のように桁数がそろっていない数字は別の問題。2026/1/15 なのか 2026/11/5 なのか、Excelには区別できません。出力元のシステムで、8桁の形に出し直してもらうのが確実です。

自動データ変換の設定で防げないの?

Microsoft 365 と Excel 2024 には「自動データ変換」の設定があります。ただし日付に関わる項目は、JAN1 のような英字と数字の並びを日付にするかどうかだけ。8桁の数字やドット区切りを日付にする設定はないので、ここまでの方法で変換してください。

まとめ:形式別の変換方法 早見表

元データの形式デスクトップ版の方法数式(Web 版でも使える)
20261101(数値・文字列)区切り位置で「日付: YMD」=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
2026.11.01置換で「.」→「/」、または区切り位置=DATEVALUE(SUBSTITUTE(A2,".","/"))
2026/11/01(左寄せ)表示形式を[標準]にして再入力=DATEVALUE(A2)
スペース・全角・改行入り―=DATEVALUE(TRIM(CLEAN(ASC(A2))))
26/11/01(2桁年)エラーチェックで 20XX に変換=DATE(2000+LEFT(A2,2),MID(A2,4,2),RIGHT(A2,2))

Excelで文字列の日付が日付にならないときは、まず ISNUMBER で中身が数値かどうかを確かめます。そのうえで、自分のデータが早見表のどの形式かを見て、方法を選んでください。

変換が終わったら、検算の式で取りこぼしと月ズレがないかを確認しておきましょう。ここまで済ませれば、並べ替えも期間の計算も、ピボットでの月別集計も日付として動くはずです。

逆に、日付を入れたら 46327 のような数字になってしまったときは、Excelで日付が数字(シリアル値)になる原因と直し方もあわせて確認してみてください。

タイトルとURLをコピーしました