Consulting

Results 1 to 7 of 7

Thread: Not Working : Sum of Hours

  1. #1
    VBAX Contributor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    132
    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

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,619
    Location
    All your dates and times are stored as DateSerial and TimeSerial. Try
    txtbx_total_hours.Value = Application.WorksheetFunction.SumIfs(Arg3.value2, Arg1, txtbx_name.Text, Arg2, DateChk)
    If you format the dates and times on the sheet as General, you will see the underlying values (Value2's)
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,825
    Location
    Don't see why SumIf() is involved

    1. One way

    txtbx_total_hours.Value = format(24.0 * txtbx_total_hours.Value, "hh:mm")

    2. A user defined function

    Capture.JPG


    Option Explicit
    
    
    Function SumHours(r As Range) As Double
        Dim t As Double
        Dim c As Range
        Dim h As Long, m As Long
        
        For Each c In r.Cells
            t = t + CDbl(c.Value)
        Next
        
        h = Int(24# * t)
        m = 60# * (24# * t - h)
        
        SumHours = TimeSerial(h, m, 0)
    End Function
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,619
    Location
    @Paul, He has many Names and many Dates not shown on this example

    That's why I am not certain my offer will help.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,335
    It looks looks as if you're using txtbx_total_hours (which I'm guessing is a textbox therefore only contains text) to hold a numeric value before you format it.
    I would further guess that the Format statement doesn't handle strings well; see the difference if in the Immediate pane you type:
    ?Format("0.25","hh:mm")
    and press Enter (you get 00:25) [This is what you're seeing in the text box.]
    versus you typing:
    ?Format(".25","hh:mm")
    and press Enter (you get 06:00)
    Solution:
    Either use a numeric (or variant) variable to hold your intermediate Sumifs result:
    zzz = Application.WorksheetFunction.SumIfs(Arg3, Arg1, txtbx_name.Text, Arg2, DateChk)
    txtbx_total_hours.value = Format(zzz, "hh:mm")
    or do the sumifs and formatting in one go:
    txtbx_total_hours.value=Format(Application.WorksheetFunction.SumIfs(Arg3, Arg1, txtbx_name.Text, Arg2, DateChk), "hh:mm")

    Finally, if your result ends up with more than 24 hours you'll only see the hours part of a single day, so you may want to use
    Application.Text(Your Sum, "[hh]:mm")
    instead of Format (1.25 will show as 30, not 6).
    Last edited by p45cal; 05-02-2021 at 02:43 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,825
    Location
    Quote Originally Posted by SamT View Post
    @Paul, He has many Names and many Dates not shown on this example

    That's why I am not certain my offer will help.
    Dunno, but since so much information was missing from the attachment, I just took a shot at the most up front question: "How to add the times"

    The biggest factor was that just adding the times gave an answer (.25) in days, so 24 x .25 = 6 hours
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Contributor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    132
    Location
    Thanks experts for your time and support.
    sumifs and formatting in one go solved the issue
    txtbx_total_hours.value=Format(Application.WorksheetFunction.SumIfs(Arg3, Arg1, txtbx_name.Text, Arg2, DateChk), "hh:mm")
    

Posting Permissions

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