15,943,245 members
3.00/5 (1 vote)
See more:
Everything I've seen on this subject pertains to clock time, as in what would the time be if I added 30 minutes and 29 seconds to 3 PM? But I've yet to see a question or answer that addresses my particular requirement.

Here it is. I'm trying to use Excel to add up and total the timings of tracks on a CD. So, I enter the time of each track, like 5:23, 4:08, 6:45, and so on, each in its own row in row B. Row A contains the name of the song on the track.

I'm not interested in getting an AM or PM clock time result. I want the total time of the CD from accumulating all of the tracks. Obviously, the result of the example I've given should come out to 16:16 (16 minutes and 16 seconds) because the minutes add up to 15, but the seconds add up to 76, which would add one more minute to the minutes (76 - 60 = 16) making 16 minutes and leaving 16 seconds.

There are Internet calculators that do this, but I haven't found any way in Excel to do it, and I haven't found anyone who has even asked this question.
Posted

Solution 1

I just tried it: as I expected, it works in Excel without any "calculators".
If works if you simply add time using strings like "dd:dd". The sum is calculated correctly, but if you look at the cell type, it's not "mm:ss" (minutes and seconds), but "hh:mm" (hours and minutes). What's the difference? not important if you stay within 60 hours, calculations look just the same. :-)

No, no. Of course this is not correct. Do simple thing: activate context menu for each cell and setup the following formats all cells involved in duration calculation: "Format Cell..." => [Tab] "Number" => Category: "Custom" => "Type". Type in the "Type" line desired format: "mm:ss", "mm:ss.000", "hh:mm:ss" or "hh:mm:ss.000". Note: you don't have to select a type from the list of available types, just write the one you need. For the summary cell, use the function `SUM(range_from:range_to)` or just operator "`+`".

You will get correct results. Second or minutes will carried in higher-order digit using the base of 60, milliseconds (if you want to use them) — 1000, etc., all like you can expect.

—SA

v4