11

Is there a date-time format for CSV that's recognized by Excel, Chrome, and Firefox (and ideally more broadly) and valid across locales in US and EU?

Excel can read CSV with ISO Dates, but does not recognize the ISO Date Time values in the first column:

Here's an example CSV:

"datetime"            
"1985-07-17T09:12Z"   
"1985-07-17 09:12"    
"1985-07-17"          
"12/10/2010 9:12",
"10-Dec-2010 09:11",
"Dec 10, 2010 09:11"
  • "1985-07-17T09:12Z" parses in Chrome and Firefox but not Excel
  • "1985-07-17 09:12" parses in Chrome and Excel but not Firefox
  • "1985-07-17" parses in Chrome, Excel and Firefox but loses the time
  • "12/10/2010 09:12" parses in Chrome, Excel and Firefox but assumes US Locale
  • "10-Dec-2010 09:11" does not parse in Firefox
  • "Dec 10, 2010 09:11" parses in Excel, Chrome and Firefox ... but US only??

Chrome can read lots of locale-specific date time formats, but Firefox is pickier.

Update: Added a more complete example

Question: Does "Dec 10, 2010 09:11" parse for me in all three only because I'm in the US, or would this work more generally?

Community
  • 1
  • 1
prototype
  • 7,249
  • 15
  • 60
  • 94
  • What do you mean by "Chrome/Firefox parses (or not) chosen format"? How do you load CSV in browsers? – BrakNicku Feb 13 '16 at 17:11
  • I was simply executing `new Date("1985-07-17T09:12Z")` in browser http://codepen.io/gradualstudent/pen/GowMEw – prototype Feb 14 '16 at 22:16
  • 1
    In this case you should try `"1985/07/17 09:12"`, it works well in both browsers and Excel in my (non-US) environment. `"Dec 10, 2010 09:11"` doesn't work in Excel for me. – BrakNicku Feb 15 '16 at 16:19
  • @BrakNicku Aha! Interestingly in browsers it's interpreted as local time and converted to GMT (UTC+0:00) `"1985-07-17T13:12:00.000Z"` which is awesome. Excel reads and displays it as local time `7/17/1985 9:12:00 AM` and I'm not sure if it has a time zone in mind. It has the year-month-day order which seems like it work in different locales. Please post as an answer. Thanks! – prototype Feb 16 '16 at 01:11

1 Answers1

6

The following format works in Chrome, Firefox, IE, Safari, Edge (parsed with javascript new Date()). Excel also parses this format (at least in my locale with default YYYY-MM-DD date format):

YYYY/MM/DD HH:mm

An example of a timestamp in this format is:

1985/07/17 09:12

swrobel
  • 4,053
  • 2
  • 33
  • 42
BrakNicku
  • 5,935
  • 3
  • 24
  • 38