Best time span (time difference) format for CSV

Question

This question is very analogous to Best timestamp format for CSV/Excel?:

I'm writing a CSV file. I need to write time spans (a.k.a. time differences) 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 programs like Excel with minimal user intervention?

Preferably in a format that is still easily humanly parseable even if it spans several days, i.e. instead of 265:00:00 something where a human can spot that it is 11 days without doing calculations in his head.

I should add that I am generating the CSV outside of Excel, so this is not about getting data out of Excel, or formatting data in Excel, but how to format a plain CSV file so that both Excel and Humans can make sense of time span values.

Answer 1

For any arbitrary number of days, the problem is not creating the format, its reading the .csv back into Excel once created. For example if a cell contains:

134.45632176823

its value as days/time is:

134 days, 10 hours, 57 minutes, 6 seconds, 201 milliseconds

Even if you could format the cell to display:

134 10:57:06.201

and then saved the file as .csv,, upon re-opening the file, Excel would interpret the value as a string rather than a days/time (a human would have no problem interpreting the format)

EDIT#1:

if you enter 1.23456789 if a cell and format it as d hh:mm:ss.000 it will display as:

1 05:37:46.666

If you then save the file as .csv and open the .csv with NotePad, you will see the as-formatted text. If, however you re-open the .csv with Excel, you will find a text cell rather than numerical cell formatted as days/time.

Excel does not have this problem with a cell containing a standard date like 1/25/2014