How can I Sum time duration?

[Chris Mitchell]

I have a spreadsheet that is output from an in house system.

One of the columns is "Duration", and is of the form "+0 hh:mm:ss.xxx", e.g.
"+0 00:09:11.983" where I'm guessing +0 is the number of days and is always
0 in my examples, and hh:mm:ss is hours:minutes:seconds and xxx is
milliseconds. I want to be able to add all of the Durations to arrive at
total time, preferably as a largish number of minutes, typical totals being
up to millions of minutes.

I've tried using Sum, but this returns 00:00:00

I'm sure there must be an easy way to do this, but how?

TIA

Chris

 

[Peo Sjoblom]

A couple of ways, this will give you decimal minutes


=SUMPRODUCT(SUBSTITUTE(A1:A20,"+0 ","")+0)*1440


formatted as general or number


=TEXT(SUMPRODUCT(SUBSTITUTE(A1:A20,"+0 ","")+0),"[m]:ss.000")


will give you a text representation of mm:ss.000


SUMPRODUCT(SUBSTITUTE(A1:A20,"+0 ","")+0)

then custom format as [m]:ss.000

will give you Excel time format. Don't know if the latter 2 will work with
up to million minutes though

--


Regards,


Peo Sjoblom