Michael said:
I am currently paying my employees on a percentage basis. However, my new
payroll company is requesting that I turn in hours for employees.
[....]
How can I calculate the following in excel (if possible).
John Doe made $1,200 at 37% in one week however if I track his hours he
worked 50 hours that week.
What is his pay at 40 hours to find out his overtime.
I think you need to turn to your state's labor laws first to understand
the requirement. Then we might be able to help with the formulation.
For example, for Calif, the following rules apply (see
http://www.dir.ca.gov/dlse/FAQ_Overtime.htm , which may or may not be
current law):
<Begin Quote>
In California, the general overtime provisions are that a nonexempt
employee 18 years of age or older, or any minor employee 16 or 17 years
of age who is not required by law to attend school and is not otherwise
prohibited by law from engaging in the subject work, shall not be
employed more than eight hours in any workday or more than 40 hours in
any workweek unless he or she receives one and one-half times his or
her regular rate of pay for all hours worked over eight hours in any
workday and over 40 hours in the workweek. Eight hours of labor
constitutes a day's work, and employment beyond eight hours in any
workday or more than six days in any workweek is permissible provided
the employee is compensated for the overtime at not less than:
One and one-half times the employee's regular rate or pay for all hours
worked in excess of eight hours up to and including 12 hours in any
workday, and for the first eight hours worked on the seventh
consecutive day of work in a workweek; and
Double the employee's regular rate or pay for all hours worked in
excess of 12 hours in any workday and for all hours worked in excess of
eight on the seventh consecutive day of work in a workweek.
There are, however, a number of exemptions from the overtime law.
[....]
If you are paid by the piece or commission, either of the following
methods may be used to determine the regular rate of pay for purposes
of computing overtime:
The piece or commission rate is used as the regular rate and you are
paid one and one-half this rate for production during the first four
overtime hours in a workday, and double time for all hours worked
beyond 12 in a workday; or
Divide your total earnings for the workweek, including earnings during
overtime hours, by the total hours worked during the workweek,
including the overtime hours. For each overtime hour worked you are
entitled to an additional one-half the regular rate for hours requiring
time and one-half, and to the full rate for hours requiring double
time.
<End Quote>
(IMHO, the last clause should be "to __an_additional__ full rate for
hours requiring double time".)
As you can see, the computation can be quite complicated. But other
states might have much simpler rules.
