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.

Gary's Student · Answer 1 · 2014-01-25T18:31:50.573

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

edited Jan 25 '14 at 18:31

answered Jan 25 '14 at 14:54

Gary's Student

95,722
10
59
99

The problem with `134.45632176823` is that a human would not be able to make much sense of it. I'm looking for something that my application can generate that Excel will not interpret as just a string -- if that is even possible. – Joachim Breitner Jan 25 '14 at 16:28
If the number of days is less than or equal to 31, then you could use the **Custom Format** : d hh.mm.ss.000 – Gary's Student Jan 25 '14 at 17:23
And Excel detects this format in a CSV file? Note that this question is *not* about formatting inside Excel. – Joachim Breitner Jan 25 '14 at 18:08
Ok, thanks for the clarification. Suboptimal, then, but it might well be that there is no solution for the problem. – Joachim Breitner Jan 25 '14 at 20:32
Well.....except for the problem of "human understanding", the easiest approach is to leave the value without format.....it can be stored and retrieved without error. – Gary's Student Jan 25 '14 at 22:09
What do you mean by without format – I am generating the file myself; I need to write *something*. – Joachim Breitner Jan 25 '14 at 22:10
I mean using a **NumberFormat** like "0.00000000000" – Gary's Student Jan 26 '14 at 00:31

score 0 · Answer 2 · answered Jan 25 '14 at 13:39

0

I tried this in Excel

"y/m/d h:mm:ss.000"

and it returned 07/1/10 10:14:02.016 for the difference between 1/1/2007 00:00:00 and 11/1/2014 10:14:02.016

answered Jan 25 '14 at 13:39

Mark Fitzgerald

3,048
3
24
29

So if I put a string of that format in a CSV file and open it with excel, it is detected as a time difference? I would have expected it to be parsed as a date... – Joachim Breitner Jan 25 '14 at 16:31

Best time span (time difference) format for CSV

2 Answers2