Overtime Payroll formula


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!


Who is Participating?
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.
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.

JenneratorAuthor Commented:
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?

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JenneratorAuthor Commented:
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!

Patrick MatthewsCommented:
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.


JenneratorAuthor Commented:
Thanks! I got it worked out.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.