anish.ms
05-02-2021, 11:46 AM
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
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