Apr 12 2012, 02:20 PM
I'm creating a table/form that I need to total multiple start & end times. I need to be able to take mulitple entries (example: start 8:00am end 10:30, start 11:00 end 2:00, start 2:15 end 5:00) and run a total time for how long it took to complete the job. It's really just like a time clock that tells you your total hours worked. I've tried using the =datediff("n",[start_time],[stop_time])/60 (with just one set) but it's not giving me the total I'm looking for. Can anyone assist me?
Apr 12 2012, 02:25 PM
What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.
Have you tried something like?
SELECT Sum(DateDiff("n", [start_time], [stop_time]))/60 As TotalTime
Just my 2 cents...
Apr 12 2012, 02:33 PM
I'm on 2010..sorry. I haven't tried it with the "as totaltime". I'll try it now and let you know. Thanks
Apr 12 2012, 02:46 PM
Let us know how it goes...
Apr 12 2012, 03:27 PM
What's the structure of your table? Is each bout of work a different record, related one-to-many to (say) an employee table?
If so you should be able to create a query with a calculated field, e.g.
SELECT EmployeeID, [Start_Time], [Stop_Time], DateDiff("n", [Start_time], [Stop_Time]) / 60 AS HoursWorked FROM....
You could display these individual bout times on a Subform (or a Report), and Sum the HoursWorked value in a textbox in the footer.
Or you could do a totals query:
SELECT EmployeeID, Sum(DateDiff("n", [Start_time], [Stop_Time]) / 60) AS HoursWorked FROM....
with criteria to limit the times to the desired day or week or month worked.
Apr 13 2012, 02:01 PM
Not sure if this will help but this is how I calculate differences in time
You can use the following logic to calculate the difference between two times which works even across midnight.
Format([StartTime] -1 -[EndTime], "Short Time")
Apr 13 2012, 06:06 PM
Glen, that's fine for displaying time intervals under 24 hours, but it breaks down if you have periods (or sums of periods) over that limit. E.g. 26 hours 30 minutes will display as #12/31/1899 02:30#, which is not going to work well on a timecard!
Apr 13 2012, 06:21 PM
John I never thought of going past a 24 hour period so yes it would not work for that but could work for a daily time and then Sum them.
Thanks for pointing that out to me.
Apr 17 2012, 05:24 PM
Thank you all for you assistance. I was able to get it using [Stop1]-[Start1]+[Stop2]-[Start2]+[Stop3]-[Start3]+[Stop4]-[Start4]+[Stop5]-[Start5] which gave me a total of the hours worked on a job. The only issue I had was I had to make sure that the default value was set to 0 in order for access to calcualte properly. I'm sure there's a much more efficent way of doing this, but it worked. =))
Apr 17 2012, 05:26 PM
Congratulations on getting it to work. Good luck with your project.
Apr 18 2012, 12:52 AM
Glad to see you got it working!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here