Sum() on time values

[samvb]

Hi,

I have a table with column finishtime. It can stores the time it took for a person to finish his task. it can range from 00:01:00 to 12:59:59 (company rule). The program works perfectly fine in all except when I need to find out the total time it took for a person to finish his task. Sum converts the time to AM/PM and returns time instead.

For instance, I have this two values: 12:58:36 and 12:10:10. The total time it took him is more than 25 hours and I need to return a time like 25:xx:xx.

But it returns :01:08:46

The query i am using is

SELECT format(Sum(CDate(finishtime)),'hh:nn:ss') AS ttltime FROM table1
 

finishtime is a text field by the way. And it doesn't matter if I append AM/PM to the values. The result is the same.

Any tips please?

[NeoPa]

There is no format option that I'm aware of that will give you that functionality I'm afraid Sam. You'll have to roll your own. You know how date data is stored yes?

[dsatino]

Your best best would be to convert everything to a single numerically formatted unit, probably hours.

So 12:58:36 becomes:

12+(58/60)+(36/3600)= 12.97667 Hours

And 12:10:10 becomes:

12+(10/60)+(10/3600)= 12.16944 Hours

Total Time = 25.14611 = 25:08:46

Since finishTime is a text field, it will be easy to capture the parts using a mid() function.

[Mariostg]

Try this function:

Function AddTime()
    Dim H As Long
    Dim M As Long
    Dim S As Long
    Dim HMS As String
    Dim t1inSecond As Long
    Dim t2inSecond As Long
    Dim st As Long
    Dim tArray As Variant
    Dim time1 As Date
    Dim time2 As Date
 
    time1 = #12:58:36 PM#
    time2 = #12:10:10 PM#
 
    tArray = Split(time1, ":")
    t1inSecond = tArray(0) * 3600 + tArray(1) * 60 + Split(tArray(2), " ")(0)
 
    tArray = Split(time2, ":")
    t2inSecond = tArray(0) * 3600 + tArray(1) * 60 + Split(tArray(2), " ")(0)
 
    st = t1inSecond + t2inSecond
 
    H = st \ 3600
    M = (st - (H * 3600)) \ 60
    S = st - (H * 3600) - M * 60
    HMS = H & IIf(M < 10, ":0", ":") & M & IIf(S < 10, ":0", ":") & S
    Debug.Print HMS
End Function
 

[samvb]

Thanks dsatino! I used your method to solving the problem perfectly!

Thank you all!

[NeoPa]

samvb:
I used your method to solving the problem perfectly!

I suggest it would be a good idea to set their post as the Best Answer in that case :-)

[neelsfer]

I use "long time" to do a similar calculation for finishtime and startime tables
TotalTime: Format([FinishTime]-[starttime],"hh:nn:ss")