Challenge summing time (mm:ss) values?

[Mr Davo]

Hi Everyone,



I am trying to sum times (mm:ss) in Excel 2010 and I am getting nowhere quick. No matter what I seem to try, and I have poured over the internet trying a variety of methods, I am either getting the result 0:00:00 or 2:25. I have taken the liberty of linking to a section of the troublesome sheet, and it would be great if somebody could offer some advice on how to make the summing of the 'Duration (minutes)' column behave itself!

https://dl.dropboxusercontent.com/u/...for-Excel.xlsx

Any help will be greatly appreciated.

Kind Regards,

Davo

[Pecoflyer]

External links being potentially unsafe, could you please post your sheet on the forum? Thx

[NoSparks]

Hello Davo

Your column B isn't the mm:ss that you think it is. It is actual time based on the 24 hour clock, ie: B2 is 9:05 PM.

In C2 put =TIME(0,LEFT(B2,LEN(B2)-3),RIGHT(B2,2)) *24*60 to get minutes with a decimal, drag down, then sum the column as normal.

Good luck
NoSparks

[Pecoflyer]

Another method . Use following formula

=sumproduct(B2:B113*1)/60

and custom format the result as [h]:mm:ss