Macro to open CSV file, and reformat date field into text and save as YYYY-MM-DD

[DaveBre]

Hi all,

I have a macro which clycles through a folder of CSV files making changes (normally deleting rows of columns).

I'd like to adopt it instead to reformatting in place (if possible), or copying/paste special with a formula similiar to =TEXT(D2,"YYYY-MM-DD) which works outside of VBA.

The dates to be reformatted will always be in column D (starting at D2), however the number of rows will vary.

From past experience I know that CSV files don't interpret dates as text and after the change is make should it be re-opened it will display as per my regional settings, but the next person to view will be doing so via a text editor and the change should be sufficient for the formatting to stick.

The following code is about as far as I've managed to get, could anyone add anything to it?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[AlphaFrog]

You couldn't put a formula in the same cell as the date value. So if column D has the dates, you couldn't have the TEXT formula in column D as well.

If you change the display format of the dates, then resave the CSV file, it will use the displayed date format as the saved text.

Use this to set the date format of all dates in column D.

Range("D:D").NumberFormat = "YYYY-MM-DD"

[DaveBre]

Hi Alphafrog,

I've tried the code suggested but it seems to mix the dates and months around, or not touch them at all.

The 02/05/2014 becomes 2014-02-05

The 09/05/2014 becomes 2014-09-05

The 16/05/2014 remains the same

The 23/05/2014 remains the same.

Any idea why?

T

[AlphaFrog]

Hi Alphafrog,

I've tried the code suggested but it seems to mix the dates and months around, or not touch them at all.

The 02/05/2014 becomes 2014-02-05

The 09/05/2014 becomes 2014-09-05

The 16/05/2014 remains the same

The 23/05/2014 remains the same.

Any idea why?

T

I have a notion.

The original CSV files have dates in the format of dd/mm/yyyy.

Your computer's regional setting is currently English(United States) and not English(Australia)

When you open the CSV file, Excel using English(United States) will wrongly convert some of the dates as mm/dd/yyyy when the day as less than or equal to 12 in the CSV file. Those days can wrongly be converted to months because the day is 12 or less. When the day is greater than 12, the text-date remains as text and not converted to a serial-date. The text-dates are not affected by the .NumberFormat property. Only the wrongly converted serial dates would be formatted.


Check your computer's regional setting for English(Australia). Change it if necessary and reopen the CSV file. That should fix it if my assumption is correct.