Overtime Payroll formula

Posted on 2006-04-06
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

    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

    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

    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!

    LVL 92

    Expert Comment

    by:Patrick Matthews
    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

    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

    Thanks! I got it worked out.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now