: how can I sum stopwatch time

[msamir12]

I have a stopwatch form in Access that records results in a table and I wants to sum the time in this table
the time is recorded in this format "hh:mm:ss:ms"


and this is an example "00:00:51:66"
if you want the file I can attach the link for the access file .. please I need a fast response

Thanks inadvance

[CJ_London]

duplicate thread here.

http://www.access-programmers.co.uk/...=1#post1467554

[CJ_London]

take a look at this link - http://www.excelguru.ca/content.php?184

suggest you will need a udf to split your string into its component parts then multiply each part by the respective number of milliseconds and add them together. google the split function.

[sneuberg]

take a look at this link - http://www.excelguru.ca/content.php?184

suggest you will need a udf to split your string into its component parts then multiply each part by the respective number of milliseconds and add them together. google the split function.

Actual time when assigned to a double is the fractional part of a day. So to get milliseconds for example you just multiple it by 24 * 60 * 60 * 1000

[June7]

I expect this data is text, not a date/time. AFAIK, will have to parse the text string into its constituent parts, calculate each to a common unit such as seconds, add the four parts then Sum the records.

This would be easier if each part was in a separate field. However, use string manipulation functions to extract the parts. VBA is not needed as long as the string structure is consistent. Calculations in query:

Hrs: Val(Left([fieldname],2))
Mins: Val(Mid([fieldname],4,2))
Secs: Val(Mid([fieldname],7,2))
Mss: Val(Right([fieldname],2))

TotalSecs: Hrs * 3600 + Mins * 60 + Secs + Mss / 1000

Now Sum on TotalSecs.

However, be aware these calcs will error if the field does not have value.

How do you get the data down to ms? Are you using Windows API code?

Attaching db wouldn't hurt.

[ssanfu]

You don't provide much info. Did you understand what Ajax meant?
How are your VBA coding skills?
What have you tried?


Edit...
Oops, I missed that June...
Must have been on the phone too long and didn't refresh the window.

[Micron]

cross posted here today also (3 places now)
http://www.mrexcel.com/forum/microso...atch-time.html
I was helping there, but not as of now...

[sneuberg]

The attached database has queries that implement the suggestions previously posted. Hope this helps.

[msamir12]

The attached database has queries that implement the suggestions previously posted. Hope this helps.

@sneuberg Thanks a lot for your help I don't know how to thank you in this. I only wonder the final sum is in milliseconds . . correct ??
this is a wonderful work believe it

[June7]

Calculations in post 6 return value in seconds. Could change for hours or minutes - just means more decimal digits.

[msamir12]

this is the stopwatch file if you want to edit it with the new values

DB stopwatch.accdb

[June7]

Edit for what new values?

Modify the formula as you see fit. It's just arithmetic converting time parts to common unit.

Have you made any attempt to build query with suggested expressions?

[msamir12]

Edit for what new values?

Modify the formula as you see fit. It's just arithmetic converting time parts to common unit.

Have you made any attempt to build query with suggested expressions?

@june7 thanks alot I am sorry I will give a try as I catch cold and sick a little bit thanks again for your help and sorry for the delayed response

[ssanfu]

You have the field in Table1 named "Time". This is a reserved word and an intrinsic function.

You also have some corruption in the dB you posted. I recreated your dB (well, most of it).

I changed the field name
I modified the code.
I modified the form.
I thought there should be 3 digits for the milliseconds, so I also modified that.


This has given me some ideas - I might add a couple of lap times.

[msamir12]

Hi Guys ..

Honestly I donno how can I thank you for this ... all of you .. really this is the final file of the whole work yet I needed to ask for one thing about the file.. how can I count every time as it is an individual time and make an equation to save the total seconds on total time trials
Ex: assume that I have trials for some test and each time I record the time for every trial and I already calculated the sum of seconds and I want this sum to be (total seconds/total trials) to know the average for handling the trials .

thanks a lot guys for help I am really very glad StopWatch new.zip

File attached