Excelの日付が文字列や数字になる。12通りで貼って直し方を数えた

日付の列が集計に乗らないときは、まずその列が数値か文字列かを見る。文字列なら日付として扱われていない。数値でも、2千万台の数になっていたら日付ではない。

同じ日を12通りの書き方で貼ってみた。2026年9月1日になったのは10通りで、1通りは数値だが日付ではなく、1通りは文字列のまま残った。

以下の数は、Microsoft 365のExcel（バージョン16）に実際に貼って、セルの中身を読んで数えたものだ。確認日は2026年9月22日。Windows 11で、貼り付けの設定は既定のままにしてある。

貼ると11通りが数値になるが、日付なのは10通り

12通りを1列に貼って、1つずつ中身を読んだ。数値になったのは11通り、文字列のまま残ったのは1通りだった。

文字列で残ったのはピリオドで区切った2026.9.1だ。スラッシュとハイフンは日付になるのに、ピリオドは日付として読まれない。

数値になった11通りのうち、1通りは日付ではない。8桁の20260901は、2千万台のただの数として入る。セルには20260901と出たままで、日付の表示にもならない。

これが見つけにくい理由は、型の検査を通ってしまうからだ。ISNUMBERで数値かどうかを見ると、8桁の数字はTRUEを返す。列の型をそろえたつもりでも、その行だけ57000年あたりの日付として扱われる。

残りの書き方は全部日付になった。全角の２０２６/９/１も、うしろに空白が付いた2026/9/1 も、令和8年9月1日も、R8.9.1も、1-Sep-26も日付になる。年を書かずに9/1とすると、入力した年の9月1日になる。この年は入力したときに決まるので、別の年のデータを貼ると違う日になる。

文字列が30行混ざると、9件と561,000円が落ちる

100行の表を作り、そのうち30行だけ日付を文字列にした。残り70行は日付として入っている。

9月の分をCOUNTIFSで数えると21件になった。本当は30件ある。SUMIFSで金額を足すと1,279,000円で、本当は1,840,000円だ。9件と561,000円が落ちている。

落ちた行はエラーにならない。エラーにならないから、合計が出た時点で終わりにしてしまう。文字列の日付は、比較の条件に当てはまらないので数えられないだけだ。

見分けるには、日付の列の隣でISNUMBERを数える。

=SUMPRODUCT((ISNUMBER($A$2:$A$101)=FALSE)*1)

この数が0でなければ、その列での集計は落ちている。行数と数えた件数を並べて見るのでもいい。取り込んだ直後に1回数えておくと、あとで合わない理由を探さずに済む。

VLOOKUPが合わないときと同じ形の失敗で、数値と文字列の取り違えは照合でも集計でも同じように効く。

区切り位置で直らない2通りは、壊れている2通りと同じ

文字列の日付を直す方法として、データタブの区切り位置がよく挙がる。12通りに対して試した。

区切り位置で2026年9月1日になったのは10通りだった。直らなかったのは2026.9.1と20260901の2つだ。

つまり、貼った時点で壊れている2通りと、区切り位置で直らない2通りがぴったり同じになる。正しく日付になった10通りは区切り位置でも直るが、そもそも直す必要がない。壊れている2通りだけが残る。

ピリオド区切りは会計ソフトの出力で出てくる。8桁の数字は基幹システムのCSVでよく見る。どちらも区切り位置では直らないので、式で直すことになる。

1つの式で12通りとも直す

配った表では、まず全角と前後の空白を落とした値を作る。

=IF($A3="","",TRIM(ASC($A3)))

ASCが全角を半角にする。TRIMが前後の空白を落とす。この2つを先に通しておくと、あとの場合分けが減る。

そのうえで、1つの式に4つの場合を並べる。

=IF($A3="","",IFERROR(
  IF(IFERROR($A3+0,0)>19000101,
     DATE(LEFT($K3,4),MID($K3,5,2),RIGHT($K3,2)),
  IF(ISNUMBER($A3),INT($A3),
  IFERROR(INT(DATEVALUE($K3)),
          INT(DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
            $K3,"年","/"),"月","/"),"日",""),".","/")))))),
  "直せない"))

上から順に、8桁の数字、すでに日付になっているもの、そのまま読める文字列、区切りを直せば読める文字列を見ている。

8桁かどうかは、文字数ではなく数の大きさで見分ける。2026/9/1は8文字なので文字数では8桁の数字と区別できない。数に直すと46266で、19000101よりはるかに小さい。19000101を境にすれば、日付の大きさの数とyyyymmddの数が分かれる。

最後のSUBSTITUTEが、年月日とピリオドをスラッシュに替える。ここで2026.9.1と2026年9月1日とR8.9.1が通る。どれにも当たらなければ直せないと出る。

12通りを入れると12通りとも2026年9月1日になった。存在しない日付の2026/13/1を入れると、直せないと出る。

時刻が付いていると、20行中1行しか合わない

日付に時刻が付いた列で、等号を使って日を指定すると外れる。

同じ日の0時から19時まで1時間おきに20行作り、2026年9月1日と等しいかを数えた。合ったのは1行だけだった。0時の行しか合わない。

Excelの日付は1900年1月1日を1とする数で、1日が1になる。時刻は小数で持つので、10時30分は0.4375が足された数になる。46266と46266.4375は別の数だ。だから等号では合わない。

INTで小数を落としてから比べると、20行とも合った。

=IF(INT($A2)=DATE(2026,9,1),1,0)

期間で数えるときは、おわりの日に1日足して未満で比べる方法もある。9月30日までを数えたいなら、10月1日より小さいという条件にする。9月30日以下にすると、9月30日の10時のデータが落ちる。

祝日の判定のように日付どうしを突き合わせる表では、片方に時刻が付いているだけで一致しなくなる。取り込んだ側をINTで切ってから合わせる。

1900年2月29日という日がある

Excelは1900年を閏年として扱う。実際には1900年2月29日は存在しないが、シリアル値60として入っている。

確かめると、=DATE(1900,2,29)は60を返し、表示も1900/2/29になる。1900年2月28日から3月1日までの差を取ると2日になった。2026年で同じ計算をすると1日だ。

実務で1900年の日付を扱うことはまずない。ただ、取り込みに失敗した行がこの辺りに落ちることがある。空文字を日付に直そうとすると0になり、0は1900年1月0日として表示される。日付の列に1900年が出たら、それは日付ではなく取り込みの失敗だと思ってよい。

配った表では、1990年から2100年の外に出た行に色を付けている。範囲で弾くのがいちばん早い。

配ったブックの使い方

1枚目のA列は表示形式を文字列にしてある。ここに貼れば、元の形のまま入る。貼った時点で値が変わるのを止めてから、式で直す順番になる。

見本の12行で、はじめの日を9月1日、おわりの日を9月30日にして数えると、元の列では0件、直した列では11件になる。同じ表で数え方が違うのではなく、元の列は全部文字列なので1件も当たらない。

貼ったあとの形が変わるのを止める話は、AIに作らせた表がExcelで崩れるのほうに詳しく書いた。貼り先を先に文字列にするのは、日付の列でも同じだ。

確かめていないこと

Web版のExcelとGoogleスプレッドシートでは試していない。文字列を日付として読む決まりが同じかどうかは確認していない。

和暦は令和だけで試した。平成と昭和をまたぐ表記や、明治以前は確認していない。

地域の設定を英語にしたときの動きも確認していない。日付の読み方は地域の設定で変わるので、1-Sep-26や9/1の結果は変わるはずだ。ここで数えたのは日本語の設定での結果になる。