Overtime Calculator in Excel

To calculate overtime, it is necessary to know the value of the employee's hourly work. This value is obtained by dividing the monthly salary by the total workload. Then, the additional percentage provided for in the legislation or collective agreement is applied.

The 50% overtime rate is the most common and represents an additional 50% on the value of the normal hour. The calculation is done as follows:

• Calculate the value of a normal hour:

  • Monthly salary divided by monthly working hours.

• Multiply the normal hourly rate by 1.5 (i.e. 100% + 50%).

• Multiply by the number of overtime hours worked.

Example: If the salary is R$2,000.00 and the monthly workload is 200 hours, the hourly rate is R$10.00. Overtime with 50% is R$15.00 (R$10.00 x 1.5).

Overtime with a 100% additional payment is commonly applied on holidays or Sundays, depending on collective agreements. The calculation is similar to the previous one, but uses double the value of the normal hour:

• Calculate the value of a normal hour.

• Multiply by 2 (100% + 100%).

• Multiply by the number of overtime hours worked.

Example: With the same salary of R$2,000.00, the value of 100% overtime would be R$20.00.

Night overtime is that which is worked between 10pm and 5am and includes night shift pay. The calculation follows these steps:

• Apply the 20% night shift surcharge to the normal hourly rate.

• Add the percentage of overtime (50% or 100%, as applicable).

• Multiply by the number of nighttime overtime hours worked.

Note: Night time lasts 52 minutes and 30 seconds, reducing the required workload.

Paid Weekly Rest (DSR) is a right for those who work overtime. To calculate:

• Add up the value of overtime worked during the week.

• Divide by the number of business days.

• Multiply by the number of rest days in the week (usually 1).

Example: If 10 hours of overtime were worked for a total value of R$150.00, over 5 working days, the DSR would be R$30.00 (150 / 5 x 1).

Imagine an employee with a salary of R$3,000.00 and a monthly workload of 220 hours. He worked 10 hours of overtime with a 50% bonus.

• Normal hourly rate: R$ 3,000.00 / 220 = R$ 13.64.

• Overtime value: R$ 13.64 x 1.5 = R$ 20.46.

• Total value of overtime: R$ 20.46 x 10 = R$ 204.60.

Every worker hired under the CLT regime is entitled to overtime pay, except:

• Positions of trust, such as managers, who receive compensatory bonuses.

• Self-employed workers.

Yes, the company must pay for overtime hours worked or offer a time bank, as long as this is provided for in a collective agreement or contract. Otherwise, non-payment may result in fines and labor lawsuits.

The following are not considered overtime:

• Time spent traveling, except in cases of transportation provided by the company in hard-to-reach locations.

• Hours allocated for breaks for rest and meals.

The calculation must be done by the company's human resources department or personnel department, using specific tools to avoid errors.

With the 2017 Labor Reform, there were changes such as:

• Regulation of the individual time bank, which can be negotiated directly between the company and the employee.

• Flexible working hours, allowing agreements that meet the needs of both parties.

Knowing these rules is essential for workers and employers to avoid legal problems and ensure compliance with labor laws.