Adding times and totalling in Excel 97

Highlight all your cells that you want formatted.  Pull down your Format menu.  Choose Cells.  Choose the Number tab.  Choose Time.  Select one of the options that does not have an AM/PM designation.  Make sure to enter in all of your time values with a colon.  Then add the column as you would any other column.  It will total the range and express it in a time value.

Thanks for the formatting help. I was hoping you'd say how to add as well. Never had to use it for that before, so I would appreciate the formula very much.

 

The SUM function will work until the total time exceeds 24 hours. To correctly add your times and display the SUM as total hours and minutes, format the cell that contains the SUM function as

 

[hh]:mm

 

If you're just looking to show total minutes (and not have a total that shows hours) change the format to

 

mm:ss

 

in the cell where the SUM function is used.

Thanks. Already tried that.

What I'm trying to add up is track times, song lengths. Always in minutes and seconds, so I'm just looking for a 000:00 sum for a result.

I've tried formatting the cells as both "number" and "custom" (that's where the mm:ss category is), but the results I've been getting fall short of the actual time when added manually.

Must I enter the times as something other than 00:00? Perhaps I should enter and then paste special as formulas or something? I've tried a few combinations with little success. This is Excel 97, by the way.

 

Thanks for your reply. I'm probably closer to getting what I need now but still not quite there yet.

I formatted my column as "number", then "custom" (mm:ss). The times I have entered as 10:04, for example, suddenly change to 04:00. Perhaps this is a coding format, but I'd like to just show the actual times and add the minutes and seconds to get total minutes and seconds.

Must I first copy and paste as values or formats or add/subtract, etc.?

Could be gremlins . . .

The simple fact of the matter is that excel is not very good at keeping track of durations of time.  It is quite good at keeping track of points in time (today at 10:40 AM or yesterday at 5:16 PM, for example), but it is not good at durations.  My advice to you would be to convert all times to seconds (if that is too hard, have separate columns for minutes and seconds in the duration, then have a third column that is basically minutes*60 + seconds to convert the whole duration into seconds).  Now add up the third column, then divide by 60 to break it back into minutes and seconds.  If the minutes are more than 60, you can again divide by 60 to get hours, minutes and seconds.