CODEWITHSUNDEEP
excelcsvformatdate
Maxim Veksler
Maxim Veksler

Reputation: 30232

Date format that is guaranteed to be recognized by Excel

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.

Upvotes: 8

Views: 2619

Answers (2)

Steve
Steve

Reputation: 319

If you format your dates as follows in the csv output, Excel will parse the content exactly as a date (other columns for realism only)

43,somestring,="03/03/2003",anotherval
55,anotherstring,="01/02/2004",finalval

so add ="{date}" and it parses as date!

Upvotes: 0

David
David

Reputation: 25470

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

Upvotes: 8

Related Questions