Summation of hh:mm on a text field.

[hooi]

Hi,

I would like to know how I can use query to do summation on a text field which has hh:mm format (in hours and minutes. eg. 10:30, 11:00, ...). Which string manipulation function should I use?

Thanks in advance for helping.

 

[namliam]

Depending on the results you want you must either convert it to time or to a number...

A text fields cannot be sumated

Regards

The Mailman

 

[KodeKing]

try this

FormatDateTime myTime, vbShortTime

 

[Mile-O]

FormatDateTime myTime, vbShortTime

That won't work; that is VB or VbScript; not VBA.

I've posted a function before that takes time values/textual time values and returns them totalled as a string. Try a search under my name with Time or something. I think it's in the Queries forum if it helps to narrow down the search.

 

[raskew]

If you're summing elapsed time, you wouldn't want to convert the result to a date, which will have no relevancy, e.g., in the following example 2847 minutes converts to 1.97708332538605 days which, when stored in date/time data format becomes 12/31/1899 11:27:00 PM. And what are you gonna do with that?

Instead, hours and minutes need to be converted to integers and then may be combined and displayed as a string.

Here's a function I posted somewhere a few days ago which might provide some ideas.

Function TotTime(ParamArray VarMyVals() As Variant) As Variant
'*******************************************
'Name:      TotTime (Function)
'Purpose:   Process an array of "hh:mm" hours
'           and minutes:
'           (1) Determine total # of minutes
'           (2) Convert (1) to hh:mm string
'Author:    R Askew, 6-Oct 03
'Inputs:    From the debug window:
'           ? tottime("10:00", "9:12", "13:23", "14:52")
'Output:    Total minutes = 2847 (from debug.print)
'           47:27
'*******************************************
Dim idx As Long
Dim numHrs As Integer, numMins As Integer, minHold As Integer
Dim strHrs As String, strMins As String

minHold = 0
For idx = 0 To UBound(VarMyVals()) 'loop thru the array
   numHrs = val(Left(VarMyVals(idx), InStr(VarMyVals(idx), ":") - 1))
   numMins = val(Mid(VarMyVals(idx), InStr(VarMyVals(idx), ":") + 1))
   minHold = minHold + val((60 * numHrs) + numMins)
Next idx
Debug.Print "Total minutes = " & minHold

'Having created number of minutes as an integer,
'we now want to display it as a string in hh:mm format
strHrs = Int(minHold / 60)
strMins = Format(minHold Mod 60, "00")

TotTime = strHrs & ":" & strMins

End Function

 

[hooi]

Thank you everyone for contributing.

Since there can be many solutions for a problem, I've also come up with another solution:

Assuming the field is a text field. The query that is created to do summation on the text field containing hh:mm format is as follows:

Expr1: Format(Sum(TimeValue([TimeField])),"Short Time")

fyi please.

 

[raskew]

This will work provided the summation < 24 hours. However, if you try it using the times in my example above, the answer returned will be 23:27, not the correct sum of 47:27. In other words, the method won't allow you to create sums over 24 hours.

 

[hooi]

Great! Thank you for highlighting Raskew. I shall be using your solution for my application.

 

[raskew]

Ain't it great when a plan comes together!?

Glad it worked for you.

Bob

 

[hooi]

Hi Bob,

In a scenario where I want to sum up the total hours:minutes from a table containing multiple records, how should I call the function you've created? Is the array being used in such a situation?

Table A:
idx____Parentidx___TimeSpent
1________1________10:00
2________1_________9:12
3________1________13:23
4________1________14:52

 

[raskew]

You could pull that off using a query. If you had table tblTimes3 with field timeSpent, this would get it (change the table name to agree with yours):

SELECT Sum((60*Val(Left([timespent],InStr([timespent],":")-1))+Val(Mid([timespent],InStr([timespent],":")+1)))) AS Expr1, LTrim(Str(Int([expr1]/60))) & ":"  & ltrim(str(int([expr1] mod 60))) AS Expr2
FROM tbltimes3;

Best wishes -- Bob

 

[hooi]

Thank you Bob. Got it now...