+ Reply to Thread
Results 1 to 10 of 10

Sum in dd:hh:mm:ss

  1. #1
    Registered User
    Join Date
    03-19-2019
    Location
    Mexico
    MS-Off Ver
    Office 2016
    Posts
    7

    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!!
    Last edited by AngelJD25; 03-19-2019 at 05:25 PM. Reason: SOLVED

  2. #2
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,866

    Re: Sum in dd:hh:mm:ss

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Registered User
    Join Date
    03-19-2019
    Location
    Mexico
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Sum in dd:hh:mm:ss

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

  4. #4
    Valued Forum Contributor
    Join Date
    01-07-2013
    Location
    Michigan
    MS-Off Ver
    O365
    Posts
    601

    Re: Sum in dd:hh:mm:ss

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

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    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.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,078

    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.
    Last edited by ChemistB; 03-19-2019 at 05:07 PM.
    ChemistB
    My 2¢

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  7. #7
    Registered User
    Join Date
    03-19-2019
    Location
    Mexico
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Sum in dd:hh:mm:ss

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

  8. #8
    Registered User
    Join Date
    03-19-2019
    Location
    Mexico
    MS-Off Ver
    Office 2016
    Posts
    7

    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. #9
    Registered User
    Join Date
    03-19-2019
    Location
    Mexico
    MS-Off Ver
    Office 2016
    Posts
    7

    Re: Sum in dd:hh:mm:ss

    Quote Originally Posted by shg View Post
    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. #10
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,346

    Re: Sum in dd:hh:mm:ss

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

    Formula: copy to clipboard
    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: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Roel Jongman; 03-19-2019 at 06:16 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1