Convert Column Dates to Text VBA

Skybluekid

Well-known Member
Joined
Apr 17, 2012
Messages
828
Hi All,

I have in column D a list of dates:

43728
43710.35417
43732
43689.62014
43698.44236
43727
43708.5
43699

<colgroup><col></colgroup><tbody>
</tbody>

I would like to convert these dates to Text and then format them as dd/mm/yyyy. I hear you cry "this is easy and why are you using VBA???"

Well this is part of a wider chain that copies data from on sheet to a Template. Once this template is complete, it is then exported to a CSV file to be uploaded.

I have used some code from VoG:

Set ColumnFormat = Range("D3:D" & Count)


For Each c In ColumnFormat
c.NumberFormat = "@"
c.Value = Format(c.Value, "dd/mm/yyyy")
Next c

Which does the trick, except when the file has been exported to CSV, it then reverts back to the date number format, as above rather than text. To Transfer the values to Template, I am copying the column and then pasting values

In the Template the Values have to be in the dd/mm/yyyy text format, that can not change.

I would very much welcome some pointers.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,094
Office Version
365
Platform
Windows
Which does the trick, except when the file has been exported to CSV, it then reverts back to the date number format, as above rather than text. To Transfer the values to Template, I am copying the column and then pasting values
Are you sure? Where are you seeing this?

I see this happen quite a bit. What they do not understand is that opening a CSV file directly in Excel re-converts the values. And then if you re-save the file, you save those conversions.
Hence, if you want to see what is REALLY in a CSV file, you should NEVER use Excel to view it, as Excel does not simply open it, it also may convert the values.
If you really want to see what is in a CSV file after creating it, you should use a Text Editor (like NotePad, WordPad, or any 3rd party Text Editor) to view it.

One of my biggest pet peeves with Microsoft is that they decided that Excel should be the default program to open CSV files. That was a horrible decision that can lead to confusion and issues, since it does whatever field conversions it deems necessary. I always edit that and change the setting to use a Text Editor as the default program to view CSV files.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,253
Messages
5,449,262
Members
405,560
Latest member
Jadax

This Week's Hot Topics

Top