CODEWITHSUNDEEP
excelcsvtimespreadsheet
Joachim Breitner
Joachim Breitner

Reputation: 25782

Best time span (time difference) format for CSV

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.

Upvotes: 3

Views: 778

Answers (2)

Gary's Student
Gary's Student

Reputation: 96791

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

Upvotes: 1

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

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

Upvotes: 0

Related Questions