Excel 2007 : time for summing duration of time

**confusus** · 05-19-2010, 04:38 AM

Ok, so i have this formula throughout the cells F3:F103
=SUBSTITUTE(LEFT
(VLOOKUP($C99;'sheet2'!$A$4:$D$148;4;0);
LEN(VLOOKUP($C99;'sheet2'!$A$4:$D$148;4;0)
)-4);" h ";":")..
I did it on my own, dunno if there's a simpler way.

I have duration of time values stored as text in ("XX h XX min" or ("X h XX min") at times) format.After using the formula i wrote up,(now they are in [h]:mm format), I want to SUM them all in cell F104, but using SUM() formula does not work, although +ing them one by one works all right.. I AM STUCK.
Help pls.

**DonkeyOte** · 05-19-2010, 04:41 AM

Originally Posted by confusus

I did it on my own, dunno if there's a simpler way.

There might well be as easier way but without a sample it's hard to say.

Originally Posted by confusus

I want to SUM them all in cell F104, but using SUM() formula does not work, although +ing them one by one works all right.. I AM STUCK.

SUBSTITUTE is a string based function and as such returns Text not Numbers... if you want the time "strings" as true time values (numbers) then "coerce" the string accordingly:

=0+SUBSTITUTE(....)

At which point you should find you can sum.

**DonkeyOte** · 05-19-2010, 04:46 AM

In terms of simplifying, based on your XX h XX min / X h XX min construct - you might find you can use:

=0+SUBSTITUTE(LEFT(VLOOKUP($C99;'sheet2'!$A$4:$D$148;4;0);7);"h";":")