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

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

28394




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