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.

mm:ss durations in MS excel

OK, I know this is an old problem, but I can't seem to find a reasonable solution.  I have a series of start times and end times from a video.  They are expressed as mm:ss.  Excel forces me to think time is a function of dates (which it is not) and choose a date format.  At first, this worked alright and allowed me to calculate the differences between one coder's start time and another coder's start time (same people coding a video.  I'm looking at calculating the overlap among segments of video each person coded).  I saved that document over a month ago and returned to use it today.  After working with the data for 1/2 hour and creating some charts, MS Excel suddenly deciced that negative times were going to be displayed as ######.  This obviously does not work for me as I need to see negative as well as positive times. 

I've tried to use a custom [h]:mm:ss value to format my numbers but haven't had any luck.  I've also played around with all sorts of different time formats, but I can't get my negative data to show up again.  Any ideas on how to get Excel to actually allow a [h]:mm:ss format that is then calculable?
Peter Rich Send private email
Wednesday, April 04, 2007
 
 
I usually end up working in seconds and then calculating and displaying min:ss in separate cells.
Martin Send private email
Wednesday, April 04, 2007
 
 
A quick Google search shows many solutions to the problem, most involving switching to the 1904 date system:
http://www.google.com/search?hl=en&q=excel+negative+time

The alternative is to convert the time to text manually.

=IF(C1<0,"-"&TEXT(-C1,"[h]:mm:ss"),TEXT(C1,"[h]:mm:ss"))
Mark Ransom Send private email
Wednesday, April 04, 2007
 
 
###### just means the formatted data can't fit in the current column width - try increasing the column width...
Bob
Wednesday, April 04, 2007
 
 
If you format the cells to Number with several decimal places, you'll see that the times are actually plain ordinary fractions. If you do a little bit of math, you'll discover that the fraction in question is days. That is, 0.5 is 1/2 day == 12:00 noon. If the value is >= 1, then you have the number of days since the 'beginning of time' where that normally means (or used to mean) since Jan 1, 1900.

Excel does display negative dates and times as "#####", so you'll have to do your own thing to calculate and format to your liking.
Ron Porter Send private email
Wednesday, April 04, 2007
 
 
My bad - Excel does indeed display negative dates/times as #######

Like others have said you will have to come up with a custom calculation and formatting
Bob
Wednesday, April 04, 2007
 
 
What I used to tell people, way back when I wsa doing Excel support, was:

No, Excel does *NOT* do elapsed times. It just plain doesn't. Not now, won't ever. Give up. Do your work in seconds, and display the results with

=INT(Value/3600)&":"&RIGHT("00"&MOD(INT(Value/60),60),2)&":"&RIGHT("00"&MOD(Value, 60),2)

Man, that's scary. It's been so long since I've fired Excel up, I forgot what it uses for string concatenation.
mjfgates Send private email
Thursday, April 05, 2007
 
 

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

Other recent topics Other recent topics
 
Powered by FogBugz