Excel。数値を元号表示の日付に変えたいけど、うまくいかないので、どうしたらいい？
＜TEXT関数・LEFT関数・MID関数・RIGHT関数&DATE関数＞
最初から日付として入力されているデータならば、
単純に表示形式を変更することで、和暦にしたり、西暦にしたり、
年月日にしたりなどできるのですが、
下記のようなデータの場合、なかなか、大変な作業が必要になるのです。
では、このようなことをしたいわけです。
A列に申込日というのがあって、単純に日付を数値として入力してあるそうです。
さらに、年は元号というものなのですが、
これをB列のように西暦表示にしたいというのが今回のやりたいことなのです。
別に難しくはないでしょう？と思った方も多いかと思いますが、
今回の曲者は年が元号ということなのです。
西暦の場合だと、どうなるのかを確認してみましょう。
次の表を見てみましょう。
こちらの場合は、A列は、西暦で数値を入力しております。
B列のように、こちらは、/（スラッシュ）で区切られている西暦表示にしてあります。
B8の数式は、
=DATE(C8,D8,E8)
関数に慣れている人でしたら、LEFT関数などをネストしながら算出することもできると思いますが、
今回は、C列に年。D列に月。E列に日。
を求めて、DATE関数を使って日付を算出させています。
C8には、=LEFT(A8,4)
左から4文字分ということですね。
D8には、=MID(A8,5,2)
5文字目から2文字分ということですね。
E8には、=RIGHT(A8,2)
右から2文字分ということですね。
という数式がそれぞれ設定されております。
ということで、西暦の場合は、それほど困らずに算出することができます。
なお、表示形式で、/（スラッシュ）が入る表示形式に変えたらいいのでは？
と思われるかもしれませんが、それはできません。
日付。Excelではシリアル値というもので管理しております。
1を1900年1月1日として、一日を1としてカウントするようになっております。
ですから、1900年1月1日から20160614日後を指すことになってしまうので、
表示形式では対応できないわけです。
そこで、本題に戻りますが、このシリアル値のこともあり、表示形式は使えません。
C列の年。D列の月。E列の日。は先ほどと同じように算出しております。
先ほどの西暦は、DATE関数を使って算出できましたが、今回はどうでしょうか？
B3をクリックして、DATE関数ダイアログボックスを表示しましょう。
年には、C3
月には、D3
日には、E3
これで、OKボタンをクリックしましょう。
残念ながら、DATE関数ではダメでした。要するに年が元号という認識がないわけです。
だから、今回のように簡単に思われるかもしれませんが、一筋縄では、いかないわけです。
今回は、TEXT関数と表示形式を使って算出していきます。
C3の数式は、
=TEXT("H"&C3&-D3&-E3,"yyyy/mm/dd")*1
というように設定していきます。それでは、C3をクリックして、TEXT関数ダイアログボックスを表示しましょう。
値の"H"&C3&-D3&-E3 ですが、
"H"は元号の頭文字です。平成なのでHです。
もし昭和の場合は、Sで作成する必要があります。&で年月日を結合させるのですが、
-（ハイフン）を年月日の間にいれることで、文字タイプの日付型に変えることができます。
/（スラッシュ）を入れたいところですが、/（スラッシュ）では、結合することができません。
この時点で、値の結果は、H27-5-9 となるのですが、
西暦表示にしたいので、TEXT関数を使って、表示形式を"yyyy/mm/dd"とします。
そしてOKボタンをクリックしましょう。オートフィルを使って数式をコピーしましょう。
とりあえず、完成しました。
ただ、完成ではあるのですが、先ほど作りました、
B8は、右揃えになっていますが、B3は左揃えになっていますよね。
本来日付は、右揃えになるはずですよね。
実は、TEXT関数を使って求めたB3:B5は、文字タイプの日付になっているのです。
そこで、×1をすることで、シリアル値にすることができるようになりますので、
B3の数式の後ろに×1を追加します。
よって、
B3の数式は、
=TEXT("H"&C3&-D3&-E3,"yyyy/mm/dd")*1
となっているわけです。このように、日付を用途に合わせて、表示をかけたい場合、
簡単にできないケースもありますので、最初から、
日付形式で入力するように心がけるといいかもしれませんね。
