スプレッドシートで文字列の日付を正しい日付に直す方法

他部署からもらったCSVをスプレッドシートに貼り付けたら、日付がうまく並ばず集計が止まってしまった、という状況は珍しくありません。
見た目は日付なのに左寄せになっていて、並べ替えや関数が思ったとおりに動かないとき、多くは「日付が文字列になっている」ことが原因です。
この記事では、文字列になってしまった日付を効率よく直す手順と、再発を防ぐための考え方を整理して解説します。

スプレッドシートで日付が文字列になっているか確認する

インポートやコピーをした直後に、まず確認したいのは「その日付は本当に日付データなのか」という点です。
見た目だけでは判断しにくいこともあるため、いくつかの観点からチェックすると、後のトラブルを減らせます。
ここでは、文字列になった日付を見抜く基本の確認ポイントを整理します。

日付が文字列かどうかを見分けるポイント

一般的には、セルの配置と表示形式を確認すると、日付か文字列かを判断しやすくなります。
実務の現場でも、レイアウトを整える前にこのチェックをしておくと、後からの手戻りが減ることが多いです。

代表的な確認ポイントは次の三つです。

• 日付が左寄せになっていないか
• 数式バーに「’2024/01/15」のように先頭にシングルクォートが付いていないか
• メニューの表示形式が「自動」ではなく「テキスト」になっていないか

いずれも「文字列として扱われているサイン」です。
特に、外部システムから取り込んだCSVやTSVでは、すべての列が文字列扱いになっているケースがよくあります。

判断基準としては、日付のセルを選択したときに、表示形式が「日付」になっているかどうかをまず見るとよいでしょう。
「自動」や「プレーンテキスト」になっている場合は、日付として扱われていない可能性が高くなります。

代表的な文字列日付のパターン

文字列になっている日付には、いくつかの典型的なパターンがあります。
あらかじめパターンを知っておくと、どの変換方法を使えばよいか判断しやすくなります。

よくあるパターンの例です。

• 「2024-01-15」「2024/1/5」のように区切り記号はあるが文字列扱い
• 「20240115」のように8桁の数字だけで構成されている
• 「2024年1月15日」「1月15日(月)」のように日本語が混ざっている
• 「15/01/2024」「01-15-2024」のように日付と月の順番が地域によって異なる

実務では、複数のパターンが同じ列に混在していることも多く、ここが変換を難しく感じる理由になりがちです。
どのパターンが何件ぐらいあるかをざっと眺めてから、関数を使うか手作業も交えるかを決めるのが現実的です。

日付形式と表示形式の違いを理解する

スプレッドシートでは、内部的な「値」と画面上の「表示形式」が分かれていることが重要な前提です。
内部の値はシリアル値と呼ばれる連番で、表示するときに「yyyy/mm/dd」や「2024年1月15日」などの書式が適用されます。（出典：Google ドキュメント エディタ ヘルプ）(Google ヘルプ)

つまり、見た目が「2024/01/15」と同じでも、

• 中身が「日付シリアル値」の場合
• 中身が「’2024/01/15」という文字列の場合

では、並べ替えや関数の動きがまったく変わります。

判断基準としては、日付を含む列を昇順に並べ替えてみて、期待どおりに並ぶかどうかを見る方法も有効です。
順番がおかしい場合、多くは文字列と日付が混在しているサインだと考えられます。

文字列の日付を本来の日付データに直す方法

日付が文字列だと分かったら、次は「どう直すか」です。
最短で済ませる方法もあれば、多少手間をかけてでも確実に直す方法もあります。
ここでは、状況に応じて選べる代表的な変換方法を整理します。

結論（最短のやり方）

最短で済ませたい場合の基本的な方針は次のとおりです。

1行のまとめとしては、「そのまま日付と認識される書式なら表示形式を変える、そうでなければ関数で日付を組み立てる」という考え方になります。

• 「2024/01/15」「2024-01-15」のような書式
  • 試しに表示形式を「日付」に変更し、それでうまくいけば完了
• 「20240115」「2024年1月15日」のような書式
  • DATE関数やDATEVALUE関数、LEFT／MID／RIGHT関数を組み合わせて変換する
• パターンが混在している場合
  • 件数が少なければ手修正、多い場合は列ごとに補助列を作って関数で一括変換

実務の現場でも、まずは表示形式の変更を試し、それで一部しか直らない場合に関数を使う手順を採ることが多いです。

