469,271 Members | 1,685 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Sum() on time values

228 100+
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
Expand|Select|Wrap|Line Numbers
  1. SELECT format(Sum(CDate(finishtime)),'hh:nn:ss') AS ttltime FROM table1
  2.  
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?
Oct 12 '10 #1
6 11721
NeoPa
32,171 Expert Mod 16PB
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?
Oct 12 '10 #2
dsatino
393 256MB
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.
Oct 12 '10 #3
Mariostg
332 100+
Try this function:
Expand|Select|Wrap|Line Numbers
  1. Function AddTime()
  2.     Dim H As Long
  3.     Dim M As Long
  4.     Dim S As Long
  5.     Dim HMS As String
  6.     Dim t1inSecond As Long
  7.     Dim t2inSecond As Long
  8.     Dim st As Long
  9.     Dim tArray As Variant
  10.     Dim time1 As Date
  11.     Dim time2 As Date
  12.  
  13.     time1 = #12:58:36 PM#
  14.     time2 = #12:10:10 PM#
  15.  
  16.     tArray = Split(time1, ":")
  17.     t1inSecond = tArray(0) * 3600 + tArray(1) * 60 + Split(tArray(2), " ")(0)
  18.  
  19.     tArray = Split(time2, ":")
  20.     t2inSecond = tArray(0) * 3600 + tArray(1) * 60 + Split(tArray(2), " ")(0)
  21.  
  22.     st = t1inSecond + t2inSecond
  23.  
  24.     H = st \ 3600
  25.     M = (st - (H * 3600)) \ 60
  26.     S = st - (H * 3600) - M * 60
  27.     HMS = H & IIf(M < 10, ":0", ":") & M & IIf(S < 10, ":0", ":") & S
  28.     Debug.Print HMS
  29. End Function
  30.  
Oct 12 '10 #4
samvb
228 100+
Thanks dsatino! I used your method to solving the problem perfectly!

Thank you all!
Oct 12 '10 #5
NeoPa
32,171 Expert Mod 16PB
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 :-)
Oct 12 '10 #6
neelsfer
547 512MB
I use "long time" to do a similar calculation for finishtime and startime tables
TotalTime: Format([FinishTime]-[starttime],"hh:nn:ss")
Oct 12 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Tom Mortimer | last post: by
2 posts views Thread by dwasbig9 | last post: by
1 post views Thread by trevorjhughes | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.