VBA to convert text dates to integer dates for entire column

[benjhardie]

Hello!

I have a CSV file and in Col. C and F are dates (however some cells are blank).

Sometimes when I open the file, some of these dates are formatted as text (left aligned) and not dates (right aligned). Changing the format of these cells to 'Date' doesn't fix the problem - the cells remain as they are. This means that my other code will not work.

So, I have created a macro to loop through the data set and convert any of these text dates to integer dates;

Please Login or Register  to view this content.

However, this code takes a while to run.

Is there a quicker way to do this - or does anyone know why I'm getting this problem when I open my CSV file?

I have attached the file so you can see what I'm talking about.

Any help would be much appreciated!

Thank you!

[benjhardie]

Ignore this comment - I solved this part.

I've also just noticed that this method switches the dates to US style (e.g. mm/dd/yy) and I need them in UK style (e.g. dd/mm/yy).

Does anyone know how to do this?

[benjhardie]

I've actually discovered the real problem here. For some strange reason, sometimes when I open the CSV file, the days and months are swapped around (e.g 08/02/2019 is changed to 02/08/2019). However, this obviously isn't logically possible for all dates and so they are left as a text string (e.g 25/02/2019 can't be changed to 02/25/2019 as there is no 25th month - so this is left as text). I have no idea why this is happening.

[Norie]

How are you opening the CSV file?

[jindon]

benjhardie

Don't open csv by just clicking the file icon.

See
https://support.office.com/en-us/art...b-339e391393ba

You can specify data type column by column.

[benjhardie]

Thank you so much for the help! I have created some code to open the CSV using the Import Text Wizard and it is working nicely