日付データは通常1つのセルに表示すれば済むのですが、元号・年月日を別々のセルに表示するよう求められることもあります。
その場合の対処方法についてまとめてみました。
元号・年月日のデータが求められる現状…。
データを加工するときに日付という要素は必須です。
いつ売ったか、いつ買ったか、いつ入金があったか、いつ支払ったか、社員の生年月日などで利用されることは多いです。
ただ、日付の表示形式はバラバラです。統一してくれればいいのですが。
わたしは和暦（令和3年12月22日）より西暦（2021年12月22日）のほうががわかりやすくて好みです。
令和とか平成とか変える必要がないので。
会計ソフトに仕訳データをインポートするときには1つのセルに日付データを入力するようになっていいて、西暦の日付表示で問題なくインポートできるわけですが、
ときには西暦でなく和暦。さらに元号、年、月、日を別々のセルに入力することを求められることもあります。
UiPath（RPA）でJDL財産評価ソフトに入力するための減価償却データを準備するマクロ – GO for IT 〜 税理士 植村 豪 Official Blog
なぜわざわざ分ける？とは思うのですが。
ただ、効率化したいなら、こうしたムダに高いカベを超えなければいけません。
Excelで西暦表示から元号データを抽出するには？
今回の事例は、eLTAXの源泉徴収票の事例です。
別にある西暦の日付データから数式をつかって、このレイアウトに沿ったCSVファイルをつくりたいという流れです。
めちゃめちゃデータ数が多いので、省略しますが、今回とりあげるのはそのうちの日付のデータ。
- 44列目に元号
- 45列目に年
- 46列目に月
- 47列目に日
と表示することが求められています。
さらに、
- 元号は昭和は「1」、平成は「4」、令和は「5」と表示
- 年や月日が1桁の場合には、あえて2桁にしないといけない
という条件付きです。
1つのセルにある西暦の日付データからこのような条件で別々のセルにデータをつくることは可能なのか。
結論から言えば、わけることは可能です。
B2セルにある西暦の日付データをB6〜E6セルまでに元号、年月日とレイアウトどおりのかたちに数式で反映させるとこうなります。
ややこしいのですが、このように対処しないとCSVファイルとして機能しない以上は、なんとしても変換させたいところです。
では、その方法を順を追って解説してみます。
元号のデータ
まずは元号。
元号は昭和は「1」、平成は「4」、令和は「5」と表示するというルールです。
もともとある西暦の日付データの年数からそれを判断する必要があります。
元号だけをどうやって判断するか。
ここで日付データの実態が数字であるという点に目をつけます。
日付データは1900年1月1日を「1」として、何日目かという数字を日付形式で表示しているものです。
1975年2月6日という表示を数字で表示すると、実は27431だということがわかります。
参考までに。それぞれの元号の最初の日はこういった数字です。
- 昭和「1」 1926/12/25 →9856
- 平成「4」 1989/1/8 →32516
- 令和「5」 2019/5/1 →43586
ということから。もし、B2セルの数字が
- 43586以上なら「5」（令和）
- 32516以上なら「4」（平成）
- それ以外なら「1」（昭和）
というように。ifを使った数式で、条件通りに元号を表示させることができます。（まだ、令和時代の人はいないでしょうが…）
|
1 |
=IF(B2>=43586,"5",IF(B2>=32516,"4","1")) |
年のデータ
次に年です。
通常はyear関数を使って表示すれば年を抽出することはできます。ただ今回の場合で言えば、西暦の年1975が抽出されます。
ただ、今回の場合には和暦の元号で、文字数は2となっていることから、たとえ1桁の年であっても「03」のように表示される必要があります。
そこでTEXT関数を使います。日付書式を文字列として扱います。
|
1 |
=TEXT(B2,"e") |
とすると、和暦の年で「50」と表示することができます。
ちなみに「e」というのは和暦の「年」を表示する記号です。
月のデータ
月のデータも、1桁の月でも2桁表示にする必要があります。「07」のように。
こちらも先程と同様にTEXT関数を使います。
|
1 |
=TEXT(B2,"mm") |
「m」は月ですが「m」が1つだと1桁で「2」しか表示されません。2桁にするために「mm」とすれば「02」と表示することができます。
日のデータ
最後に日のデータ。
こちらも月と同様です。TEXT関数を使って「dd」とすれば1桁の日でも2桁で表示させることが可能です。
|
1 |
=TEXT(B2,"dd") |
ということで、西暦の日付データから、CSVファイルの仕様書どおりの表示をさせることができました。
データを使って入力をしない執念
こういうことをやると手で入力したほうが速いじゃんと思われるかもしれません。
確かに。1回やるだけならそうです。
ただ、この処理を50人やるなら、さらに毎年やるならその負荷はそれなりです。
そこでデータがあればどうすればいいかを考えることができます。
たとえば、1つのシートにデータを貼り付けたら、別のシートで自動変換してくれる、というしくみｇがあれば、将来的な効果はあるかなと。
入力が好きならそれはそれです。ただ、すでにデータがあるなら、多少カタチが違っても利用しない手はないかなと。
利用できるカタチに変えることも負荷はかかりトレーニングになりますし、今後の工夫もしやすくなるものです。
すでにデータがあるならば、効率化のためにも大事にしたいものです。まぁ意地もありますが。
【編集後記】
昨日は年末調整を終えてからお客様の決算報告でした。夜はふるさと納税で届いたハンバーグに子どもたちが興奮ぎみでした。
【昨日の1日1新】
※「1日1新」→詳細はコチラ
ボンルーンのパン
ふるさと納税で届いたハンバーグ
