エクセルで日付の文字列のシリアル値化

質問

E5セルに
2020年7月1日～2021年6月1日　という文字列があります。
AFセルに
=IF(E5="","",LEFT(E5,FIND("日",E5))*1)
AG5セルに
=IF(E5="","",MID(E5,FIND("～",E5)+1,11)*1)
として日付をシリアル値で取り出しています。

このエクセルを他の人にわたしたのですが、その方がE5に
2020年8月1日～2021年9月1日 という文字列をコピペしたところ
AF5もAG5もVALUEエラーになってしまいました。
最初、入力した文字列の形式が誤っているのではと思って、メールでファイルをおくってもらったのですが、入力形式に問題はなく、VALUEエラーが出ていました。
わたしの端末で、そのセルにカーソルをあて、エンターキーを押すと日付に変わりました。
どのような原因でこうなるのでしょうか？
VALUEエラーが出ないようにするのはどうすればいいのでしょうか？

回答No.3

Chiquilin · ベストアンサー

「2020年7月1日」が日付として認識されていないものと思われます。
その PCの日時の環境設定が「日本」以外になっているんでしょう。

日本にローカライズされていない PCで「1/7/2020」と入力しても
シリアル値に変換されないのと同じ話です。

> VALUEエラーが出ないようにするのはどうすれば
(1)エラーが起きる PCの地域の日時設定を変更する。
(2)「日本語の日付」からシリアル値に変換しない。
のどちらかだろうと思います。

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND("～",A1)-1),"年","/"),"月","/"),"日",""))

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(A1,1,FIND("～",A1),""),"年","/"),"月","/"),"日",""))

お礼

Chiquilinさん、ありがとうございます！
>PCの日時の環境設定が「日本」以外になっているんでしょう。
まさにこれでした！！
ほんとうにありがとうございます。

回答No.2

iyonohanamuko

2020年8月 1日～2021年 9月31日
↑のように半角スペースが入っているとかではないですよね…？

お礼

ありがとうございます。わたしも最初はそう思い、ファイルを送ってもらって中を調べたのです。でもデータは正しいものでした。計算式が壊れたのかと式を見ても壊れてません。そこでエンターキーを押したらそれまでVALUEエラーだったセルが日付に変わったのです！不思議です。

回答No.1

kkkkkm

=IF(E5="","",DATEVALUE(MID(E5,FIND("～",E5)+1,11)))

とかにしてみてはいかがでしょう。

お礼

これでためしてもらいましたがやはりVALUEエラーでした。
ありがとうございます。