Formula for daily/weekly hours worked

[Guest]

What formula do I use to calculate daily/weekly hours for employees?

 

[Guest]

Hi Denise

There are a few way you can do this - depending on how you store the start
time and end time. Have a look at this microsoft page and it will give you
some good ideas.

http://office.microsoft.com/en-us/access/HA011102181033.aspx?pid=CL100570041033

Good luck

 

[Guest]

It would depend on what your database structure looks like. Post an example
of your table structure and a couple of records of data.

What do you want your output to look like?

Dale

 

[John W. Vinson]

What formula do I use to calculate daily/weekly hours for employees?

Depends on the structure of your table and how you're storing their hours.
Care to give us a hand?


John W. Vinson [MVP]

 

[Guest]

I'm trying to figure out the proper formula for the table below. Please
advise.

Time In Lunch out Lunch in Time Out
MON 6/25/07 7:00 AM 12:00 PM 1:00 PM 4:00 PM 8.00
TUES 6/26/07 7:00 AM 12:00 PM 1:00 PM 4:00 PM 8.00
WED 6/27/07 7:00 AM 12:00 PM 1:00 PM 4:00 PM 8.00
THURS 6/28/07 7:15 AM 12:00 PM 12:30 PM 1:00 PM 5.25
FRI 6/29/07 -
SAT -
SUN -
ST:
OT:

 

[John W. Vinson]

I'm trying to figure out the proper formula for the table below. Please
advise.

Time In Lunch out Lunch in Time Out
MON 6/25/07 7:00 AM 12:00 PM 1:00 PM 4:00 PM 8.00
TUES 6/26/07 7:00 AM 12:00 PM 1:00 PM 4:00 PM 8.00
WED 6/27/07 7:00 AM 12:00 PM 1:00 PM 4:00 PM 8.00
THURS 6/28/07 7:15 AM 12:00 PM 12:30 PM 1:00 PM 5.25
FRI 6/29/07 -
SAT -
SUN -
ST:
OT:

Well...

Stop.

Your table structure IS WRONG. Storing a record for MON simply makes no sense.
Not storing an employeeID is another problem.

What are the actual fieldnames and tablenames in your table? Am I
misunderstanding (i.e. is MON 6/25/07 7:00am actually the value of the [Time
In] field?

You can use DateDiff("n", [Time In], [Lunch Out]) + DateDiff("n", [Lunch In],
Time Out]) to calculate the total minutes worked in a query, and total this
value on a Form or Report (but not in a table). This will need elaboration if
the employee doesn't take lunch or takes the afternoon off, though. You may
want to consider a different table structure such as:

Employees
EmployeeID <Primary Key>
LastName
FirstName
<other biographical data>

Hours
EmployeeID <link to Employees>
WorkStart <date/time e.g. #6/25/07 7:00:00am#)
WorkEnd <date/time>
ReasonForLeaving <e.g. "Lunch", "End of day", "Sick time", "Vacation", ...>


This design will let an employee have zero, one, two or more spans of time at
work, and let you easily do a Totals query using DateDiff to calculate the
time spent on each span, add them up over the course of a day, a week, or a
month, etc.

John W. Vinson [MVP]