Employee Time Database with "Banked Time"

[J. Trucking]

Hello,

I was wondering if anyone has ever tried to create a time management
system that accounts for banked time. This is the structure that I am
trying to build something for:

1. An employee gets 40 hours a week billed out at regular time.
2. All hours after 40/week are counted as overtime.
3. An employee can only get paid for a maximum amount of overtime
hours per pay cheque. The rest of the hours must be "banked".
4. When an employee requires some time off, they can pull/use hours
from their banked time so that they can still be paid for the time
off.

For example, John Doe works 56 hours in one week. John's "overtime
restriction" is 12 OT Hours/pay period. So John will get paid for 40
regular hours, 12 OT hours, and will "bank" 4 hours in that pay
period. Later that year, John needs part of a day off so he uses his
4 hours of banked time. Unless he has accumulated more hours to this
point, he now has zero banked hours remaining.

The employees time each day is entered into a table called
tblTimeSheet. It records TimeSheetID, EmployeeID, ProjectID,
DateWorked, HoursWorked, and CostCode. In another table called
tblEmployees, I store (among other items) EmployeeID and MaxBankedTime
(the max hours per pay period that can be paid).

What I need is to be able to enter in a date range (on a form) which
could represent a pay period. The result will be the regular hours,
overtime hours, banked time accrued during this pay period, banked
time used during this pay period (which is tracked using a certain
CostCode ie. John used 8 banked hours on 02/01/08 which will have a
costcode of "11"), total banked time accrued for the whole year to
this point, as well as the total banked time remaining from previous
years (because banked time will carry over to the next year if it is
not used).

I am a little stumped as to the best way to accomplish part of this -
mainly how to account for the historical banked time for the previous
years.. Any advice would be greatly appreciated. Thanks in advance.

John

 

[rpw]

My first reaction was that in the US, this method of pay is illegal. My
second thought was that 'John Doe' was getting the short end of the stick - 4
hours of banked overtime should equal 6 hours of time off but you don't say
that (maybe you do reimburse time that way).

Those issues aside, it seems to me that "banked" hours is always a
calculation, never a stored amount, same as overtime should be. I'll offer
suggestions but not actual, usable answers.

"Hours" is the number stored each week.

"Overtime" is a calculation (on a form or report). If ("Hours" minus 40) is
greater than 'overtime restriction' then "Overtime" = 'overtime restriction'
otherwise "Overtime" = ("Hours minus 40).

"Banked" is a also calculation. If ("Hours" minus 40) is greater than
'overtime restriction' then "Banked" = ("Hours" minus 40 minus 'overtime
restriction') otherwise "Banked" = 0

The above calculates for the banked hours.

But what about when those hours are 'used'? Do those hours count towards
the 40 regular hours in the week? They were earned at the overtime rate
(typicaly 1-1/2 times the regular rate). Are they 'reimbursed' as one hour
banked earns 1-1/2 hours off with pay? Is any interest accrued?

 

[J. Trucking]

hi rpw.

Thanks for the suggestions. I think I should probably explain this a
bit more. I'm not from the States so this method of payment is
actually legal (althouh I never was a big fan of it). As far as I
know, the law restricts you on how long the employer can hold onto the
banks time before it must be paid out. If it is paid out in cash, you
get paid out at 1.5 times. If you use the time for time off, its paid
out at 1.0 times. This database isn't for me (as I am not a fan of
this payment system) but your suggestions have put me on the right
path.

Thanks Again,

John

 

[rpw]

Glad to have been helpful. I haven't built a test, but I think you could
build a query with the formulas, then query the query from the form to get
the sums for your YTD numbers for the form's selected record.