Calculating time intervals (Time Card)

[benkingery]

I have a table that I want to use for a time clock. There are 6 columns:

EmployeeID
Date
ArriveTime
LunchOut
LunchIn
DepartTime

The PK is made up of EmployeeID and Date.

My question is kind of two-fold. 1) What datatype should I set the time fields so that I can run a query to add up the hours worked.

2) If I used date/time for date fields as I have, I'm running a query that has ([DepartTime]-[ArriveTime])-([LunchIn]-[LunchOut]) as the formula. I am getting some wierd results.

Example:
Arrive time of 8:00 am
Lunch out of 12:00 pm
Lunch In of 1:00 pm
Depart of 5:00 pm
...is yielding me .33333333333334

Anything someone can do to help me?

 

[boblarson]

Try this instead:

([DepartTime]-[LunchIn]) + ([LunchOut]-[ArriveTime])

 

[boblarson]

And ACTUALLY, you probably need to do this:

(([DepartTime]-[LunchIn]) + ([LunchOut]-[ArriveTime])*24)

 

[benkingery]

Looks like it was the 24 hour figure that needed to be added. Works perfectly.

Thanks again

 

[boblarson]

Glad we could assist.

 

[b_c]

Glad we could assist.

bob, you're a genius

...i'm going to use this formula...

any idea if the equation will return an error if the employee didnt take a lunch for the day? (i.e. no lunchout, no lunchin)


cheers,

b_c

 

[boblarson]

bob, you're a genius

...i'm going to use this formula...

any idea if the equation will return an error if the employee didnt take a lunch for the day? (i.e. no lunchout, no lunchin)


cheers,

b_c

Use NZ to handle those:


(([DepartTime]-Nz([LunchIn],0)) + (Nz([LunchOut],0)-[ArriveTime])*24)

 

[b_c]

Use NZ to handle those:


(([DepartTime]-Nz([LunchIn],0)) + (Nz([LunchOut],0)-[ArriveTime])*24)

grazi


b_c

 

[benkingery]

Of course, you may run into problems if your employee happens to clock out for lunch but not in, or in but not out. Managing punches is kind of difficult. i did it though establishing some business rules and policies. We have instituted that if our employees miss a lunch clock-in or clock-out (or both), then they will automatically be charged an hour lunch. Since the backend of our DB is SQL Server, we have a scheduled job that updates all timecards at the end of the day with any missing punches. If the employee clocked out at 12:31 for lunch and forgot to clock back in, then they are automatically clocked back in at 1:31 pm.