The key thing to understand is that there is no such thing in Access as a
time value or a date value, only date/time values, so its important that the
start and end times per employee per shift include the date as well as the
time of day. That way you cater for shifts spanning midnight.
Once you have the data stored in this way you can get the time duration
using the DateDiff function, returning it in the smallest relevant time unit,
probably minutes, and then converting the final result to hours and minutes.
So with a table TimeSheet with columns EmployeeID, StartDateTime,
EndDateTime you could for instance return the total hours and minutes worked
per employee in a given week with a query like this:
PARAMETERS [Enter start of week:] DATETIME;
SELECT [Enter start of week:] AS WeekStarting, EmployeeID,
SUM(DATEDIFF("n",StartDateTime,EndDateTime))\60 & ":" &
FORMAT(SUM(DATEDIFF("n",StartDateTime,EndDateTime)) MOD 60,"00")
AS TotalTimeWorked
FROM TimeSheet
WHERE StartDateTime >= [Enter start of week:] AND
StartDateTime < DATEADD("d",8,[Enter start of week:])
GROUP BY EmployeeID;
When you open this query, or a form or report based on it, it will prompt
for the start date of the week for which you want the total time worked per
employee returned. It works like this:
1. Firstly the parameter is declared as datetime; this avoids any possible
misinterpretation of the value entered as an arithmetical expression rather
than a date.
2. The sum of the difference in minutes between the start and end of each
shift worked per employee is firstly divided by 60, using the integer
division \ operator, to return the total hours.
3. The Mod operator is applied to the same sum to return the remaining
minutes, formatting these as two digits.
4. The query is restricted to those rows where the start of each shift is
on or later than the start of the week date entered as the parameter, and
earlier than 8 days after the date. So any shift starting on the last day of
the week would be regarded as completely within the week even if it goes on
beyond midnight. It’s a lot easier to work in whole shifts like this rather
than trying to assign proportions of the time for such a shift to different
weeks. You could of course restrict it on shifts ending within the week
rather than starting within it if you prefer.
Ken Sheridan
Stafford, England
