Converting a date to text in VBA

Question

I have a data set that includes values as text such as "March 2016".

Now I have written the following code to replicate this in my report sheet:

DataFin.Range("E9:E9") = DateSerial(Year(Date), Month(Date), 0)
DataFin.Range("E9:E9").Text = Format(DateSerial(Year(Date), Month(Date), 0), "mmmm yyyy")

It has the correct format, but when I click on the cell the value states "3/1/2016" instead of "March 2016".

Any ideas how I can change the underlying value to "March 2016"

Thanks,

Pete

Answer 1

You must format the cell as text, and then set the value to the text value of the date you want.

For example:

Sub demo()
    Dim DT As Date
DT = Date

With Range("a1")
    .NumberFormat = "@"
    .Value = Format(DT, "mmmm yyyy")
End With
End Sub