CSV date format [VBA]

Question

Hello

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

Answer 3

First of all the other answers are all good but theres some more information you might find helpful

A CSV file only contains text. That is the data is not in date format but in a text format. So when you open a CSV file in Excel, Excel by default interprets that data for you. It doesn't have to. You could force it to leave it as text, Or as mentioned by Mark you can add code into your import macro that alters it for you. If you want an automated process then this is the best solution. Simply add the code in VBA to the macro that applies the required date format to the column with the date data. Alternatively you could do this manually after the file is open and the data has been pasted by selecting the column yourself and chancing the format. You can customise number formats (choose custom) and then write it up yourself. Eg dd/mm/yyyy.

Answer 4

Hi All,

I had a similar problem and solved it like this, essentially it is the 'all of the above' option

1. With format(date,'dd-mmm-yyyy') convert the date to the 2-3-4 format e.g.01-aug-2008, no way a computer mistakes 01-aug for 08-jan :-)
2. Since copying this in excel automatically reverts it back to the date/time format, convert the result to a string by using cstr on the function in 1
3. Format the cells in which the date is copied as text (if you don't, thickheaded as excel is, it will convert it back to date / time again
4. Save the sheet as a csv file and open in notepad to confirm it has worked

Some people may say it's overkill, but better be on the safe side, than having a user call me a year from now and telling me it's doing something strange with the date's