Excel 令和01(元年も対応)年と書いてあるタイプの新旧元号日付文字列を日付に戻すセルの式

いいからまず令和元年と書くな

書いたら動かないし、対応するのがもっと難しくなる

おそらくこれが有効

令和02年3月1日のように半角数字で、元号、年、月、日と入力されている

Rとかありえないでしょう。
あとスラッシュもそんなにやりたきゃ自分で考えろ。
Rはまだしも令で略すとマジで本当に使えない。こんなバカげた改元はひどすぎる。

令和を平成に変える

XPとかはアップデートしない、とMicrosoftが言っている以上、Microsoftは平成34年とかいう表記でも西暦に換算できるわけです。当然言っている以上そうなります。
このため、令和を平成に置換するとむしろまともに動きます。
これは向こう10年は使えると思います。

ただしそのまま置換ではだめ、かつ元年はだめ

一応対応しましたが、とても見づらいです。辞めましょう

置換すると30年ずらす必要がある

それで最初
令和01年12月01日 と入力してあるセルを参照する数式

=DATEVALUE(REPLACE(B3,1,2,"平成"))

を作ったんですよ。
すると
平成01年12月01日
になってしまいました。
ということは
1989/12/1になってしまうのです。

でもどうしても元年を考慮したいんだという生産性を下げる国賊の老害世代向け

年金をカットしてほしいんだろうな。本当にのろいかよ。
B3に
令和02年2月29日と入力します
また何らかの原因で昭和や明治が文字列として混在した場合を想定した回避を行います。

=IF(ISNUMBER(B3),B3,
IF(ISERROR(IF(OR(LEFT(B3,2)="昭和",LEFT(B3,2)="大正",LEFT(B3,2)="明治"),
DATEVALUE(SUBSTITUTE(B3,"元","01")),
DATEVALUE(REPLACE(SUBSTITUTE(B3,"元","01"),1,2,"平成")))),
DATE(MID(B3,3,FIND("年",B3)-3)+2018,LOOKUP(10,LEFT(MID(B3,FIND("年",B3)+1,FIND("月",B3)-FIND("年",B3)-1),COLUMN($1:$1))*1),LOOKUP(10,LEFT(MID(B3,FIND("月",B3)+1,FIND("日",B3)-FIND("月",B3)),COLUMN($1:$1))*1)),
  IF(OR(LEFT(B3,2)="昭和",LEFT(B3,2)="大正",LEFT(B3,2)="明治"),
   DATEVALUE(SUBSTITUTE(B3,"元","01")),
   DATE(
    YEAR(DATEVALUE(REPLACE(SUBSTITUTE(B3,"元","01"),1,2,"平成")))+30,
    MONTH(DATEVALUE(REPLACE(SUBSTITUTE(B3,"元","01"),1,2,"平成"))),
    DAY(DATEVALUE(REPLACE(SUBSTITUTE(B3,"元","01"),1,2,"平成")))
       )
)))

これを入れたところに2020年2月29日表示形式がないとシリアル値、2020/2/29が現れます。

元年を使わないと簡略化

平成以外の元号も考慮しないと平成と令和だけだとここまで簡単になります。
これはSUBSTITUTE(B3,"元","01")で元年があれば置き換える、という作業がないためです。

=DATE(YEAR(DATEVALUE(REPLACE(,1,2,"平成")))+30,MONTH(DATEVALUE(REPLACE(B3,1,2,"平成"))),DAY(DATEVALUE(REPLACE(B3,1,2,"平成"))))

まずこれが基本です。これから先はこれをエラー回避処理や昭和対応が加わっていると思ってください。

仕組み

老害どもへの対応その1

まず元年がある場合、元年から置き換えます。

Isnumberで文字列ではない日付に対応

なぜかDateValueは1980/1/2のような表記が変換できません。
この正体はシリアル値を日付で表示しているためです。
これをIsnumberではねます。

平成に戻す処理

さらに平成を令和に置き換えます

エラーチェック

