Overtime Payroll formula

Posted on 2006-04-06
Medium Priority
Last Modified: 2008-03-17

I am created a payroll db and I don't know how to calculate the overtime. Here is what I have so far. I have one table that you enter the employee, date, start time, end time and breaktime amount. The employee's Payrate is stored in the employee table and is pulled into the form or report for the calculations.

It is no problem calculating if the employee works more then 8 hours in a day and increasing their daily salary by 1.5 if the hours worked are more then 8 but less then 12. If they worked more then 12 hours in one day, I have increased their daily salary by 2 (double time). Here are the formulas. I am storing them back into the payroll table so I don't have to re-create the formulas in all the forms and reports.

Private Sub TimeOut_AfterUpdate()
ClockTime = Hour([TimeOut]) - Hour([TimeIn])
StandardHrs = IIf([TTlHoursWrk] < 8, [TTlHoursWrk], 8)

End Sub


Private Sub LessBreak_LostFocus()
TTlHoursWrk = Hour([TimeOut]) - Hour([TimeIn]) - [LessBreak]
StandardHrs = IIf([TTlHoursWrk] < 8, [TTlHoursWrk], 8)
Me![Overtime1-5] = IIf([TTlHoursWrk] < 8, 0, IIf([TTlHoursWrk] > 12, 4, [TTlHoursWrk] - 8))
Me![Overtime2-0] = IIf([TTlHoursWrk] < 12, 0, [TTlHoursWrk] - 12)
Me![DailySalary] = ([PayRate] * Me![StandardHrs]) + (Me![Overtime1-5] * [PayRate] * 1.5) + (Me![Overtime2-0] * [PayRate] * 2)

End Sub

This is all working fine. Now here is my quesion. Lets say the employee worked 40 hours 5 days of 8 hour days. then they worked a 6th day 8 hour day (they should get time and a half on the 6th day). My system would not calculate the overtime on that.

Here is what I need...

Overtime pay 1.5 if 6th day and over 40 hours for the week - up to 12 hours or 7th day and over 40 hours - up to 8 hours.

Overtime Pay 2 (double) 6th day and over 40 hours for the week - all hours above 12th hour or 7th day and over 40 horus for the week all hours over 8th hour.

I have a report that groups by employee then by date and totals all above numbers. How do I incorportate any overtime worked past the 5th day?

I don't know where to start with this so any help is really appriciated!


Question by:Jennerator
LVL 44

Expert Comment

ID: 16392909
are you saying that if an employee worked a Total of 40 hours for the week, but chose to work those as 4 10 hour days, they get overtime for each of those days.  Most companies will not consider overtime until the employee has worked the standard 40 hours for the week.  

It all depends on what the business rules are as applied to overtime.


Author Comment

ID: 16392973
The way it was given to me was...

An employees standard hours can not exceed 8 hours a day or 40 hours per week.
Overtime 1.5 pay
1st-5th day from 8th to 12th hour
6th day and over 40 hours a week - upto 12 hours
7th day and over 40 hours for the week - upto 8 hours

Overtime 2.0 pay
1st - 5th day - all hours above 12th hour
6th day and over 40 hour for the week - all hours above 12th hour
7th day and over 40 hours for the week - all hours over 8th hour

That is exactly what the company gave me for overtime

What do you think?


Author Comment

ID: 16393293
I think I might be on to something. I have added a field called DayofWeek. after the payroll date is inputted, the dayofweek field will be filled in by a formula weekday([payrolldate]). Now I will know what day we are working with. Now is there anyway I can use that information to accomplish this?

Something like...

keep in mind 1 = Saturday.

IIF([dayofweek]=1 and [standardhrs] <8, dailysalary * 1.5)

But that will only work if the employee has already put in 40 hours. I don't know that for sure.

What am I missing?

Any help would be great!

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

LVL 93

Expert Comment

by:Patrick Matthews
ID: 16393507
Hi AW,

> It all depends on what the business rules are as applied to overtime.

Indeed.  The People's Republic of California has laws that sometimes give employees OT rights once they work
>8 hours in a given day, regardless of whether they topped 40 hours for the week as well.

Some union contracts also call for holiday, weekend , and/or OT differentials to apply cumulatively.


LVL 38

Accepted Solution

puppydogbuddy earned 375 total points
ID: 16406687
Business Rule #1 with respect to O/T pay: Avoid overpaying for O/T by treating overtime as a rate differential.  This means that:
1. employee paid straight time for total hours worked  
2. employee given  a .5 rate differential for hours qualifying as O/T at time and a half
3. employee given a 1.0 rate differential for hours quaifying as O/T at double time.

Example: employee making $25 per hour puts in a 48 hour workweek of which 6 hours qualify as time and a half, and 8 hours as double time.

computation of gross pay:
       straight time                  48  X $25                  =  $1,200.00
       O/T Time and a half rate  6  X $25 X   .5         =  $    75.00
       O/T Double Time Rate      8  X $25 X 1.0         =  $   200.00

And if you do the computation as shown above, it  simplifies your program considerably.  You only have 3 buckets to worry about.

Hope his helps.

Author Comment

ID: 16407178
Thanks! I got it worked out.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question