Date format that is guaranteed to be recognized by Excel

Question

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.

score 8 · Accepted Answer · answered Apr 07 '10 at 11:24

8

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

answered Apr 07 '10 at 11:24

David

24,700
8
63
83

1

Simple and true, ISO is what I'm after. Thanks. – Maxim Veksler Apr 07 '10 at 12:15

score 0 · Answer 2 · answered Feb 07 '12 at 18:42

0

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!

answered Feb 07 '12 at 18:42

Steve

319
1
4

isn't this locale sensitive as well? – crashmstr Feb 07 '12 at 18:49
yes this must be a locale sensitive format, good catch. The selected answer is correct using ISO formatting yyyy-mm-dd. – Steve Apr 27 '14 at 22:20

Date format that is guaranteed to be recognized by Excel

2 Answers2

Linked