Did you encounter a task like this? I did. Actually it is not as difficult as you may expect. It could be done in just a minute IF the data is not too bad, like the one shown above.

I am going to show you two approaches to handle this:

- Formula;
- A combination of simple techniques that even a regular Excel user should be able to manage.

## 1) Formula Approach

=SUMPRODUCT(("0:"&SUBSTITUTE(SUBSTITUTE(RIGHT(A2:A21,6),"(",""),")",""))+0)

Let’s examine it inside out.

RIGHT(A2:A21,6)

Since we are so lucky that the time we need are all sitting in the end of a text string, enclosed by (), we may use RIGHT(A2:A21,6) to extract the time portion from the text string. And this is the resulting array:

{"(8:12)";"51:36)";"41:18)";"38:15)";"43:20)";"42:58)";"(3:36)";"43:36)";"38:49)";"47:13)";"41:34)";"50:30)";"45:14)";"20:21)";"22:54)";"21:41)";"20:06)";"58:24)";"21:27)";"45:21)"}

Because the time is in m:ss format, there are cases of “(” being extracted. Thus we need to

Remove both “(” and “)” from the array by using SUBSTITUTE(text, old text, new text)

SUBSTITUTE(SUBSTITUTE(RIGHT(A2:A21,6),"(",""),")","")

The result then becomes:

{"8:12";"51:36";"41:18";"38:15";"43:20";"42:58";"3:36";"43:36";"38:49";"47:13";"41:34";"50:30";"45:14";"20:21";"22:54";"21:41";"20:06";"58:24";"21:27";"45:21"}

which is very close to what we need…

Concatenate “**0:**” (zero colon) to the resulting array

` (`**"0:"&**SUBSTITUTE(SUBSTITUTE(RIGHT(A2:A21,6),"(",""),")",""))

As Excel would interpret the result as hh:mm we need to concatenate “0:” to the above array to make sure that the resulting time will be interpret correctly as mm:ss.

{"0:8:12";"0:51:36";"0:41:18";"0:38:15";"0:43:20";"0:42:58";"0:3:36";"0:43:36";"0:38:49";"0:47:13";"0:41:34";"0:50:30";"0:45:14";"0:20:21";"0:22:54";"0:21:41";"0:20:06";"0:58:24";"0:21:27";"0:45:21"}

Adding 0 to the above result to convert the text into a real time value

` ("0:"&SUBSTITUTE(SUBSTITUTE(RIGHT(A2:A21,6),"(",""),")",""))`**+0**

Results in:

{0.00569444444444444;0.0358333333333333;0.0286805555555556;0.0265625;0.0300925925925926;0.029837962962963;0.0025;0.0302777777777778;0.0269560185185185;0.0327893518518519;0.0288657407407407;0.0350694444444444;0.031412037037037;0.0141319444444444;0.0159027777777778;0.0150578703703704;0.0139583333333333;0.0405555555555556;0.0148958333333333;0.0314930555555556}

Feeding the array into SUMPRODUCT to give the total

i.e. 0.49056712962963 or **11:46:25** (format as [h]:mm:ss)

Not really difficult… 🙂

If you are good in using FUNCTIONS and know about array formulation, it should not be too difficult. Nevertheless, I know that there are quite a lot people using Excel are having Formula-phobia. 😛 That’s why I am going to show you an non-formula approach, which may be even faster.

## 2) Non-formula approach.

Copy another set of your data and paste it on the next column

Using Find and Replace to get the time portion *(tip: CTRL+H to open the Find and Replace dialog box. Note: Select the range you want to perform Find and Replace beforehand, otherwise your original data will be in danger…)*

- First Find *( and Replace with 00:

- See the changes?! Then Find ) and Replace with nothing

- Now you should have the time portion extracted properly:

*(You may format it back to mm:ss if required)*

A simple SUM would do the rest

Did you notice that the above steps are actually the same as the ones described for formula-approach?!

Here’s a Sample File file for you to download.

Honestly, if it is an one-off task, I would absolutely do it in the way demonstrated in the 2nd approach. How about you?

Flash Fill with {SUM((B8:B27/60))} also works

LikeLike

Hi Leonid,

I don’t know Flash Fill can do that… Actually I haven’t tried Flash Fill as I am still using Excel 2010.

Appreciate if you could elaborate more on how it works.

Cheers,

LikeLike

Hi @MF,

In Excel 2013:

1. In cell B8 type 8:12. It tells Excel what pattern we’d like to use transforming data in A8:A27.

2. With B8 selected click Flash Fill in Data Tools section on Data ribbon. It fills B8:B27 with time values.

3. To get total time we need to divide time values by 60 and sum them up as =SUMPRODUCT(B8:B27/60) or array-entered ={SUM(B8:B27/60)}

Cheers,

Leonid

LikeLike

oic. Thanks for your input, Leonid.

I wish I have Excel 2013 (or even Excel 2016). 🙂

LikeLike

For the one-time approach, use Text-to-Columns to parse the data into columns. One of the columns will have the time in parentheses. e.g., (8:12)

To do the rest with forumla instead of search and replace, you could then use MID to grab the time text without the parentheses. (e.g., =MID(C8,2,LEN(C8)-2)

Concatenate “0:” on the front as you’ve suggested, convert to a value and add them up.

LikeLike

Hi RickB,

Good suggestion!

thanks😀

LikeLike