Excel関数の式の作り方を教えてください

過去の質問履歴を検索したのですが、上手く行かないので教えて下さい・・・。

エクセルの表で「500401」というような日付があるとします。

このようなデータを西暦で「1975/04/01」と別セルに表示させたいのですが、どのようにすれば良いのか分かりません・・・。

また、平成などで「60629」という５桁の日付の場合にも、西暦で「1994/06/29」と別セルに表示させたいです。

マニュアルなどを見て関数を作っても上手く行きません。
分かる方がいらっしゃったらよろしくお願いします！

質問者からの補足コメント

• ・5桁も6桁も同じ列に存在するデータです。
  ・隣の列に元号が記載されています（昭和、平成）
  ・上記の対応にIF・LENの関数と、TEXTでLEFT、MID、RIGHTの関数を使う人もいるようです。私はその根拠が分かっていないからか、同じものを使ってもきちんとした関数の式が作れず、エラーが出てしまいます。
  
  よろしくお願いいたします。

    補足日時：2024/07/29 14:47

No.5 回答者： enunokokoro 回答日時： 2024/07/29 17:18

色々な方法で対応できるかと思いますが。

A列に元号、B列に日付もどきがあるとします。
C列に西暦を求めるとしたら、
　=DATEVALUE(A2&TEXT(B2,"00年00月00日"))
などでシリアル値を求めます。C列の書式をyyyy/mm/ddに
してあれば、希望の西暦になっているかと思います。

元号がS・H・Rなどの英頭文字の場合も以下のような方法で対応。
　=DATEVALUE(CHOOSE(FIND(A2,"SHR"),"昭和","平成","令和")&TEXT(B2,"00年00月00日"))

この回答へのお礼

丁寧に教えていただき、ありがとうございます！
自分のスキルではとてもできませんでした・・・。

お礼日時：2024/07/30 08:50

No.4 回答者： タマタマポチ 回答日時： 2024/07/29 15:13

情報の後出しは二度手間、三度手間です。

5桁6桁混在で500401→50401これが50年4月なのか5年04月なのかで意味が変わってきます。
これでは作ることが出来ません。

この回答へのお礼

コメントありがとうございます。
初めての投稿なのと関数初心者のため、質問内容が不十分となってしまいました。申し訳ございませんでした。
5桁は初めにゼロが付くが、データ上はそのゼロが表示されないという意味でした。（60629＝平成6年6月29日）

お礼日時：2024/07/29 15:50

No.3 回答者： タマタマポチ 回答日時： 2024/07/29 13:06

> 平成などで「60629」という５桁の日付の場合にも、西暦で「1994/06/29」と別セルに表示させたいです。

　
60629がA1にあるとして、表示させたいセルに
=DATEVALUE("H"&LEFT(A1,1)&"/"&MID(A1,2,2)&"/"&RIGHT(A1,2))
　
表示させたいセルの書式設定を「yyyy/mm/dd」とする。
　
ただし、上記は平成であって令和であれば「"H"&LEFT」のHをRに変更、昭和であればSとする。

この回答へのお礼

教えていただき、ありがとうございます。
別セルに元号が入っていますが、それを含めていくことと、５桁と６桁の両方が混在するデータを扱っているからか、上手くできません…。もう少し情報を集めたいと思います。

お礼日時：2024/07/29 13:55

No.2 回答者： kigoshi 回答日時： 2024/07/29 13:06

ご質問の条件だけでは正確に西暦表示ができない場合があります。

例）291224→昭和29年12月24日　→1954/12/24
　　　　　　→平成29年12月24日　→2017/12/24

　　050322→平成5年3月22日　→1993/03/22
　　　　　　　令和5年3月22日　→2023/03/22

対象日付の範囲指定をするか、元号をどこかに入れるかしないと一意にならないと思います。

この回答へのお礼

教えていただき、ありがとうございます。
別セルに元号が入っているものの、５桁と６桁の両方が混在するデータを扱っているからか、上手くできません…。もう少し情報を集めたいと思います。

お礼日時：2024/07/29 13:53

No.1 回答者： cn94 回答日時： 2024/07/29 12:45

エクセルで「500401」を「1975/04/01」と表示するためには、以下の手順を踏むことで可能です。

元号の年数を西暦に変換するための計算を行う関数を使用：
昭和50年（=西暦1975年）を計算するためには、昭和元年（=西暦1925年）の差を計算する必要があります。昭和の年数に1925を足すことで西暦を得ることができます。

セルの内容を日付形式に変換：
セルのデータを分割し、年月日に分けて正しい日付形式にします。

以下のようにエクセルの関数を使用します。

手順
セルA1 に「500401」が入力されていると仮定します。
セルA2 に以下の関数を入力します。

=TEXT(DATE(1925+INT(LEFT(A1,2)), MID(A1,3,2), RIGHT(A1,2)), "yyyy/mm/dd")


関数の説明
LEFT(A1,2): A1セルの左から2文字を取得します（昭和の年数部分、例：50）。
INT(LEFT(A1,2)): 取得した文字を整数に変換します。
MID(A1,3,2): A1セルの3文字目から2文字分を取得します（月部分、例：04）。
RIGHT(A1,2): A1セルの右から2文字を取得します（日部分、例：01）。
DATE(1925+INT(LEFT(A1,2)), MID(A1,3,2), RIGHT(A1,2)): DATE関数で昭和年を西暦に変換し、月と日を設定します。
TEXT(DATE(1925+INT(LEFT(A1,2)), MID(A1,3,2), RIGHT(A1,2)), "yyyy/mm/dd"): 最終的な日付を「yyyy/mm/dd」形式で表示します。
これにより、A1セルの「500401」がA2セルに「1975/04/01」として表示されます。

by GPT

この回答へのお礼

教えていただいた通りに進めてみたらできました！
今まで試行錯誤していた時間は何だったのかと思うくらいにスムーズでした。
本当に助かりました。ありがとうございました。

お礼日時：2024/07/29 12:54