VBA incorrectly changing some dates from DD/MM/YYYY to YYYY-DD-MM

Hi all,

I have the macro below which opens csv files stored in a user selected folder and processes them changing the date format in column D from DD/MM/YYYY to text stored as YYYY-MM-DD.

For most of the dates the code works without issue, but for some (those with a month <12 possibly) it transposes the MM and DD incorrectly.

I understand that when opening the CSV's in excel it automatically converts the dates to DD/MM/YYYY, so I'm actually opening in wordpad which displays as YYYY-MM-DD, with only a portion being incorrect.

I've attached a couple of sample files (pre and post conversion). Any help in solving this would be appreciated.

Cheers

David

Please Login or Register  to view this content.

Hello Dave
I import a csv file every day & ran into the same problem you have. The file I import has a "dd/mm/yyyy hh:mm:ss" format day/date column that loses it's proper formatting & goes AWOl to that other format.
I copy the cell contents into a variable formatted as "dd/mm/yyyy hh:mm:ss", then use the Right(blah blah) function to copy the time portion into the adjacent cell, then clear the contents of the source cell, copy the date portion back into the source cell using the DateSerial(year,month,day) function & finally format the source cell to "dd/mm/yyyy"
It works 100% every time
I've attached a workbook with the code for you to experiment with
Cheers
Phil

Thanks Phil,

On investigation I've found the following code from which seems to work for my situation, however I can't seem to incorporate it into my vba:

This works:

Please Login or Register  to view this content.

Would anyone have anu ideas as to why the macro seem to process the files but not save them?

Please Login or Register  to view this content.

Thanks

David

Hi David. I looked at your code & finally (I think) worked out what it's doing. I don't know what the x=x+1 is for, I'm guessing it's for counting how many dates were formatted.
I merged some of your code with some of my code & I think it's doing what you want, including saving it.
Copy the code below into a new module & experiment. It uses your GetFolder function
Good Luck
Phil

Please Login or Register  to view this content.

Hi Phil,

Thanks for modifying the code. I've run it through and it certainly seems to modify and save the first file sucessfully.

However it doesn't cycle through all of the CSV files in the folder, of which there are 20. It stops after the 1st file.

The original code would cycle through and display a message advising that X number of files have been processed.

Thanks

David

Hi Phil,

I'll try posting another message and see if anyone can help further.

Thanks for your assistance so far.

Cheers

David

Hello David. I can't access the site via my computer for some reason,so I'm fighting with my phone.I'll have a look as soon as someone's fixed the reason I can't get on the site, unless someone else helps you first.Its only this site that's giving grief.

Hi Dave
I got my site access back
This code will loop through all the .csv files in a folder you select via the browse box. It uses your "GetFolder" function

Please Login or Register  to view this content.

If you go to the same folder every time you can use this code. Change the "FolderPath" path to suit
It doesn't need the "GetFolder" function

Please Login or Register  to view this content.

Fingers crossed one of them will work for you
Cheers
Phil

Phil,

These are both fantastic. Very much appreciated

Regards

David

No worries Dave
Cheers
Phil