CSV date format

Question

I have a VB application which extracts data and creates 3 CSV files (a.csv, b.csv, c.csv). Then I use another Excel spreadsheet (import.xls) to import all the data from the above CSV files into this sheet.

import.xls file has a macro which opens the CSV files one by one and copies the data. The problem I am facing is the dates in the CSV files are stored as mm/dd/yyyy and this is copied as is to the Excel sheet. But I want the date in dd/mm/yyy format.

When I open any of the CSV files manually the dates are displayed in the correct format (mm/dd/yyyy). Any idea how I can solve this issue?

Answer 1

When I run into this problem I usually write out the dates as yyyy-mm-dd which Excel will interpret unambiguously.

Answer 2

You can use the Format VBA function:

Format(DateText, "dd/mm/yyyy")

That will format it how ever you like.

For a more permanant solution, try changing your regional settings in windows itself, Excel uses this for its date formatting.

Start -> Settings -> Control Panel -> Regional Options.

Make sure that the language is set to whatever is appropriate and that the date settings are as you want them to be