Sum in dd:hh:mm:ss

Hi guys. I'm using a database of the duration time of the different trips that a set of vehicles made during the month. The data is in the format dd:hh:mm:ss. I would like to add these data (more than 300 rows) to obtain the total amount of time of the different trips and with this information get a lot more of averages and relevant information for my customers. The thing is that I can't get the sum.

For example:

A1: 00:17:41:24
A2: 0:01:39:00
A3: Sum(A1:A2)

But i just got 0:00:00:00

Please help me!!

Formula:

=sumproduct(a1:a2)

Excel doesn't recognize those strings as numbers.

	A	B
1	1:17:41:24
2	0:01:39:00
3	1:19:20:24	A3: =TEXT(SUMPRODUCT(--LEFT(A1:A2, FIND(":", A1:A2) - 1)) + SUMPRODUCT(--MID(A1:A2, FIND(":", A1:A2) + 1, 8)), "d:hh:mm:ss")

That will only work to a total of 31 days.

I believe the issue is that even though the cell is formatted as dd:hh:mm:ss, when you enter the value, Excel doesn’t recognize that as an input format.
Try entering your values as =1 + “17:42:25”

The alternative is to enter days as whole numbers in 1 cell (col A), then time (hh:MM:ss) in col B, then Sum in Col C

Orrr, you could leave them as text and adapt your formula to calculate them as per shg’s post.

If you change shg solution a bit you can also show duration over 31 days

Formula:

=TEXT(DATEDIF(0,SUMPRODUCT(--LEFT(A1:A2, FIND(":", A1:A2) - 1)),"d"),"0:")&TEXT(SUMPRODUCT(--MID(A1:A2, FIND(":", A1:A2) + 1, 8)), "hh:mm:ss")

____
little side step
edit: assuming above text formula is put in A3 then follwing formula will allow you to convert this result into a value you can calculate with the outcome,
(this formula van also be used on single dates in your area)

Formula:

=SUMPRODUCT(--LEFT(B6, FIND(":", B6) - 1)) + SUMPRODUCT(--MID(B6, FIND(":", B6) + 1, 8))