How to format a datetime so that Excel will understand it?

Question

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.

Solution

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.

Comments

[dendarii]

+5 or -2 to Greenwich Mean Time (GMT), so for example, Eastern Standard Time would be -5 to GMT, whereas Paris would be +1.

[Craig T]

Timezone would have to be added as a separate column.

[nickf]

well that's what I mean, how would anyone know?

[dendarii]

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?

[SDK]

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.

[oligofren]

@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 ...

[Sam Watkins]

Every time you choose a bad date format, Jesus harpoons a baby whale.