Date value changes when copied

1
votes

I have cells containing a simple concatenation function with D24 as the previous year (e.g. 15) and a custom format (MMM JJ)

CONCATENATE("Apr ",$D$24)

When I am copying and pasting these cells with a VBA then "Apr 15" becomes "15.04.16" and because of the formatting "Apr 16"

Selection.Value = Selection.Value

Was is the reason behind this? Is there another solution than just changing the format to text?

3
excelvba

3 Answers

2
votes

Excel will generally try to convert anything that looks like a date into a real date (serial number where 1 = 1 Jan 1900). One way to avoid that, and remove the formula as you are doing above, would be to pre-format as text. So:

With Selection
    .NumberFormat = "@"
    .Value = .Text
End With

might do what you want.

There are other ways, but if you don't change the cell format to text, or prefix the entry with a single quote ', any subsequent editing of that cell, even inadvertent selection, raises the risk of converting it to the real date.

2
votes

That depends on what you want in your cell. Dou you want a string or a date?

If you want a string:

  • either format as text or

  • add a ': CONCATENATE("'Apr ",$D$24)

if you want a date:

  • use the following formula instead of concatenate: =DATE($D$24,4,1)
0
votes

If you simply Copy Paste it, only the Value is pasted not the formatting (if I remember right)

Try to avoid using Selection instead use Range.

And use Range.Copy and Range.PasteSpecial Paste:=xlPasteFormats so your formatting is pasted with the values.