Adding time durations

Question

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 12-Oct-11 10:08am

jldmen

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sergey Alexandrovich Kryukov · Answer 1 · 2011-10-12T13:14:00

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