Overtime calculation in a Payroll/Billing db

Topic

New Reply

I have a payroll & billing database for security officers & other employees that work multiple locations with a table for hours worked (from time and a to time are listed) for each employee by date

Hours Worked Table

and a query which calculates the number of hours worked from the table.

Timesheet for Payroll

We are based off of a bi-weekly pay period, but If an employee has over 40 hrs in one week they would receive overtime (OT).

I need to calculate the OT received per day if they have reached the 40 hours for the week (Sat – Sun), for billing purposes.

Reply | Quote

Replies

Hi!

It’s not clear if the employeee is entitled to overtime based on daily or weekly rates, e.g. does overtime worked on a Sunday get paid at a greater rate that overtime worked on a Tuesday. I have assumed different rates.
Try thinking of this problem in spreadsheet terms. Entries would be grouped per week, and the hours worked totalled. If the total hours for the week equals or exceeds 40, then for each day of that week one column would show the hours worked, the next would show the normal hours for that day, a third would show the difference (Overtime). This could then be multiplied by the appropriate overtime rate to give an amount due.

All of this can be worked out using queries, and Append/Update queries to write the results to a table.

This is very simplistic, and in real life you would probably group the normal weekday hours together rather than show them by day.

But….. there are a myriad of payroll systems out there, that can handle these calculations. Might you be better using an off-the-shelf solution rather than reinventing the wheel?

Regards,

Jules

Reply | Quote