The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Expressing durations in CSV format

Is there a good way of expressing durations eg. 1 hour 25 minutes, 85 hours etc. in CSV format so that it is read easily and correctly in Excel and other applications?

I have resorted to writing in both d.hh.mm.ss format for readabilty, and total number of minutes as an easy value to work with in programs (minutes are the smallest measure in my program).

Is there any better way?
Andrew Rowley Send private email
Sunday, June 18, 2006
 
 
The problem with writing a 'date' in a CSV, and then having Excel convert it into an Excel-ish 'date' column, is that Excel converts that into a "number of seconds since Jan 1, 1990", and then displays that value in whatever the default date format you've told Excel about.

The result being, your original date formatting from the CSV file can be lost.  Now, if that's not important to you, and you can get a date format into Excel that you're happy with, then that's not a problem.

Personally, I find myself telling Excel that my 'date' column should be formatted as a 'text' field, so that dates continue to look to me as they were input.  This means I can't have Excel do math with those dates, but then I didn't WANT Excel to do math with those dates in this particular application.

Otherwise, Excel tends to be pretty flexible with recieving date input and converting it to the internal representation.  "2000/04/05 12:37:42" will be recognized as a 'date' field by Excel, and converted properly, for instance. (where that's "YYYY/MM/DD hh:mm:ss")
AllanL5
Monday, June 19, 2006
 
 
And now I see I've missed your point entirely -- you don't want "dates" per se, you want DURATIONS -- times between dates.

I did my own formatting once, putting an 'h' after an hours value, or an 'm' after a 'minutes' value, then writing my own code to properly interpret the values (this was for a scheduling application).

As I remember, Excel likes 'absolute' dates -- time since Jan 1, 1990.  Giving it a 'delta time' -- well, that's up to the programmer to realize that Excel's displayed "2004/01/01 00:00:01" is REALLY a '1 second' delta value.
AllanL5
Monday, June 19, 2006
 
 
I wouldn't take any suggestion offered by AllanL5 - this tool, IMO is utterly clueless about things.

Views represented by AllanL5 are myopic, limited, and without merit.
Brice Richard Send private email
Monday, June 19, 2006
 
 
> total number of minutes

This is the correct answer. Just use whatever your smallest unit is. Abstractions are for human convenience, only.
Unix Guy
Monday, June 19, 2006
 
 
Yes, I am using durations, prefereably not linked at all to dates eg. worked on x for 3 hours 20 minutes this week.
23:30 + 2:40 needs to give 26:10
Reducing it all to minutes was the best way I could find to make it workable and reasonably foolproof. I was just wondering whether there was anything obvious that I missed.
It seems like a big oversight in Excel that it can't deal with this type of time format properly.
Andrew Rowley Send private email
Monday, June 19, 2006
 
 
You could store the start and end Date/Time and then calc the duration as needed?
Honu Send private email
Tuesday, June 20, 2006
 
 
AllanL5,

I believe you'll find that Excel stores times as decimals representing the number of seconds passed since midnight, not since 'Jan 1, 1990'. Thus, 12 midday is represented by 0.5 and 6PM is represented by 0.75. Dates on the other hand are represented by underlying integers where one represents #01/01/1900#).

When you have a date and time entry, everything to the left of the decimal represents the date, while everything to the right represents the time. Hence you can perform both date and/or time calculations.

Andrew,

>"It seems like a big oversight in Excel that it can't deal with this type of time format properly."<

Excel is quite capable of storing dates and/or times and performing date and/or time calculations. Yes, it can have some interpretation issues when receiving dates/times from external sources such as CSV files.

If you know that you'll be importing the CSV into Excel you can store each time component (H, M and S) in separate columns. Excel has a TIME function which takes the number of hours, minutes and seconds as arguments. The return value is a valid Excel time. So in your example above the formula =TIME(A2,B2,0) would return 1:25 AM (assuming A2 had the hour part and B2 the minute).

If your time durations exceed 24 hours (16:00 + 18:00), Excel will automatically reset the clock at 24:00 and provide an answer of 10:00. To tell Excel that you explicitly want to have durations in excess of 24:00 hours change the number format to this: [h]:mm
The [] around the hour, tells Excel to calculate beyond a 24 hour day.

Cheers
Marcus from Melbourne
Tuesday, June 20, 2006
 
 
>Excel is quite capable of storing dates and/or times and performing date and/or time calculations. Yes, it can have some interpretation issues when receiving dates/times from external sources such as CSV files.<

But durations are *not* date/times. If it takes me 13 hours to travel 500km, how long does it take to travel 1000km - the answer is not 2 am tomorrow.

I don't actually need to do these calculations in Excel myself. I just want to export duration data from my program in a format that is most useful to my users, and least likely to give problems like durations being reinterpreted as date/times.

The destination may not even be Excel - it may be Access or any other program that imports CSV. Excel is just the most likely.

I think the answer is still to provide the number of minutes - a simple number that any program can work with.
Andrew Rowley Send private email
Tuesday, June 20, 2006
 
 
I might be missing something, but I'd be most likely to provide durations in seconds. This will provide the most useful degree of granularity.

Those wishing to work with the data will thank you for not forcing them to convert to seconds before performing any calculations.
Gavin Laking Send private email
Sunday, June 25, 2006
 
 
I don't measure in seconds - minutes is the limit of accuracy. I don't see why people would need to convert to seconds - I would expect it would be more likely that they would want to go the other way, to hours.
Andrew Rowley Send private email
Monday, June 26, 2006
 
 
"To tell Excel that you explicitly want to have durations in excess of 24:00 hours change the number format to this: [h]:mm
The [] around the hour, tells Excel to calculate beyond a 24 hour day."

Cheers marcus from melbourne - that worked a treat!
Phil Send private email
Saturday, July 15, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz