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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,479
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.
 

Forum statistics

Threads
1,081,976
Messages
5,362,513
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top