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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?

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!

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.


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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JenneratorAuthor Commented:
Thanks! I got it worked out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.