Results 1 to 3 of 3

Thread: Need Assistance Adding up Time in HH:MM:SS format

  1. #1
    Neophyte GATORWOMAN48's Avatar
    Join Date
    Jan 2020
    Posts
    4
    Articles
    0
    Excel Version
    Excel 2016

    Need Assistance Adding up Time in HH:MM:SS format



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am in a metrics based position where the tickets I work are timed for how long I am in there and it is in HH:MM:SS format. So if I was in a ticket for 1 hr 15 mins 2 seconds obviously the report would read 01:15:02 as an example. I want to take the column and add up that "time spent in ticket" however when I use the sum formula it will not work. I am trying to verify that the reports for each category I do are correct as I believe that they are not. Any help is appreciated.

  2. #2
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    257
    Articles
    0
    Excel Version
    2007
    .
    Here's the first thing I located :

    Code:
    Option Explicit
    
    Sub AddHHMMSS()
    '' SUM time in Column
    '' Choose Column,StartRow,EndRow
    '' result will be placed at Column:EndRow + 1
    '' Column=1  1=A,2=B, ...
    Dim i As Long
    Dim w As Worksheet
    Set w = ActiveWorkbook.Worksheets(1) ''choose first worksheet
    
    
    Dim d As Double
    Dim Column, StartRow, EndRow As Integer
    
    
    Column = 1 ''this is A Column
    StartRow = 1
    EndRow = 25
    
    
    For i = StartRow To EndRow
        d = d + CDate(w.Cells(i, Column)) * 24 * 60 * 60
    Next
    
    
    ''convert seconds to days
    
    
    d = d / 86400#
    ''display result in EndRow+1
    w.Cells(EndRow + 1, Column) = Format(d, "hh:mm:ss")
    End Sub
    Here's another in formula : https://www.consultdmw.com/excel-summing-time.html

  3. #3
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,717
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Perhaps post a sample sheet? Is your time real time or text looking like time?
    Thank you Ken for this secure forum.

Posting Permissions

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