First, verify that D2:D20 contain text, not numeric time. For example, does =ISTEXT(D2) return TRUE?
If so, then ostensibly:
=INT(SUMPRODUCT(DATE(1900,1,LEFT(D2:D20,FIND("d",D2:D20)-1)) + TIMEVALUE(MID(D2:D20,FIND("d",D2:D20)+1,99))))
& TEXT(SUMPRODUCT(DATE(1900,1,LEFT(D2:D20,FIND("d",D2:D20)-1)) + TIMEVALUE(MID(D2:D20,FIND("d",D2:D20)+1,99))),"\d hh:mm:ss")
If we can assume that d, h, m and s are always 2 digits as you indicated, we can simplify:
=INT(SUMPRODUCT(LEFT(D2:D20,2) + TIMEVALUE(RIGHT(D2:D20,8)))) & TEXT(SUMPRODUCT(LEFT(D2:D20,2) + TIMEVALUE(RIGHT(D2:D20,8))),"\d hh:mm:ss")
Of course, the sum of the days might no longer be limited to 2 digits.
Bookmarks