Best timestamp format for CSV/Excel?

Question

I'm writing a CSV file. I need to write timestamps that are accurate at least to the second, and preferably to the millisecond. What's the best format for timestamps in a CSV file such that they can be parsed accurately and unambiguously by Excel with minimal user intervention?

Answer 1

I believe if you used the double data type, the re-calculation in Excel would work just fine.

Answer 2

I would guess that ISO-format is a good idea. (Wikipedia article, also with time info)

Answer 3

For second accuracy, yyyy-MM-dd HH:mm:ss should do the trick.

I believe Excel is not very good with fractions of a second (loses them when interacting with COM object IIRC - I'll try to find a reference and post it here).

Answer 4

The earlier suggestion to use "yyyy-MM-dd HH:mm:ss" is fine, though I believe Excel has much finer time resolution than that. I find this post rather credible (follow the thread and you'll see lots of arithmetic and experimenting with Excel), and if it's correct, you'll have your milliseconds. You can just tack on decimal places at the end, i.e. "yyyy-mm-dd hh:mm:ss.000".

You should be aware that Excel may not necessarily format the data (without human intervention) in such a way that you will see all of that precision. On my computer at work, when I set up a CSV with "yyyy-mm-dd hh:mm:ss.000" data (by hand using Notepad), I get "mm:ss.0" in the cell and "m/d/yyyy  hh:mm:ss AM/PM" in the formula bar.

For maximum information[1] conveyed in the cells without human intervention, you may want to split up your timestamp into a date portion and a time portion, with the time portion only to the second. It looks to me like Excel wants to give you at most three visible "levels" (where fractions of a second are their own level) in any given cell, and you want seven: years, months, days, hours, minutes, seconds, and fractions of a second.

Or, if you don't need the timestamp to be human-readable but you want it to be as accurate as possible, you might prefer just to store a big number (internally, Excel is just using the number of days, including fractional days, since an "epoch" date).

---

[1]That is, numeric information. If you want to see as much information as possible but don't care about doing calculations with it, you could make up some format which Excel will definitely parse as a string, and thus leave alone; e.g. "yyyymmdd.hhmmss.000".

Answer 5

I have a similar problem, but unfortunately I can't edit the format of the csv file. I need to review the csv file, and make a few minor modifications then resave the file, either as csv or unicode txt. For both formats excel loses the seconds in the revise file.

Is there a way of force excel to record the full time including the second time.

Any help will be gratefully received