EXCELマクロで日付をYYYYMMDDに変換

質問

EXCELマクロで、ドット記号有りの日付を、ドットなしの形式(YYYYMMDD)に変換したいです。

ドット有りとは、たとえば下記のようなものです
　2016.9.01
　2016.09.1
2016.9.1
2016.09.01

これを、20160901のようにするには、Replaceでうまく置換できれば良いのですが、ゼロが消えてしまったりしてうまくいきません。
マクロでどのように記述すればよいでしょうか？

エクセルのバージョンは2010です。

回答No.5

kagakusuki

例えば、A2以下に

　　　　　　A列
1行目　　　(空欄)
2行目　　2016.9.01
3行目　　2016.09.1
4行目　　2016.9.1
5行目　　2016.09.01

という具合に元データが入力されていて、A列のデータを基にしてB列にドットなしの形式(YYYYMMDD)に変換した値を出力したいという場合には、一例としては以下の様なVBAのマクロになります。

Sub QNo9242176_EXCELマクロで日付をYYYYMMDDに変換()

Const FirstRow = 2 '元データが入力されている最初の行
Const DataCouumn = "A" '元データが入力されている列
Const OutputCouumn = "B" '変換後の値を出力する列

Dim i As Long, buf As Variant, LastRow As Long

LastRow = Range(DataCouumn & Rows.Count).End(xlUp).row
If LastRow < FirstRow Then Exit Sub
For i = FirstRow To LastRow
buf = Range(DataCouumn & i).Value
buf = Replace(buf, ".", "/")
If buf <> "" And IsDate(buf) Then
buf = Format(buf, "yyyymmdd")
Range(OutputCouumn & i).Value = buf
End If
Next i

End Sub

　尚、「2016.9.01」などの様なドット付きの値が、最初から変数 buf に入っていて、そこから変数 buf に入っている値をドットなしの形式(YYYYMMDD)に変換したいという事でしたら、上記のVBAの構文の中の

buf = Replace(buf, ".", "/")
If buf <> "" And IsDate(buf) Then
buf = Format(buf, "yyyymmdd")
Range(OutputCouumn & i).Value = buf
End If

という部分の処理だけを行えば良い事になります。

　ついでにワークシート関数を使った例に関しても挙げておきますと、上記のVBAと同じ様にB2以下に結果を出力するのであれば、B2セルに次の関数を入力してから、B2セルをコピーして、B3以下に貼り付けて下さい。

=IF(COUNTIF($A2,"*?.*?.*?"),IFERROR(TEXT(SUBSTITUTE($A2,".","/"),"yyyymmdd")+0,""),"")

回答No.4

chie65535

因みに

=TEXT(SUBSTITUTE(A1,".","/"),"YYYYMMDD")

の式でも可能ですが、この式の場合は、不正な日付になると変換してくれないので注意が必要です。

例えば「2016.2.30」とか「2015.2.29」とか「2016.11.31」など「日付として有り得ない値」が指定されると、上手く変換しません。

当方が最初に回答した

=TEXT(LEFT(A1,FIND(".",A1)-1),"0000")&TEXT(MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1),"00")&TEXT(RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1)+1)),"00")

の式の場合は、13月とか14月とか、32日とか33日とかの「不正な日付」でも、それなりに変換します（日付として正しい値かどうかは関知しない）

回答No.3

ushi2015

こんにちは
選択した範囲が矩形で1列なら、
Sub test()
　 With Selection
　 　 .TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _
　 　 TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
　 　 Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
　 　 :=Array(1, 5), TrailingMinusNumbers:=True
　 　 .NumberFormatLocal = "yyyymmdd"
　 End With
End Sub
とかでも。

回答No.2

chie65535

マクロで書かなくても、セルに以下の式を入力すれば良い。
=TEXT(LEFT(A1,FIND(".",A1)-1),"0000")&TEXT(MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1),"00")&TEXT(RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1)+1)),"00")

回答No.1

Chiquilin

数式じゃいかんのかね。
=TEXT(SUBSTITUTE(A1,".","/"),"yyyymmdd")

For Each r In Selection
If r.Value <> "" Then r.Value = Val(Format(Replace(r.Value, ".", "/"), "yyyymmdd"))
Next r