CODEWITHSUNDEEP
vbaexcel
VBA Pete
VBA Pete

Reputation: 2666

Converting a date to text in VBA

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

Upvotes: 5

Views: 63521

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

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

Upvotes: 9

Related Questions