準備と前提条件の確認ポイント

変換作業に入る前に、最低限次の点を確認しておくと安全です。

• どの列を変換対象にするかを決め、元データをコピーしてバックアップを取る
• スプレッドシートの「ファイル＞設定」で地域設定がどこになっているかを確認する
• 日付の書式パターンが何種類あるかをざっくり数えておく
• 変換後に使いたい関数（DATEDIF、NETWORKDAYSなど）があれば、どの列で使うかを決めておく

特に地域設定は、日付の解釈ルールに影響する判断基準になります。
日本のフォーマットだと思っていても、シートの地域が別の国になっていると、「01/02/2024」が1月2日なのか2月1日なのか変わってしまう可能性があります。（出典：Google ドキュメント エディタ ヘルプ）(Google ヘルプ)

文字列の日付を日付データに直す手順

ここでは、代表的なパターンごとに、具体的な変換手順を紹介します。
いずれも、まずは隣の列など別の列に変換結果を作り、問題なければ値貼り付けで上書きする流れにすると安心です。

パターン1：「2024/01/15」「2024-01-15」など区切りあり

1. 対象の列を選択する
2. メニューの表示形式から「数字＞日付」を選ぶ
3. 必要に応じて「表示形式の詳細設定＞その他の日付や時刻の形式」で好きな書式を選ぶ

このパターンは、もともとスプレッドシートが日付として解釈しやすい書式なので、表示形式を変えるだけで直ることが多いです。（出典：Google ドキュメント エディタ ヘルプ）(All About（オールアバウト）)

パターン2：「20240115」のような8桁の数字

1. 変換結果を入れる列を用意し、先頭のセルに次のような式を入力する
   • =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
2. オートフィルで下までコピーする
3. 変換結果の列を値でコピーして、元の列に貼り付ける

DATE関数は「年」「月」「日」から日付を作る関数で、LEFT／MID／RIGHT関数で文字列から年・月・日を切り出しています。（出典：Google ドキュメント エディタ ヘルプ）(Google ヘルプ)

パターン3：「2024年1月15日」「1月15日(月)」など日本語混在

1. 「年」「月」「日」などの文字をSUBSTITUTE関数で取り除く
2. その結果をパターン2と同様にDATE関数で日付に変換する
3. あるいは、DATEVALUE関数で解釈できる書式に一旦置き換え、DATEVALUE関数を使う

DATEVALUE関数は、既知の形式で記述された日付の文字列を日付シリアル値に変換する関数です。（出典：Google ドキュメント エディタ ヘルプ）(Google ヘルプ)
ただし、解釈できる書式はシートの地域設定に依存するため、期待どおりに動かない場合はDATE関数で組み立てるほうが安全なこともあります。

つまずきやすいケースと原因別の対処法

現場でよく見かける「うまくいかないケース」と、その原因・対処法を整理します。
症状ごとに分けて考えることで、問題切り分けがしやすくなります。

症状1：DATEVALUE関数で#VALUE!エラーになる

• 原因
  • 文字列の日付がシートの地域設定と異なる順番（日付と月が逆など）になっている
• 対処
  • 地域設定をデータの元システムに合わせる
  • またはLEFT／MID／RIGHT関数で年・月・日を切り出し、DATE関数で組み立てる

症状2：一部の行だけは日付に変換されない

• 原因
  • 同じ列の中に「2024/1/5」と「2024-01-05」「20240105」が混在している
  • 途中に空白や見えない文字が含まれている
• 対処
  • TRIM関数やCLEAN関数で余分な空白や制御文字を除去する
  • 書式ごとに列を分けるか、IF関数で条件分岐しながら変換する

症状3：並べ替えやフィルタがうまく効かない

• 原因
  • 日付データと文字列が混在している
• 対処
  • 変換前に「文字列と疑われる行だけにフィルタをかけて抽出」するなど、対象を絞って一括変換する
  • 変換後、列全体を選択して日付形式が統一されているか確認する

判断基準としては、「エラーが出ているのか、結果は出るが並べ替えが変なのか」を分けて考えると原因にたどり着きやすくなります。

失敗を防ぎ再発を避けるためのポイント

一度直しても、別のデータを取り込むたびに同じ問題が起きると負担が大きくなります。
ここでは、今後の再発を減らすための考え方を整理します。

