Click here to Skip to main content
15,943,245 members
Please Sign up or sign in to vote.
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

1 solution

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
 
Share this answer
 
v4

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900