Googleスプレッドシート。日付を元号表示にするには？
＜Googleスプレッドシート＞
Googleスプレッドシートを使っているけど、
Excelみたいに出来ない・うまくいかない。という声を良く聞くものの中で、
まぁ、一番多いのは、
日付をどうやったら、元号表示に出来るのですか？
というものかなぁ～と。
Googleスプレッドシートの表示形式では、
今の所、残念ながら、元号表示にすることができないようなのです。
というか、元号自体を認識してくれてないようなのです。
このように、F5に平成29年1月1日と入力して、
F6に1を入力して、F7に=F5+F6という和算の数式を作ってみると、
#VALUE!
というエラーが表示されてしまいました。
なんと、平成29年1月1日はテキストということで、文字になってしまっているのです。
では、表示形式では出来ないとしても、元号表示をすることは、
完全に【白旗】なのかというと、面倒ではありますが、次のような数式を作れば、
元号で表示するコトはできます。
以前作成したスケジュール表を使って、元号表示を作成してみましょう。
A5には、DATE関数で日付が作成されています。
A5の数式は、
=DATE($A$2,$B$2,row()-4)
E5に次のような数式を作ってみましょう
="平成"&(YEAR(A5)-1988)&"年"&text(A5,"mm月dd日")
かなり強引な数式ですね。
2017年は平成29年なので、
どうやったら、29という数値を算出できるのか？といったら、
平成が始まったのが、1989年からなので、1988を引いてあげれば、
29という数値が算出出来ます。
あとは、月日を合体させてあげるという形にすれば、元号表示が可能になります。
ただ、この数式は欠点がありまして、昭和が対応していない。
そして、平成1年という表示よりかは、平成元年という【元年】の表示にしたいですよね。
この2点に対応した数式に変更していきます。
まぁ大正も明治も対応させたいのですが、
数式が煩雑になるので、今回は、昭和・平成のみの対応に今回はさせていただきます。
さらに、昭和元年も今回はパスということで…
準備として
昭和最後の日は、昭和64年1月7日で1989/1/7 シリアル値は32515
平成最初の日は、平成元年1月8日で1989/1/8 シリアル値は32516
平成元年大晦日は、平成元年12月31日で1989/12/31 シリアル値は32873
これらのシリアル値を使っていきます、
そうしないと、いちいち年月日を使わないと行けなくなりますので、
数式を作るのに、不便ですね。
そして、Googleスプレッドシートは、
Excelと異なり、ダイアログボックスがありませんので、
直接入力で数式を作る必要があります。
では、作成する数式は次のようになります。
=if(A5>32515,if(AND(A5>32515,A5<=32873),"平成元年"&text(A5,"mm月dd日"),"平成"&(YEAR(A5)-1988)&"年"&text(A5,"mm月dd日")),"昭和"&(YEAR(A5)-1925)&"年"&text(A5,"mm月dd日"))
う～む長い！
どういう考え方なのかというと、
まず、平成か昭和かを判断するのでIF関数の後に>32515という条件を使っています。
そして、平成元年なのか？ということを判断させる必要がありますので、
AND関数を使って(>32515,<=32873)という条件を使っているわけですね。
Googleスプレッドシートさん。
いずれ、元号表示を出来るようにしてくださいね。
