Consulting

Results 1 to 6 of 6

Thread: Solved: Total sum of Time in a column

  1. #1
    VBAX Tutor
    Joined
    Jan 2006
    Posts
    248
    Location

    Solved: Total sum of Time in a column

    I have 14 rows in column A which I want to have the total sum for.

    The cells are custom formated to h:mm and the are at least 4 blank rows in the column maximum 5, the blank rows vary depending on the amount of shifts worked per fortnight.

    When I tried a simple SUM formula it only count the rows till the first blank row, not the actual time.

    Can anyone help with a formula that skips the blank rows and adds the actual hours up.

    Have included a example to help assist.

    Thanks
    Attached Files Attached Files

  2. #2
    VBAX Regular fmcti's Avatar
    Joined
    Mar 2011
    Posts
    16
    Location
    This is because whenever the total reaches 24, it goes back to zero, as it is another day.

    I also wonder how to get the total hour.

  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Change the result cell format to [hh]:mm
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  4. #4
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    Do a custom format on the sum cell.

    [h]:mm

    edit: beat to it again lol

  5. #5
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    744
    Location
    This is happening because we are still looking at Time and Not Duration. Something like:
    =COUNT(A2:A15)*8
    Should work with formatting retained as number and not Time.
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  6. #6
    VBAX Tutor
    Joined
    Jan 2006
    Posts
    248
    Location
    Thanks Mdmackillop that has solved that problem, I will mark thread as solved.

    Thanks again.

Posting Permissions

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