Click here to Skip to main content
14,640,737 members
Rate this:
Please Sign up or sign in to 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

Rate this:
Please Sign up or sign in to vote.

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

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100