エクセル 日付の修正

質問

お尋ね致します。
あるエクセルの表なんですが、A列が、31001（令和3年10月1日）という形の日付になっています。
これをyyyy/mm/ddに変えるため、B列の全行に2021と入力し、C列にmid関数で月を取り出し、D列にright関数で日を取り出し、この3列を使ってE列をdate関数で2021/10/01 にしています。
これよりスマートな方法があったらご教示願えませんでしょうか？　ちなみに最後の形はR3/10/01でもOKです。

回答No.1

chie65536（@chie65535） · ベストアンサー

E1に
=DATE(2018+A1/10000,MOD(A1/100,100),MOD(A1,100))
と書きます。

この式をE1に入れると「44470」のように、日付シリアル値が表示されるので、表示形式を「西暦年/月/日」や「和暦年/月/日」に変更しましょう。

なお、数値で「31001」のように年月日が区切り無しで入力されているのを、年、月、日に分解する場合、LEFTやMIDを安易に用いてはいけません。

「年が絶対に一桁」なら「月は２文字目から２文字分」ですが、令和10年を過ぎたら「月は３文字目から２文字分」になってしまいます。

MID関数を使って取り出している場合「年が１桁のデータと、年が２桁のデータが混在」していると、「月」の切り出し開始位置が２文字目の場合と３文字目の場合が混在してしまい、動作しなくなります。

MID関数を将来的にも使い続けるなら「元データが５桁なのか６桁なのかを判定して、２通りの切り出し方を切り替えする必要があります（「２０００年問題」のように「和暦２桁問題」が発生します）

提示した回答では「年は元の値を１００００で割った値」「月は元の値を１００で割って、それを更に１００で割った余り（つまり、千と百の桁の２桁）」「日は元の値を１００で割った余り」にしてあり、年が１桁でも２桁でも何桁でも、正しく動作します。

「令和１０年ころには、もう、このブックは使ってない」というなら問題は無視できますけど…。

お礼

ありがとうございました。大変助かりました。

回答No.3

imogasi

これを質問する前に、A列のセルのデータが、
（１）文字列で入っているのか（多分先頭に半角のアポストロフィが付いている）、（２）普通の数のようにエクセルのセルに、31001と入っているのか、
（３）その他、そのセルに対する表記形式の設定で、そうなっているのか、
（A)区別することの重要性を認識し、
（B）エクセルの日付の、「日付シリアル値」の仕組の理解が必要だが、そういうことは、質問に説明してない。初心者だろう。
ーー
なんとなく、セルには、数字で31001（３万１千１）と入っているのではないかと推測する。
ここのセルのイメージの、1部を取り出すために関数を使うには、文字列対象の関数しかなく（ほかの方法として、割り算の商や余りを使えるかも）、文字列化してから１部を取り出す(取り出したものも文字列の数字）を使う。
その場合には、後尾から、２桁，次の２桁、残り桁数を取り出す必要がある。
例データ　A2:E3
数 文字列化 日 月 令和年
31001 31001 01 10 3
120912 120912 12 09 12
後部からの２桁　日　=RIGHT(A2,2)
中間の２桁　月　　　=LEFT(RIGHT(A2,4),2)
年の部分　　　　　　=LEFT(A2,LEN(A2)-4)　　
これで文字列の数字が確定。
後は、この年、月、日の３つのデータを使って、日付シリアル値を作る。
F2に　=DATE(E3+2018,D3,C3)
C3やD3やE3は文字列で会ったはずだが、エクセル関数の引数部分では、文字列数字は、数字化して扱ってくれる、というお助け機能があるのでOK。
結果　F1:F3
西暦
2021/10/1
2030/9/12
ーー
そしてエクセル日付は、好みの日付の表示形式を設定するためには、セルの値が、
日付シリアル値（その日は、１９００年から何番目の日かの整数）でセットしておく
必要がある。
＝＝＝
以上の如く、ややこしいことになるので、エクセルでは、当初から、西暦で 2022/4/１３のようなデータ入力の必要性がわかるだろう。
ただ和暦の日付も年号漢字付きで、入力できるタイプ（即座に日付シリアル値に変換してくれる）があるので勉強のこと。

回答No.2

msMike

添付図参照(Excel 2019)
￣￣￣￣￣￣￣￣￣￣￣↓↓↓↓↓↓↓↓↓↓↓↓↓

お礼

ありがとうございました。