Not Working : Sum of Hours

[anish.ms]

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

[SamT]

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)

[Paul_Hossler]

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

[SamT]

@Paul, He has many Names and many Dates not shown on this example

That's why I am not certain my offer will help.

[p45cal]

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).

[Paul_Hossler]

@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

[anish.ms]

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")