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.
Attachment 28392

Given below are the codes that I'm using

Code:

---

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

---

All your dates and times are stored as DateSerial and TimeSerial. Try

Code:

---

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)

Don't see why SumIf() is involved

1. One way

Code:

---

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

---

2. A user defined function

Attachment 28394

Code:

---

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

---

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

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

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

Quote:

---

Originally Posted by 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.

---

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

Thanks experts for your time and support.
sumifs and formatting in one go solved the issue

Code:

---

txtbx_total_hours.value=Format(Application.WorksheetFunction.SumIfs(Arg3, Arg1, txtbx_name.Text, Arg2, DateChk), "hh:mm")


---