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. 
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?
The problem with writing a 'date' in a CSV, and then having Excel convert it into an Excelish '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
> 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.
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.
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.
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.

Powered by FogBugz