Any employee’s employment contract stipulates their contracted work hours and provides the scope of their duties during those hours. Any time beyond the contractual hours of work is referred to as overtime. Since employees perform their duties in return for compensation, these hours should be recorded to help the employer calculate the compensation the employee is entitled to. These records are made using an overtime sheet.
An overtime sheet is a document that records the time and number of hours an employee works beyond their regular working hours.
Employees may be required to stay beyond their regular working hours to complete a project or other tasks. Employees are often compensated at double rates for working these extra hours, which is why they need to keep records. The records will be used to calculate how much they should be paid. This article discusses the current regulations that have been prescribed over time and how they work. It also shows you how to make your overtime sheet in MS Excel.
Overtime Sheet Templates
If you work for a company with an overtime policy, you probably constantly need to use overtime sheets. However, you may find it challenging to prepare them from scratch each time they are needed. To make it easier for you, you should use the templates provided here. These templates can be edited and used without making a sheet from scratch, thus helping you save time. Below are free-to-download and editable templates that you can use for ease.
Employee Pay and Overtime Regulations
The Department of Labor, under the Federal Government, provides regulations that all employees are entitled to overtime for extra work hours. This stipulation is standard for employees working on a wage basis and extends to salaried employees. The Fair Labor Standards Act (FLSA) defines exempt employees as executives, administrators, or professionals who are not entitled to such a minimum wage as they are paid on a salary basis. However, this definition is limited to an employee’s contract stipulations.
The FLSA guides the wage and hour division of the US Department of Labor by providing overtime rules and regulations. The Act also provides for minimum wages and child labor. However, some states provide stricter laws compared to those in the FLSA; you need to review the laws of your state when calculating extra hours.
The exemption afforded to salaried and other employees who are not entitled is because of the nature of their work. According to the FLSA, you can identify whether an employee is paid minimum wage by looking at the stipulations of their employment contract. Further, their recognition as exempted employees depends on the enforcement of the rules by the employer.
On January 1, 2020, the Department of Labor began enforcing new rules. The new rules increased the number of employees entitled to such payments by stating that employees earning $684 per week or less are entitled, compared to the previous limit of $455 per week. This increment affects workers earning $35,568 annually. These employees must be compensated if they work more than 40 hours a week. Further, employees earning over $684 per week are classified as highly compensated employees (HCEs). As a result, these HCEs’ annual compensation has increased from $100,000 to $107,432 annually.
note
For audits, employers must keep records of all compensation paid to their employees, including overtime. Further, all the records must be complete according to FLSA standards.
How does Overtime Pay Work?
Overtime is paid at a higher rate than regular hours. It is typically paid when an employee works more than their assigned number of hours per week. It is calculated by multiplying the number of hours by 1.5 of the amount of hourly pay. This compensation is higher than average hours because it is for work beyond an employee’s contractual hours.
How do I calculate overtime?
Since there are two categories of employees: hourly and exempt, it is important to know how to calculate overtime for both. Hourly employees are paid for every hour of work; as such, it is easier to calculate their dues.
EXAMPLE
If an employee works 45 hours weekly, their weekly pay should include the normal 40 hours and the additional five hours. If the employee earns $14 per hour, their total pay for the week will be:
- Normal hourly pay ($14) * 40 hours = $560
- Overtime hours (5 hours) *normal hourly pay ($14) *1.5 = $105
- Total pay for the week ($560+ $105) = $665
For exempt employees earning less than $684 per week, it can be calculated in two ways. In the first method, the employee’s annual pay is divided by 2080 hours to determine their hourly pay. The 2080 hours consist of 50 work weeks and two vacation weeks.
EXAMPLE
If an employee earns $32,000 annually, their hourly pay will be calculated by dividing their yearly pay by 2080 hours, i.e., $32,000 ÷ 2080 = $15.38/hour.
In the second method, hourly pay is determined using an employee’s weekly pay. The weekly pay is divided by the number of normal working hours that should be fulfilled per week.
EXAMPLE
If an employee earns $550 per week working 38 hours per week, their hourly pay can be determined as follows:
- $550 ÷ 38 hours = $14.47/hour.
This amount can then be used to determine the employee’s overtime pay.
How do I Create an Overtime Sheet in MS Excel?
You can create your overtime sheet on MS Excel if you know its basics. The following steps will guide you through creating one for five workers, all earning the same hourly rate (assuming they all earn $15.00 per hour).
Step 1
Open a blank Excel sheet by typing the word ‘excel’ on your search bar and selecting ‘blank sheet’ to open your Excel sheet.
Step 2
Once you open the new sheet, select cells; A1 will be the employee name column, B1 will be the pay rate column, and C1 will indicate Monday as the first day of the work week. You should then select C1 to G1 and autofill them to complete the rest of the week.
Step 3
Once you indicate the columns, you should fill in all the details, including the employees’ names, pay rates, and daily hours worked. Since all the employees earn the same amount per hour, select cell B2, where you will indicate the hourly pay, and highlight cells B2 to B6 to autofill the rest of the employees’ pay.
Step 4
Once you fill in the details, you should calculate the total hours worked by each employee for the workweek. This total will be used to calculate overtime hours. You must create the function on the function bar to calculate the total. In this case, it will be a sum of the daily pay. Select cell H2 and create the function; = SUM (C2-G2) will automatically give you the sum of Mary’s weekly hours. You can use autofill for the rest of the employees by dragging your cursor from H2 to H6.
Step 5
Now that you have your total hours, you should calculate how many hours they have worked compared to 40 hours per week. This information should be indicated in cell I1 (Normal hours). You will use an ‘IF’ function, i.e., IF =IF(12<=40,40, I2-40).
Step 6
Calculate overtime hours by creating an ‘IF’ function, i.e., =IF(H2=40, H2>=40, H2-40); essentially, if the total work hours (H1) are equal to 40, then the value to be indicated is 40, however, if the total work hours are more than 40, then the value indicated should be the difference between the value in H1 and 40 (regular work hours).
Step 7
Calculate the total pay once you have all the values. You should begin by calculating the standard pay, the overtime pay, and the sum of the standard and overtime pay. Select cell K1, where you will indicate the standard pay.
Since each employee earns $15 as standard pay for the 40 hours, the function used will be B2*40, as shown below:
You should then select cell L1, where you will indicate overtime pay. For example, Mary’s weekly overtime will be the product of B2*J2*1.5. Your table should now appear as shown below:
Once you have all your values, you can calculate the total pay for each employee by adding their standard pay and overtime pay, i.e., adding the values in K2 to L2 to calculate Mary’s total pay. For each of the steps in this section, you can autofill each column by highlighting it and selecting the autofill tab once you input the formula.
Once satisfied with your details, you may format your overtime sheet by changing the colors of each column or row by highlighting the column and selecting the color tab at the top menu. You can then save your template for future use.
Final Thoughts
Overtime pay is a legal compliance issue; failing to pay non-exempt employees their dues could result in legal liability. Therefore, you should know how to calculate it and keep employee records. The first step in calculating overtime is identifying whether an employee is exempted; this can be established by reviewing the employee’s contract and comparing it to the FLSA. There are many methods of calculating it; as described above, it is advisable to select a standard method. A standard method makes it easier to calculate overtime and ensures the records are streamlined, especially if the company needs to be audited. Further, it is advisable to have one format for the uniformity of the collection of information.
About This Article
