Overtime Sheet: How to Make + Free Templates for Excel

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, overtime hours should be recorded to help the employer calculate the compensation the employee is entitled to. The records of overtime 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 for overtime. This article discusses the current regulations prescribing over time and how they work. It also shows you how to make your overtime sheet on MS Excel.

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 overtime or 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 wage and child labor. However, some states provide stricter laws on overtime compared to those in the FLSA; as such, you need to review the laws of your state when calculating overtime.

The exemption afforded to salaried and other employees not entitled to overtime 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.

From January 1, 2020, the Department of Labour began enforcing new overtime rules. The new rules increased the number of employees entitled to overtime 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 for overtime 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 audit purposes, 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 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. Overtime 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 to 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 overtime dues. For example:

If an employee works 45 hours weekly, their weekly pay should include the normal 40 hours and the additional overtime for the extra 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

Overtime for exempt employees earning less than $684 per week 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. For 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. For 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 to Create an Overtime Sheet on MS Excel?

You can create your overtime sheet on MS Excel if you know its basics. The following steps will guide you through creating an overtime sheet 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.

Creating an Overtime Sheet on MS Excel Step 01

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.

Creating an Overtime Sheet on MS Excel Step 02

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.

Creating an Overtime Sheet on MS Excel Step 03

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.

Creating an Overtime Sheet on MS Excel Step 04

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).

Creating an Overtime Sheet on MS Excel Step 05

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).

Creating an Overtime Sheet on MS Excel Step 06

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:

Creating an Overtime Sheet on MS Excel Step 07

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., add 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.

Overtime Sheet Templates

If you work in 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 overtime sheet templates. These templates can be edited and used without creating a sheet from scratch, thus helping you save time. Below are free-to-download and editable overtime sheet templates that you can use for your ease.

daily timesheet excel formula

excel timesheet formula with lunch break

monthly overtime sheet template excel free download

excel timesheet template with formulas

free excel timesheet template multiple employees

weekly timesheet template excel free download

project timesheet template excel

excel monthly timesheet template with formulas

free excel timesheet template multiple employees

daily timesheet template

project timesheet template excel

monthly overtime sheet template excel free download

overtime sheet pdf

overtime sheet format in word

excel timesheet template with formulas

weekly timesheet template excel free download

    Final Thoughts

    Overtime pay is a legal compliance issue; failing to pay non-exempted employees their dues could result in legal liability. Therefore, you should know how to calculate overtime and keep employee overtime 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 overtime; 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 overtime sheet for uniformity of collection of information.

    Keep Reading