Hi,

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?

Hi,



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
FROM TableName

(untested)
Just my 2 cents...

I'm on 2010..sorry. I haven't tried it with the "as totaltime". I'll try it now and let you know. Thanks

Hi,



Let us know how it goes...

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.

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")

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!

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.

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. =))

Hi,

Congratulations on getting it to work. Good luck with your project.

Glad to see you got it working!