基幹システムの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 が FALSE | 8桁の文字列 | 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」を選ぶ
区切り位置は、元のセルを上書きします。念のため、作業前に元の列をコピーしておくと安心です。
- 8桁の数字が入った列を1列だけ選択します。
- [データ]タブの[区切り位置]をクリックします。
- 1/3 の画面で[カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ]を選びます。
- [次へ]を押します。
- 2/3 の画面では、区切り文字のチェックをすべて外して[次へ]を押します。
- 3/3 の画面の[列のデータ形式]で[日付]を選び、右の一覧から[YMD]を選択します。
- [完了]を押します。
肝心なのは、最後の画面で「日付: 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(変換後) |
|---|---|---|
| 2 | 20261101 | 2026/11/1 |
| 3 | 20261115 | 2026/11/15 |
| 4 | 20261203 | 2026/12/3 |
| 5 | 20270105 | 2027/1/5 |
B列が 46327 のような数字で表示されたら、表示形式を[短い日付形式]にしてください。DATE の引数の詳細はDATE関数の使い方、文字の切り出し方はMID関数の使い方で解説しています。
変換できたら、元の列に値として戻しておきましょう。
- A列の表示形式を、先に[短い日付形式]にしておきます。
- B列の変換結果をコピーします。
- A2 を右クリックし、[貼り付けのオプション]の[値]を選びます。
- 作業用の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)で「.」を「/」にする
- ドット区切りの日付が入った列だけを選択します。
- Ctrl+H を押して[検索と置換]を開きます。
- [検索する文字列]に「.」を入力します。
- [置換後の文字列]に「/」を入力します。
- [すべて置換]を押します。
列を選ばずに実行すると、シート全体が置換の対象になります。小数の 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式の結果 | 起きていること |
|---|---|---|
| 20261301 | 2027/1/1 | 13月が翌年1月に繰り上がる |
| 20261132 | 2026/12/2 | 11月32日が12月2日に繰り上がる |
| 20260230 | 2026/3/2 | 2月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で日付が数字(シリアル値)になる原因と直し方もあわせて確認してみてください。