• 元データの提供元に、日付の書式を統一してもらえないか相談する
• 取り込み用のシートを決めておき、関数で本番シートに変換済みの値を渡す設計にする
• 日付を扱う列には、あらかじめ「日付」形式を設定しておく
• 新しい行を追加する際は、既存の行をコピーしてから値だけ上書きする運用にする

実務では、「取り込み用シート＋本番シート」の二段構成にしておくことで、後から変換ロジックを変えやすくなるケースが多いです。
どこまでルールを厳しくするかは、シートを触る人数や更新頻度を判断基準に決めるとよいでしょう。

日付を文字列に戻さないための設定と運用のコツ

一度きれいに直した日付も、ちょっとした操作で再び文字列に戻ってしまうことがあります。
日付が文字列化しないよう、入力方法やシート構造の設計段階で工夫しておくと安心です。
ここでは、日常的な運用の中でできる対策を紹介します。

日付入力時に気をつけたいこと

日付を手入力する場面では、次のようなポイントを押さえておくと文字列化を防ぎやすくなります。

• 日付用の列は、事前に「日付」形式を設定しておく
• 「2024/1/5」のように、スプレッドシートが理解しやすい書式で入力する
• 可能であれば、データ入力規則で日付以外を弾く設定を検討する

特に、他の人にも入力してもらうシートでは、入力ルールと例をヘッダー行に明記しておくと誤入力を減らせます。
「01-05-24」のような略記は、地域設定によっては意図しない日付になることもあるため、なるべく避けるのが無難です。

外部データ取り込み時のチェックリスト

外部システムや別ファイルからデータを取り込むときには、事前に簡単なチェックリストを使うとトラブルを減らせます。

• 取り込み元のシステムで、日付列の書式を確認しておく
• 取り込み専用シートに一度貼り付け、その上で関数変換する
• 新しい取り込みパターンが発生したら、その都度ヘルプ列やコメントにメモしておく

経験上、一度ルールを決めてしまえば、あとは毎回同じ手順で変換できるようになり、日付の整備にかかる時間が大きく短縮されることが多いです。

よくある質問

Q1. 一度日付に直したのに、コピーしたら文字列になってしまいました。どうすればよいですか
コピー先の列の表示形式が「プレーンテキスト」になっている可能性があります。
コピー先の列を「日付」形式にしてから貼り付けると、日付として保持されやすくなります。

Q2. 特殊な書式のまま日付として扱いたい場合はどうすればよいですか
内部的には日付シリアル値のまま保持し、表示形式だけカスタム日付で変更します。
TEXT関数で文字列にしてしまうと、以後は日付として計算できなくなる点に注意が必要です。

Q3. ある列の一部だけがどうしても日付に変換できません
そのセルだけ半角と全角の混在や見えない文字が含まれていることがあります。
TRIMやCLEAN関数、SUBSTITUTE関数で余計な文字を取り除いたうえで、改めてDATE関数などを試してみてください。

Q4. Excelとスプレッドシートで日付の挙動が違います
日付の起点や地域設定、認識できる書式がツールごとに異なるためです。
どちらか一方で安定して動く書式に統一し、変換用の列を設けておく運用が現実的です。

スプレッドシートの日付を文字列から直す方法のまとめ

・セルの配置と表示形式を確認して日付か文字列かを見分ける
・まずは表示形式を日付に変えるだけで直るかを試して判断する
・8桁数字のような書式はDATE関数と文字列関数で日付を組み立てる
・日本語混じりの日付は余計な文字を除去してからDATEやDATEVALUEで変換する
・DATEVALUEは地域設定に依存するためうまく動かないときはDATE関数を優先する
・変換は隣の列で行い問題なければ値貼り付けで上書きする
・TRIMやCLEANで見えない空白や制御文字を事前に除去しておく
・エラーが出る場合と並べ替えがおかしい場合で原因の切り分け方を変える
・地域設定と日付書式の組み合わせを事前に確認しておく
・日付列はあらかじめ日付形式に設定し入力ルールも明示する
・取り込み専用シートを作り本番シートには変換済みの値だけ渡す構成にする
・元データの提供元と日付書式を事前に取り決めておく
・新しい取り込みパターンが発生したら変換手順をシート内に記録しておく
・TEXT関数で文字列化した日付は計算に使えないことを共有しておく
・運用ルールの厳しさはシートを扱う人数と更新頻度を基準に決める