How to sum time durations in days greater than number of days in current month?

I got many time durations in "hh:mm:ss" and want to display the sum of these as "dd hh:mm:ss". Excel 2007 does this correct as long the summation number of days is less or equal the number of days in current month.

It is not possible to overload days using "[dd]" as you can with hours, minutes and seconds.

Duration examples

A1 00:54:12
A2 14:02:00
A3 12:20:01
A4 23:59:59
A5 =Sum(A1:A4)

Which non-array formula can I use to achieve wanted for开发者_如何学Gomat ?

Use the formula

=floor(sum(A1:A4))&" "&floor(mod(sum(A1:A4),1)*24)&":"&floor(mod(mod(sum(A1:A4),1)*24,1)*60)&":"&mod(mod(mod(sum(A1:A4),1)*24,1)*60,1)*60

which will generate the result as a string with the days hours:minutes:seconds. To correct the fractional seconds, and to ensure each of hours/minutes/seconds get two digits, you should pad them like this: TEXT(formula_section,"00")

The final formula would then be:

=floor(sum(A1:A4))&" "&TEXT(floor(mod(sum(A1:A4),1)*24),"00")&":"&TEXT(floor(mod(mod(sum(A1:A4),1)*24,1)*60),"00")&":"&TEXT(mod(mod(mod(sum(A1:A4),1)*24,1)*60,1)*60,"00")