Convert Column Dates to Text VBA

Skybluekid

Well-known Member
Joined
Apr 17, 2012
Messages
1,224
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top