Adding time - days, hours, minutes

Hi all,

I have inherited a spreadsheet where I am looking to add up a column of time. The time is currently in the format XXd XXh XXm.

Here are a selection of times I have in cells [I3] to [I8]:

[I3] = 34d 20h 40m
[I4] = 02d 21h 17m
[I5] = 41d 17h 04m
[I6] = 05d 21h 57m
[I7] = 13d 09h 10m
[I8] = 02d 14h 01m

I would like [I9] to display the total time which should be 101d 08h 09m.

I have looked around the internet and tried different formulas but the final SUM always seems to be wrong. One that nearly got it right was http://stackoverflow.com/questions/1...alue-example-1 . This was out by an hour but looking at the formula I couldn't work out why.

I only use excel occasionally and have inherited this worksheet which is a little over my head. Hopefully someone has a better way of doing this or has a magical formula which makes it all work!

Thanks in advance,

Shaun

Try this array formula

=INT(SUM(LEFT(I3:I8,2)*24*60,MID(I3:I8,5,2)*60,MID(I3:I8,9,2)*1)/24/60)&"d "&INT(MOD(SUM(LEFT(I3:I8,2)*24*60,MID(I3:I8,5,2)*60,MID(I3:I8,9,2)*1),24*60)/60)&"h "&MOD(SUM(LEFT(I3:I8,2)*24*60,MID(I3:I8,5,2)*60,MID(I3:I8,9,2)*1),60)&"m"

Confirm with Ctrl+Shift+Enter and not just Enter

I am confused how Ace_XL is getting 108 since mine is getting 104h

Formula:

=SUMPRODUCT(1*LEFT(I3:I8,2))+INT((SUMPRODUCT(1*MID(I3:I8,5,2))+INT(SUMPRODUCT(1*MID(I3:I8,5,2))/60))/24)&"d"&TEXT(INT((SUMPRODUCT(1*MID(I3:I8,5,2))+(SUMPRODUCT(1*MID(I3:I8,9,2))/60))/24)," 00")&"h"&TEXT(MOD(SUMPRODUCT(1*MID(I3:I8,9,2)),60)," 00")&"m"

If you insist..

=INT(SUM(LEFT(I3:I8,2)*24*60,MID(I3:I8,5,2)*60,MID(I3:I8,9,2)*1)/24/60)&"d "&TEXT(INT(MOD(SUM(LEFT(I3:I8,2)*24*60,MID(I3:I8,5,2)*60,MID(I3:I8,9,2)*1),24*60)/60),"00")&"h "&TEXT(MOD(SUM(LEFT(I3:I8,2)*24*60,MID(I3:I8,5,2)*60,MID(I3:I8,9,2)*1),60),"00")&"m"

Remember to Ctrl+Shift+Enter

Edited (and corrected) version of Sixth Sense's non-array formula

=SUMPRODUCT(1*LEFT(I3:I8,2))+INT((SUMPRODUCT(1*MID(I3:I8,5,2))+INT(SUMPRODUCT(1*MID(I3:I8,5,2))/60))/24)&"d"&TEXT(MOD(INT((SUMPRODUCT(1*MID(I3:I8,5,2))+SUMPRODUCT(1*MID(I3:I8,9,2))/60)),24)," 00")&"h"&TEXT(MOD(SUMPRODUCT(1*MID(I3:I8,9,2)),60)," 00")&"m"

Thanks for the correction Ace_XL