# Sum in dd:hh:mm:ss

1. ## 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

2. ## Re: Sum in dd:hh:mm:ss

Formula:
`Please Login or Register  to view this content.`

3. ## Re: Sum in dd:hh:mm:ss

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

4. ## Re: Sum in dd:hh:mm:ss

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

5. ## Re: Sum in dd:hh:mm:ss

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.

6. ## Re: Sum in dd:hh:mm:ss

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.

7. ## Re: Sum in dd:hh:mm:ss

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

8. ## Re: Sum in dd:hh:mm:ss

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

9. ## Re: Sum in dd:hh:mm:ss

Originally Posted by 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.
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

10. ## Re: Sum in dd:hh:mm:ss

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

Formula:
`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)

Formula:
`Please Login or Register  to view this content.`

There are currently 1 users browsing this thread. (0 members and 1 guests)