Excelの日付の変換を関数でやるかマクロでやるか

Excelで日付のデータを変換しなければいけない場合があります。
その変換に関数・マクロといった方法を解説してみました。

Excelで日付の処理をする必要性

Excelでは、他のソフトからダウンロードしたデータを使うことがあります。
しかしながら、このようなデータだと、Excelでは日付とみなしません。

このまま集計したり、計算したりするときには不都合なのです。
Excelで、2020/8/1といった形式に変換しなければいけません。

 

日付の変換をExcelの関数でやる方法

日付の変換をするなら、まずは、Excelの関数でやる方法があります。
使うのは、DATE、LEFT、MID、RIGHTです。

まとめて1つにするなら、こう入力します。
(セルA2に対象のデータがある場合)

=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))

ただ、こうやってわけて考えたほうが、わかりやすいでしょう。
DATE関数は、=DATE(年、月、日)で、日付データをつくります。

20200801から、年、月、日を取り出せばいいのです。
20200801のうち、年は、2020であり、LEFT関数で、左から4つ取り出します。

次は月。
20200801の08を取り出すには、5文字目から2文字取り出せばいいことになるので、MID関数で取り出します。

最後は日。
20200801から、01を取り出すには、右から2文字取り出せばいいので、RIGHT関数で取り出します。

 

これらをDATE関数で、日付データにするのです。

 

令和の場合は、020801となるので、年を西暦にします。
=VALUE(LEFT(a2,2))+2018
と、左から2文字取り出し(02)、それをVALUEで数値にして(2)、2018を足せば、2020です。
CSVファイルを開くときに、20801にしてしまうと、さらに工夫が必要となります。
CSVファイルをを開くときに、A列は文字列で開いたほうがいいでしょう。

データとして、令和で出すソフトは使うのをやめたいところですけど。

日付の変換をExcelマクロでやる方法

この日付の変換をExcelマクロでやる方法もあります。

こういったプログラムです。

 

セルA2にあるデータを変換し、セルB2に表示しています。
変換は、Formatで、####/##/## としました。

令和で、020801だと、こんな感じでしょうね。
変数y(年)、m(月)、d(日)で、
y(年)は、左から2文字取り出し、それを数値にして(Val)、2018を足して2020
m(月)は、左から3文字目から2文字取り出して08
d(日)は、右から2文字取り出して01

これらを&でくっつけて、20200801にしてから、Formatで2020/8/1にします。
関数のほうが楽ですね。
CSVファイルを開くときに、20801にしてしまうと、さらに工夫が必要です。
CSVファイルをを開くときに、A列は文字列で開いたほうがいいでしょう。

 

マクロの場合、こういった連続したデータも変換ができるのがメリットです。

 

A列にあるデータを数えて、2行目からその数まで繰り返すというプログラムです。

このようにさくっと変換できます。
テンプレートのファイルをつくっておき、ダウンロードしたら貼り付けて、マクロを実行するといいでしょう。

プログラムをこのようにちょっと変えると、
(変換後のデータをA列に書き込んでいます)

 

A列のデータに

変換後のデータで、上書きすることもできます。

元のデータを上書きするのは、原則としておすすめできませんが、使える場面もあるかと。
(会計ソフトのデータを毎回使うときはこっちでもいいかもしれません)

 

関数とマクロ、どちらも使う場面があるので、両方抑えておくといいでしょう。
私の場合は、日付以外の処理も含めて、関数を使うことが多いです。
関数が入ったセルをデータの数だけコピーするところにはマクロを使っていますが。

RPAでやっていることもあり、RPAならダウンロード・エクスポートを自動化し、そのときに変換も自動化することもできます。

 



■編集後記

昨日はオフ。
家族で出かけたり、ガンプラつくったり、ジブリ観たり、ゲームしたり、オンラインヨガしたり。

「1日1新」
有明ダイソー
ミッキーマウス 水鉄砲
RG シャア専用ザク完成

■娘(3歳5ヶ月)日記
背中に背負うタイプの水鉄砲を買いました。
外出から帰ってきてからの水浴びで早速使用。
うまく使えていました。