Date format that is guaranteed to be recognized by Excel

Question

Hello,

We're exporting our analytics reports in various formats, among them CSV. For some clients this CSV finds it's way into Excel.

Inside the CSV file one of the columns is a Date, for example

"Start Date","Name"
"07-04-2010", "Maxim"

Excel has trouble parsing this date format, obviously depending on the Locale of the user. Is "07" is the day or the month...

Could you recommend some textual format for a Date field that excel will not have trouble parsing? I'm aiming at the most fail safe option possible. I would settle for some escape sequence that will cause excel to avoid parsing the text in the column altogether.

Thanks for helping, Maxim.

Answer 1

You have two options. Go with the month as a string and the year as 4 digits, or use ISO formatting: yyyy-mm-dd.