16

I have a script which outputs a CSV file. How should I format dates or date+time fields so that Excel will correctly parse that field as a date?

PS: I'd like to keep timezone information intact as well.

nickf
  • 537,072
  • 198
  • 649
  • 721
  • CSV is not a very well defined format, and, in international environments, their interpretation depends on the regional settings of the pc (dmy vs mdy).Even the comma can be replaced by a semicolon. I avoid that "non format" whenever possible. – iDevlop May 10 '10 at 07:11
  • I've never seen "the comma replaced by a semicolon" in a file claiming to be CSV. In fact, I can't remember the last time I saw a CSV file that didn't follow RFC 4180 (). The only question is how to represent rich objects like datetimes. – Ken May 10 '10 at 19:13

2 Answers2

5

Excel's date format does not contain any information about time zones. I would export the time zone as a separate column, perhaps as an offset from GMT, like 5 or -2. I personally prefer the unambiguous yyyy-mm-dd date format.

dendarii
  • 2,958
  • 20
  • 15
  • +5 or -2 to Greenwich Mean Time (GMT), so for example, Eastern Standard Time would be -5 to GMT, whereas Paris would be +1. – dendarii May 10 '10 at 11:54
  • well that's what I mean, how would anyone know? – nickf May 10 '10 at 23:09
  • Since in your question you say you would like to keep timezone information intact, I assume this information exists in your source data. In what format is your timezone data? – dendarii May 11 '10 at 09:25
-3

I would use the "dd/mm/yyyy hh:nn:ss" format. I have never had any problems with Excel understanding this format before.

Craig T
  • 2,761
  • 5
  • 25
  • 33
  • Timezone would have to be added as a separate column. – Craig T May 10 '10 at 22:33
  • 2
    Is this the same as the "dd/MM/yyyy hh:mm:ss" format? Excel doesn't read this format correctly from CSV files. It reads the dates only in American format (mm/dd/yyyy), regardless of your regional settings. It's a bug in Excel as far as I can see. – SDK Dec 18 '12 at 15:41
  • @SDK That is not correct. At least for the Mac version ... I just tried importing the following csv file "11/01/2013 13:00:00"; 10 and excel parsed it as 11th of January ... – oligofren Nov 17 '13 at 22:45
  • 3
    Every time you choose a bad date format, Jesus harpoons a baby whale. – Sam Watkins Apr 07 '14 at 03:40