Date formats

[Neil_wylie]

Hi Everyone,

I am capturing data from a csv file using VBA, and using it to populate an Excel 2003 workbook. The first column in the csv file contains dates, and these are being partially converted to American format. If the date is 06/10/15, it comes across as 10/06/15. On the other hand, if the date is 13/10/15, it stays the same. Does anyone know how to sort this?

[myou]

What Date Format would you prefer in the sheet?

[InvisibleMan]

Select the column of dates and then use Data | Text to Columns. On the third step of the wizard, click on the date option and choose the correct format, for example, DMY. Click Finish. Done.

[Neil_wylie]

I would like the format dd/mm/yy. The main issue is that the date come across directly for any date after the 12th. i.e. Excel examines the whole field, and determines the format according to the cell value.

[InvisibleMan]

Did you try what I suggested?

[Neil_wylie]

Hi Invisible Man,

Thanks for the reply. It doesn't affect the issue. I'll try to be more specific:
I have a file in csv format, and the first column "A" holds date values in the format dd/mm/yyyy.
When I open the file using Excel 2003 all values appear without alteration.
When I open the file using VBA and the "Workbooks.open" method, then:
If the date <= 12th of the month, then the day and month are transposed i.e. the date 11/06/2015 becomes 06/11/2015. Otherwise.the date comes across as is.

In my view, the problem lies with the workbooks.open method. An alternative might be to treat the input file as a text file, but this brings its own problems that I won't bore you with.

[InvisibleMan]

Just record a macro to use Text to columns as described. Then run that macro, or embed it into your existing code, as soon as the file is opened.