How To Calculate the Total on-the-clock time, by Month, in MSAcces

M

Michaniker

How To Calculate the Total on-the-clock time, by Month, in MSAccess 2000

StartDate EndDate
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.
--
Michaniker

P.S. This was answered once before but the thread was deleted before I got a
change to try the results.
Webmaster for PartMonster.com (http://www.partmonster.com)
 
K

KARL DEWEY

Sum(DateDiff("n",[StartDate],[EndDate])
This will give you a number like 2343456234 which is the number of minutes.
You will then need to divide to come up with days - hours - minutes.

I see what looks like error in your data example. The last entry 2/3/2005
00:01:00 AM 2/04/2005 23:59:00 PM is 23 hours. I only know of the Fire
Department where people put in that many hours in a day. You may need to run
a query to check errors of this type.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top