Change cell values containing dates to the text shown after date formatting

Question

I have a column of dates in excel that show up as mmm/yy (the left column) because of the cell formatting, when in actuality the cell values are full dd/mm/yyyy dates (as shown in the right column) [image here].

How do I get the actual cell values of the left column to be of the same mmm/yy text they are showing? I've tried looping through the column and using cell.value = cell.text but it ends up changing the day of the date to its year for some reason.

Answer 1

You can just change the format of the left column for your need. Or you can use the following to format your dates:

=TEXT([your date],"mmm-yy")

Answer 2

To have that as the actual value, you need to change it into a text string, and also format the cell as text (so Excel won't change it back into a date).

So given your data, doing this in VBA:

For Each c In Range("A1:A10")
With c
    .NumberFormat = "@"
    .Value = Format(c.Offset(0, 1), "mmm-yy")
End With
Next c