excel vba change format of date - numbers

Question

Excel changes the following values automatically to a number, I guess because he considers them as a date:

2/2/1 becomes 36527 4/2/1 becomes 36926

I have a column with a combination of different formats now:

2/1/
3/1/
8/7/
36527
1/0/0
36926

Which VBA code can I use to convert the numbers back to their original format? The other values should stay the same.

I know the cDate function, but I guess it's not useful here?

I have already this in my VBA code for pasting values

ActiveWorkbook.Sheets("Import").Columns("A:AH").NumberFormat = "@"
ActiveWorkbook.Sheets("Import").Range("A1").Select
ActiveSheet.Paste

before importing or inputting the values, change the cells to text. Excel will then not change them to dates. — Scott Craner
– Scott Craner, Commented Mar 18, 2020 at 15:34

Dick Kusleika · Accepted Answer · 2020-03-18 15:54:49Z

1

You can't change them back. Once Excel converts them, the original value is gone. Before you input the value, you can prepend an apostrophe to force it to text.

ActiveCell.Value = "'" & sMyValue

or as @Scott Craner commented, you can format the cell as text

ActiveCell.NumberFormat = "@"
ActiveCell.Value = sMyValue

answered Mar 18, 2020 at 15:54

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1987607 Over a year ago

I had already this in my VBA code ActiveWorkbook.Sheets("Import").Columns("A:AH").NumberFormat = "@" ActiveWorkbook.Sheets("Import").Range("A1").Select ActiveSheet.Paste

Dick Kusleika Over a year ago

If you paste, it will overwrite the formatting. It's best to set the Value property rather than paste. But if the value you need is only in the clipboard, then paste special so you don't overwrite the formatting.

Collectives™ on Stack Overflow

excel vba change format of date - numbers

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related