Logbook - sum of hours and minutes, without dates and times provided. eg (1:45 + 2:31 + 3:12 = 7:28)

You can split the the "text" times into hours and minutes and use the DURATION function to convert those into durations (segments of time) that can be be added.

Here are two ways:

If you have Numbers 10.x then you can use REGEX.EXTRACT:

=DURATION(0,0,REGEX.EXTRACT(A2,"^\d*"),REGEX.EXTRACT(A2,"\d*$"))

The regex simply says grab the first digits that are "anchored" by the ^ to the beginning of the string (the hours) and the last digits that are "anchored" to the end by the $ (the minutes).

If you have an earlier version (or you just don't like Regex) then you can use FIND:

=DURATION(0,0,LEFT(A2,FIND(":",A2)−1),RIGHT(A2,LEN(A2)−FIND(":",A2)))

Place whichever formula you want to use in an adjacent column and change A2 to the first value of your list of "times". Then fill or copy paste down the column. Then, with the whole column selected, "remove" the formula by command-c followed by Edit > Paste Formula Results.

You can change the Duration formatting via the 'Style' dropdown in the right panel as shown in the screenshot.

If your region uses , as a decimal separator be sure to substitute ; for , in the formula.

More on the DURATION formula here.

SG

Unless you are against the format being 00:00 (with the leading zero, and example being 01:45), you can select the entire column and set the format to Duration. In Duration format you can simply sum the entire column.

Badunit wrote:

Unless you are against the format being 00:00 (with the leading zero, and example being 01:45), you can select the entire column and set the format to Duration. In Duration format you can simply sum the entire column.

And get hours and minutes instead of minutes and seconds?

SG

SGIII wrote:

And get hours and minutes instead of minutes and seconds?

SG

No, the column would be minutes & seconds. If the units are not displayed, it looks the same. If the resulting SUM needs to be in hr:min, it can be multiplied by 60 (and the format units changed to hr:min).