NUMBERS: How to sum a list of time durations to get a total? I'd working with a sound track and need to manage the segments.

If they really are imported as durations, then you can just sum them:

Can you post a screenshot of where they come from, how you are importing them, and what the they look like in the cells?

My guess is that they are not (yet) in a format that Numbers recognizes as durations.

SG

Gene,

Note that SG used the 10h 2m 20s 010ms format. This is the most reliable, especially for direct entry.

If your data format isn't recognized as Duration, and if TIMEVALUE works for you, that's your best bet.

Jerry

Data relieved in mm:ss:## format.

Could you give an example or two of the 'mm:ss:##' format? What exactly does it look like?

If you can give specifics either a formula or a short script should be able to convert it to a form that Numbers can treat it as a duration. But knowing the specifics is important.

SG

Well, glad you've found a "workaround"... whatever it was.

BTW, your format has been a bit of a moving target:

first: MM:SS:10th

then: mm:ss:##

And now: 1:32:20

Looks as if there are not always two digits for minutes after all.

Anyway, Numbers and other spreadsheets can of course easily handle this kind of problem, if the problem is defined precisely enough.

SG

Boy, this one had me stumped. Using Numbers 3.5.3 (2150) on Mac OS X 10.10.5.

For some reason, when I used the above techniques, and formatted my summation cells as a Duration, Numbers kept reformatting it back to a Date and Time. I would get an error: Values being summed by SUM must be either all durations or all numbers, unless one value is a date.

I had set up a sum of a column in which the difference between two times (G5 - Event End and G4 - Event Begin) gave me the duration in the column (B4 and below), and I wanted to sum the column to tell the total hours in the column. Apparently, some of the columns were zero, rather than durations, because the time fields were blank, in the rows where I didn't have time entries yet. (My previous time difference formula had been = H4 - G4)

Each cell in my column of duration difference cells contained the formula: = IF ( ISBLANK (G4), "" , H4 - G4). This places nothing in the cells where there has been no event thus far.

My summation formula is simply =SUM ( B4 : B24 ). =SUM(B) doesn't work because I have text headers. I hope, if I extend the rows downward, the formula will automatically populate. However, what made the formula work was the way I formatted the cell.

When I just chose Duration as the Data Format and then Custom Units, clicked off hr and min boxes, to limit my field to hours and minutes, and then chose "0h 0m" for my style, I continually got the error reported above in my summation cell. But, if I chose Create Custom Format... from the Data Format drop down menu, I can set my own format for the cell. Leave the Type as "Date & Time". Delete the month day and year. For my purposes I dragged the Hour field into the format line, then I typed "h " (that is small-h followed by a space) and then I dragged the Minute field into the format line, then I typed m (no space). This gave me the format 0h 0m.

I hopes this helps.