これで一度、Iserror関数で元号を変換して、日付に変わるか試します
エラーになったら真、つまり日付になりません。
TrueになったらFalse、日付になっています。
この対応関係がややこしいですね。

エラーにならない場合は平成の30年ずらした日付

Falseの方から説明します
まず普通に日付になった場合は先に述べたように30年ずらす必要があります。
しかし、この場合、平成、令和以外が日付で入ってきた場合、それを除去します。
これらは、DateValue関数でそのまま変換できるためです。

DATE(YEAR(DATEVALUE(REPLACE(B3,1,2,"平成")))+30,
MONTH(DATEVALUE(REPLACE(B3,1,2,"平成"))),
DAY(DATEVALUE(REPLACE(B3,1,2,"平成"))))

特に、

YEAR(DATEVALUE(REPLACE(B3,1,2,"平成")))+30

この部分が令和を30加算しています。

これに元号処理が加わると

if(OR(LEFT(B3,2)="昭和",LEFT(B3,2)="大正",LEFT(B3,2)="明治"),
DATEVALUE(REPLACE(SUBSTITUTE(B3,"元","01")),
DATE(
   YEAR(DATEVALUE(REPLACE(SUBSTITUTE(B3,"元","01"),1,2,"平成")))+30,
   MONTH(DATEVALUE(REPLACE(SUBSTITUTE(B3,"元","01"),1,2,"平成"))),
   DAY(DATEVALUE(REPLACE(SUBSTITUTE(B3,"元","01"),1,2,"平成"))))
    )
)

もし昭和であれば特に変換はいらないのでDateValueにそのまま入ります。

閏年の2月29日

閏年でない場合はエラーになります
ここは、このようにしています
基本的に令和02年2月29日から02,2,29をとりだす
02に2018を足して2020にする
これをDatevalue(2020,2,29)に入れる

テクニックMidとFind

MidとFindで切っていきます。
年月日の入力が前提なので、これで切れます。
さらに数字は数字で取り出すため以下のテクニックを使います。

MID(B3,3,FIND("年",B3)-3)+2018

ここで年を抜き出します。
この時、かならず平成か昭和か元号は2文字であることが前提です
これはExcelのカレンダーが1900年以降であるため、このように設定しても障害はないでしょう。
そして2018を足してずらします。
元年があるときは、B3が元年の置き換えが入ります。
そしてここは令和元年対応はいりません。
なぜいらないかというと、元年は平成元年で成功するからです。
また、エラーになるのは本当の閏年の日付を閏年に変える場合です。
令和元年2月29日は存在しません。
しないということは変える必要がないのです。

LOOKUP(10,LEFT(MID(B3,FIND("年",B3)+1,FIND("月",B3)-FIND("年",B3)-1),COLUMN($1:$1))*1)

次に月はどうでしょう。月は02や2が考えらえます。
そこで、下記のロジックを用い、抜き出した文字列を数字に変換します。
そして02でも2でもかならず数字を抜き出します。
スタートは年の次の位置ですね。
Columnは常に$1:$1です
10^7は十分に大きい程度の意味なので10程度でいいと思います。
これと同じようにFindで絞り込んで日付も数字に変えます。
このようにして得たものをDateにいれて日付にします。

シリアル値はIsNumberでエラー回避

式とは流れが逆ですが、最後に

=IF(ISNUMBER(B3),B3,

昭和は本来は入力すると自動的に日付になります。
このため文字列は想定しなくてもいいのですが、文字列で入力できるので、文字列で入ってきた場合は、DateValueでそのまま日付に変えます。
しかしこのDateValue関数はなぜか1980/1/1のような日付の値には反応しません。
日付の正体はシリアル値です。このためIsNumberでTrueの時は、そのまま値を反映します
以上のような処理を経て、元号換算、エラー処理ができます。
他方現在のところ令和はなったりならなかったりしますが、システムが対応すればそのまま処理されるようになるので、この式はMSのアップデートでも問題がないと思います。

参考リンク

Excelで数字のみを抽出する関数、文字のみを抽出する関数
https://qiita.com/mhara/items/82421d1b34e88a3efba1