+ Reply to Thread
Results 1 to 7 of 7

Adding Time, Minutes & Seconds

  1. 04-03-2012, 02:20 PM #1
    critter4511
    critter4511 is offline
    Registered User
    Join Date
    04-03-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Adding Time, Minutes & Seconds

    I've searched and experimented and cannot find a solution to this problem. Using Excel 2010.

    I have raw time data, formatted as General, from a database that represents mm.ss.0 (tenths of seconds), with the value of the minutes being very large. Example: 994369:17.3. I need to SUM many rows of time and prefer to stay in the same format and not be in the hh:mm:ss format. I've formatted the cells in many different ways and each time the SUM result is 00:00.0.

    Here are examples of the data I am working with and trying to add.
    2631:18.0
    547:17.5
    280986:52.0
    994369:17.3
    659639:00.6
    Reply With QuoteRegister To Reply
  2. 04-03-2012, 02:39 PM #2
    Dennis7849
    Dennis7849 is offline
    Valued Forum Contributor
    Join Date
    03-14-2012
    Location
    Arizona USA
    MS-Off Ver
    Excel 2000/2007
    Posts
    408

    Re: Adding Time, Minutes & Seconds

    Can you attach a sample workbook?
    Reply With QuoteRegister To Reply
  3. 04-03-2012, 02:50 PM #3
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Adding Time, Minutes & Seconds

    If you have data in A1:A10 then try this formula for a sum

    =SUMPRODUCT((0&RIGHT(A1:A10,6))+(0&LEFT(A1:A10,(A1:A10<>"")*(LEN(A1:A10)-6)))/144)

    custom format result cell as [m]:ss.0
    Audere est facere
    Reply With QuoteRegister To Reply
  4. 04-03-2012, 04:24 PM #4
    critter4511
    critter4511 is offline
    Registered User
    Join Date
    04-03-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Adding Time, Minutes & Seconds

    Conversation Duration.xlsx Sample workbook attached.
    Reply With QuoteRegister To Reply
  5. 04-03-2012, 04:45 PM #5
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Adding Time, Minutes & Seconds

    As reluctant as I am to post a formula in a thread where DLL participates ...

    =SUMPRODUCT(LEFT(A2:A13, FIND(":", A2:A13) -1) / 1440 + RIGHT(A2:A13, 3) / 86400)

    and format as [m]:ss.0
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  6. 04-03-2012, 05:11 PM #6
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Adding Time, Minutes & Seconds

    Nice suggestion shg!.....small correction I think....

    =SUMPRODUCT(LEFT(A2:A13, FIND(":", A2:A13) -1) / 1440 + RIGHT(A2:A13, 4) / 86400)

    My suggestion was also designed to cope with blanks in the range.....but won't work with any values shorter than that in A8 (other than blanks). If you change the range so that A1 isn't included you should get the correct result
    Reply With QuoteRegister To Reply
  7. 04-03-2012, 06:41 PM #7
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Adding Time, Minutes & Seconds

    !.....small correction I think....
    I rest my case ...
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1