Search code examples
excelvbadatetext

Excel VBA - Convert date to display as text in the form MMM YYYY

I would like to convert Column A or Range A from date format to display as text format MMM YYYY.

Here is the worksheet layout:

enter image description here


However, no changes occur as I run the code:

Sub CONVERT_DATE()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks.Open ("MyWorkbook.xlsx")
Workbooks("MyWorkbook.xlsx").Activate

Dim ws As Worksheet
Set ws = Workbooks("MyWorkbook.xlsx").Sheets("MyWorkSheet")

Dim wsLastRow As Long
wsLastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

'Convert date to display as text in the format mmm yyyy

ws.Range("A2:A" & wsLastRow).Text = Format(ws.Range("A2:A" & wsLastRow).Text, "mmm yyyy")

Workbooks("MyWorkbook.xlsx").Close SaveChanges:=True

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Can anyone spot the issue(s)?

Many thanks!

Solution

  • Use NumberFormat instead of trying to set the Text

    Sub CONVERT_DATE()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks.Open ("MyWorkbook.xlsx")
Workbooks("MyWorkbook.xlsx").Activate

Dim ws As Worksheet
Set ws = Workbooks("MyWorkbook.xlsx").Sheets("MyWorkSheet")

Dim wsLastRow As Long
wsLastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

'Convert date to display as text in the format mmm yyyy

' change this line
ws.Range("A2:A" & wsLastRow).NumberFormat = "mmm yyyy"

Workbooks("MyWorkbook.xlsx").Close SaveChanges:=True

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub