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マクロでやる方法もあります。

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

Sub date_conversion()

    Range("b2").Value = Format(Range("a2").Value, "####/##/##")

End Sub

 

セル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列は文字列で開いたほうがいいでしょう。

Sub test()

     Dim y
     Dim m
     Dim d
     
     y = Val(Left(Range("a2").Value, 2)) + 2018
     m = Mid(Range("a2").Value, 3, 2)
     d = Right(Range("a2").Value, 2)
    
    Range("b2").Value = Format(y & m & d, "####/##/##")

End Sub

 

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

 

Sub date_conversion()


    Dim i
    For i = 2 To Range("a" & Rows.Count).End(xlUp).Row
    
        Range("a" & i).Value = Format(Range("a" & i).Value, "####/##/##")
    
    Next
    



End Sub

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

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

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

 

A列のデータに

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

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

 

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

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

 

---

■編集後記

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

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

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