Sum in dd:hh:mm:ss

[AngelJD25]

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!!

[avk]

Please Login or Register  to view this content.

[AngelJD25]

didn't work man, but thank you for the attention

[Melvosh]

Are these numbers formatted as date/time, or are these text entered exactly as shown?

[shg]

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.

[ChemistB]

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.

[AngelJD25]

That worked! Thank you and greetings to the lone star state

[AngelJD25]

That worked! Could you help me for more than 31 days? I have hundreds of data, I'm sure it will add way more than 30 days. Thank you and greetings to the lone star state

[AngelJD25]

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.

That worked! Could you help me for more than 31 days? I have hundreds of data, I'm sure it will add way more than 30 days. Thank you and greetings to the lone star state

[Roel Jongman]

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

Please Login or Register  to view this content.

____
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)

Please Login or Register  to view this content.