How To Calculate Monthy Total on-the-clock time
1/1/2005 00:01:00 AM 1/1/2005 02:00:00 PM
1/1/2005 00:01:00 AM 1/1/2005 09:01:00 AM
1/1/2005 23:00:00 AM 1/2/2005 12:35:00 PM
1/28/2005 06:30:00 AM 1/31/2005 07:00:00 PM
1/31/2005 06:30:00 AM 2/01/2005 21:31:00 PM
2/3/2005 00:01:00 AM 2/04/2005 23:59:00 PM
Above is representative of a table (TimeTable) capturing my employeeâs time on-the-clock. Each row represents the time a different employee clocked-in (StartDate) and clocked-out (EndDate).
I am attempting to pull the fields and calculate the total on-the-clock time, by month, in an attempt to show the total time that I do not have around-the-clock employee coverage. If the business is to run 24/7, this an important calculation so I can tell my boss how many more personnel I need to hire to get complete coverage. The difficulty I see in the rows are:
1. Some have overlapping times
2. Some have some equivalent clock-in time
3. Some could have equivalent clock-out times
4. Some EndDates exceed the end of month cut-off.
5. Some StartDates will preceed the start of a month.
For the above figures, I have an answer which was manually done.
-Jan 2005 has 31 days or 744 hours. Using the above table shows employee on-the-clock time as 117.05 hours therefore I do not have coverage for 626.95 hours.
-Feb 2005 has 28 days or 672 hours. Using the above table shows employee on-the-clock time as 69.47 hours.
What would the formula look like to get the total on-the-clock time, by month, in a query (or queries) using MSAccess 2000? I imagine this could be pulled off via query and pivot table.