8桁の数値を日付に変換する

---

下図A列のような"単なる8桁の数値"を、Excelが計算できる日付(シリアル値)に変換する方法です。

これ、いろんなやり方があります。マクロを使わない方法は、下記のページをご覧ください。

取得と変換(Power Query)：数値を日付に変換

機能のTips：8桁の数値を日付に変換する

上記、機能編のTipsでは「区切り位置」を使って変換する方法をご紹介しました。じゃ、それをマクロでやるなら、どう書くかってのが本稿の内容です。まずは、マクロ記録してみましょう。ここでは、セル範囲A2:A7に"なんちゃって日付もどき"が入力されていて、シリアル値に変換した結果を、セルB2から下に代入するとします。手動でやるなら、まずセル範囲A2:A7を選択しておかなければなりません。その状態で操作すると、次のようなコードが記録されます。具体的な操作手順は、上記のページをご覧ください。

Sub Macro1()
    Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
End Sub

マクロ記録で得られるコードは"答え"ではなく"ヒント"です。記録されたコードを、意味も分からず、そのまんま使うことだけは、決してしないでください。では、記録されたコードをよく"読んで"、書かれている意味を"理解"して、加筆修正していきましょう。まずは冒頭の「Selection」です。これは"選択されているモノ(オブジェクト)"という意味で、今回でしたら、[区切り位置]を実行する前に選択しておいたセル範囲A2:A7です。マクロでは、操作対象のセルをあらかじめ選択する必要はありません。なので、これを次のように直します。

Sub Macro1()
    Range("A2:A7").TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
End Sub

「Destination:=Range("B2")」は、変換した結果を代入する先頭セルです。これを省略すると、元データに上書きされます。今回は、セルB2から下に代入したいので、これはこのまま残します。「DataType:=xlDelimited」というのは、ウィザード1/3で指定する[コンマやタブなどの区切り文字によってフィールドごとに区切られたデータ]です。これ、省略すると[コンマやタブなどの区切り文字によってフィールドごとに区切られたデータ]になりますので、今回は省略できます。

Sub Macro1()
    Range("A2:A7").TextToColumns Destination:=Range("B2"), _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True
End Sub

「TextQualifier:=xlDoubleQuote」は、文字列が""などで囲まれているとき、その引用符を指定します。今回は関係ありませんね。これも省略できます。次の「ConsecutiveDelimiter:=False」は[連続した区切り文字は1文字として扱う]かどうかです。これも同様に、今回は元データに文字列がありませんので関係ありません。こいつも省略可能です。

Sub Macro1()
    Range("A2:A7").TextToColumns Destination:=Range("B2"), _
        Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
        FieldInfo:=Array(1, 5), TrailingMinusNumbers:=True
End Sub

ちょっと、見やすいように改行位置を調整しました。さて、何やら面倒くさそうなのが「Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False」です。これは、ウィザード2/3で指定する「区切り文字」です。データがどんな文字で区切られているかを指定します。標準では「タブ」が選択されています。もし、CSVデータなどカンマで区切られたデータを変換するのでしたら、ここで[コンマ]チェックボックスをオンにします。とはいえ、今回の元データは"なんちゃって日付もどき"です。区切られていません。なので、ここはすべて省略できます。

Sub Macro1()
    Range("A2:A7").TextToColumns Destination:=Range("B2"), _
        FieldInfo:=Array(1, 5), TrailingMinusNumbers:=True
End Sub

残るは「FieldInfo」と「TrailingMinusNumbers」です。まず、最後の「TrailingMinusNumbers:=True」ですが、このオプションは分かりにくいです。ウィザード3/3で、変換後に指定する表示形式を指定できるのですが、ここに[詳細]ボタンがあります。この[詳細]ボタンをクリックして表示される[テキストインポートの詳細設定]ダイアログボックスにある[負の数はマイナスを表示する]という設定です。言うまでもなく、今回は関係ありませんから省略です。

Sub Macro1()
    Range("A2:A7").TextToColumns Destination:=Range("B2"), FieldInfo:=Array(1, 5)
End Sub

マクロ記録したコードから不要な設定を省略したら、1行になりました。最後の「FieldInfo:=Array(1, 5)」は必須です。Arrayというのは、VBAのArray関数です。Array関数は、引数に指定した値を格納した配列を作ってくれます。Array(1, 5)の「1」は"1列目"の意味です。そもそも[区切り位置]機能は、ひとつの文字列を、複数列に分割する機能です。その、分割した各列に対して個別に表示形式を指定できます。今回の"なんちゃって日付もどき"は区切りません。てゆーか、区切れません。今回は、文字列を区切って分割しようというのではなく、その過程で指定できる「形式の変換」を利用します。したがって、元データは1列しかありませんし、変換後も1列です。Array(1, 5)の「1」には、毎回「1」を指定します。さて、分かりにくいのがArray(1, 5)の「5」ですね。これは、分割後の各列に、どんな表示形式を設定するかを指定しています。

ここには、次の数値または定数を指定します。

定数	値	形式
xlGeneralFormat	1	標準
xlTextFormat	2	文字列
xlMDYFormat	3	MDY (月日年) 形式の日付
xlDMYFormat	4	DMY (日月年) 形式の日付
xlYMDFormat	5	YMD (年月日) 形式の日付
xlMYDFormat	6	MYD (月年日) 形式の日付
xlDYMFormat	7	DYM (日年月) 形式の日付
xlYDMFormat	8	YDM (年日月) 形式の日付
xlEMDFormat	10	EMD (台湾年月日) 形式の日付
xlSkipColumn	9	スキップ列

定数xlEMDFormatは、簡易字中国語サポートがインストールされている環境で使用可能です。今回指定した「5」は「YMD (年月日) 形式の日付」です。日本では、この"年月日"という並びが一般的ですし、嫌なら変換した後のセルに別の表示形式を設定すればいいです。なので、Array(1, 5)の「5」も、毎回同じ「5」を指定すればいいです。

ということで、8桁の"なんちゃって日付もどき"を、Excelで集計・計算できる日付形式(シリアル値)に変換するには、

Sub Macro1()
    Range("A2:A7").TextToColumns Destination:=Range("B2"), FieldInfo:=Array(1, 5)
End Sub

とします。元データが入力されているセル範囲(ここではセル範囲A2:A7)や、引数Destinationに指定する代入先(ここではセルB2)を、どのように指定するかはケースバイケースですね。状況などに合わせて、適時指定してください。