Consulting

Results 1 to 7 of 7

Thread: Not Working : Sum of Hours

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location

    Not Working : Sum of Hours

    Hi,

    Can somebody help me with the below-
    I have time (hours) like below in a column and the cell format is [hh]:mm:ss
    01:00:00
    02:00:00
    02:00:00
    00:30:00
    00:30:00

    The above time is totaling to 6 hrs, but when I add it using sumif and format it to "HH:MM", I'm getting the below answer.
    Total Hrs.png

    Given below are the codes that I'm using

    Sub UpdateTotalEntriesHours()
        Dim Arg1   As Range
        Dim Arg2   As Range
        Dim Arg3   As Range
    
    
        tl = Len(txtbx_review_date.Text)              'total length 
        dl = InStr(txtbx_review_date.Text, ":") + 1   'date start position Example Sun : May 02, 2021
        On Error Resume Next
        DateChk = Right(txtbx_review_date.Text, tl - dl)
        On Error GoTo 0
        
        Set Arg1 = DestnWS.Range("Table_DB[Member Name]")
        Set Arg2 = DestnWS.Range("Table_DB[Review Date]")
        Set Arg3 = DestnWS.Range("Table_DB[Review Time]")
        
        txtbx_total_entries.Value = Application.WorksheetFunction.CountIfs(Arg1, txtbx_name.Text, Arg2, DateChk)
        txtbx_total_hours.Value = Application.WorksheetFunction.SumIfs(Arg3, Arg1, txtbx_name.Text, Arg2, DateChk)
        txtbx_total_hours.Value = format(txtbx_total_hours.Value, "hh:mm")
    End Sub
    Attached Files Attached Files

Posting Permissions

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