472,325 Members | 1,373 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,325 software developers and data experts.

Sum() on time values

228 100+

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
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 12634
32,511 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
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
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
  13.     time1 = #12:58:36 PM#
  14.     time2 = #12:10:10 PM#
  16.     tArray = Split(time1, ":")
  17.     t1inSecond = tArray(0) * 3600 + tArray(1) * 60 + Split(tArray(2), " ")(0)
  19.     tArray = Split(time2, ":")
  20.     t2inSecond = tArray(0) * 3600 + tArray(1) * 60 + Split(tArray(2), " ")(0)
  22.     st = t1inSecond + t2inSecond
  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
Oct 12 '10 #4
228 100+
Thanks dsatino! I used your method to solving the problem perfectly!

Thank you all!
Oct 12 '10 #5
32,511 Expert Mod 16PB
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
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

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

Similar topics

by: Tom Mortimer | last post by:
Hi, A quick question - can anyone tell me how to interpret negative time values in pstats.Stats.print_stats() output? Eg - 39052965 function...
by: Ron Hocking | last post by:
When I link a MySQL table containing a TIME column to Microsoft Access the value does not display. If I edit the value it updates correctly in mysql...
by: gi75research | last post by:
What should be a very simple function is going terribly wrong, and I don't know why. StartTime and EndTime are table values (formatted like...
by: narayana | last post by:
hi gurus, I am kid at DB2 .problem is I am unable to insert values of time and date datatype fields into tables .default date datatype is taking...
by: madhura | last post by:
Hi, I need to compare some time values stored in a file in the following format & get the lowest of them. FORMAT -...
by: dwasbig9 | last post by:
Hi Group (fairly limited knowledge of Access and almost none of Access VBA. Using Access 2003). I need to sum time, I've found through the...
by: trevorjhughes | last post by:
I am running an import of some date/time values that have come from a Cognos query. These values are in a CSV file and I want to import them into...
by: whatsuppussycat | last post by:
I want to be able to SUM the values returned using the TOP 5 and COUNt statements. So far I've tried from many angles and can't get it to work, very...
by: mabrynda | last post by:
Hi, Is there any possibility to sum all the values in many (say n) columns in a single table using VBA in access 2003? Say I have a table with 200...
by: tammygombez | last post by:
Hey fellow JavaFX developers, I'm currently working on a project that involves using a ComboBox in JavaFX, and I've run into a bit of an issue....
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.