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.

Answer 1

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

Answer 2

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.