contador web Saltar al contenido

How to calculate overtime in Excel | Downloads

Excel allows you to calculate amounts of overtime worked quickly and conveniently. The useful feature, for example, is to set up a spreadsheet to track your hours before or after hours and find out if the company is paying correctly. To do this, simply register the time of arrival, lunch, return and departure of the company. It is also necessary to know the amount paid for each additional hour of work.

READ: Excel used in attack that can stop companies

In the following tutorial, find out how to calculate overtime in Excel from your PC. The procedure was performed on the latest version of Office 2016, but the tips also apply to users of previous editions of the Microsoft program.

Learn how to calculate overtime in Excel by PC Photo: Divulgao / MicrosoftLearn how to calculate overtime in Excel by PC Photo: Divulgao / Microsoft

Learn how to calculate overtime in Excel by PC Photo: Divulgao / Microsoft

Want to buy a cell phone, TV and other discounted products? Meet the Compare TechTudo

Step 1. Set up a table as the example below. You must record the time of arrival, departure for lunch, return of lunch and the end of business hours;

Example overtime control spreadsheet Photo: Reproduction / Helito BeggioraExample overtime control spreadsheet Photo: Reproduction / Helito Beggiora

Example overtime control spreadsheet Photo: Reproduction / Helito Beggiora

Step 2. To know the total working hours in the day you must calculate the hours worked in the morning and after lunch by summing the two shifts. Check out the example in the image formula;

Calculating total hours worked per day Photo: Reproduction / Helito BeggioraCalculating total hours worked per day Photo: Reproduction / Helito Beggiora

Calculating total hours worked per day Photo: Reproduction / Helito Beggiora

Step 3. Create a column and fill in your office hours. To calculate overtime, decrease the amount of hours worked from the number of hours your normal working hours;

Calculating Overtime Photo: Reproduction / Helito BeggioraCalculating Overtime Photo: Reproduction / Helito Beggiora

Calculating Overtime Photo: Reproduction / Helito Beggiora

Step 4. Now create a column with the overtime value. Multiply the amount of overtime by 24 to convert the hours to a decimal number. Finally, multiply by the overtime value. Note that in the last two columns, the formatting of the cell must be set to "Currency".

Calculating overtime value Photo: Reproduction / Helito BeggioraCalculating overtime value Photo: Reproduction / Helito Beggiora

Calculating overtime value Photo: Reproduction / Helito Beggiora

Take advantage of the tips for creating a spreadsheet to calculate your overtime in Excel.

How to delete all blank lines in Excel at once

How to delete all blank lines in Excel at once