contador web Saltar al contenido

Time control in Excel: how to do hoursheet | Productivity

Excel allows you to create a spreadsheet to enter hours worked and make time control. To do this, simply record the arrival, lunch, return and departure times of the company. In the calculations, it is also possible to find out if the person is working overtime or owes hours, to count breaks or absences. In the following tutorial, learn how to assemble a point control spreadsheet and calculate hours worked in Excel. The procedure was performed in the latest version of Office 2016, but the tips are also valid for users of previous editions of the Microsoft program.

How to calculate overtime in Excel

Learn how to create a point control spreadsheet in Excel Photo: Divulgao / MicrosoftLearn how to create a point control spreadsheet in Excel Photo: Divulgao / Microsoft

Learn how to create a point control spreadsheet in Excel Photo: Divulgao / Microsoft

Want to buy cell phones, TV and other discounted products? Meet Compare TechTudo

Step 1. Set up a table like the example in the image below. You must register, at a minimum, the time of arrival, departure for lunch, return of lunch and end of working hours;

Sample point control table Photo: Reproduo / Helito BeggioraSample point control table Photo: Reproduo / Helito Beggiora

Sample point control table Photo: Reproduo / Helito Beggiora

Step 2. To calculate the total hours worked in the day you must add the two shifts with the hours worked in the morning and after lunch. Check the example in the formula of the image;

Calculating hours worked Photo: Reproduo / Helito BeggioraCalculating hours worked Photo: Reproduo / Helito Beggiora

Calculating hours worked Photo: Reproduo / Helito Beggiora

Step 3. If you want to calculate overtime, create a new column with daily office hours. In a new column, decrease the number of hours worked from the number of hours you work, as shown in the image;

Calculating overtime Photo: Reproduo / Helito BeggioraCalculating overtime Photo: Reproduo / Helito Beggiora

Calculating overtime Photo: Reproduo / Helito Beggiora

Step 4. To know the number of hours due, the inverse calculation: decrease the working hours of the hours worked. Excel automatically disregards negative hour calculations.

Calculating hours due Photo: Reproduo / Helito BeggioraCalculating hours due Photo: Reproduo / Helito Beggiora

Calculating hours due Photo: Reproduo / Helito Beggiora

Ready! Take advantage of the tips for creating a time tracking worksheet to calculate your employees' working hours in Excel.

What is the Excel formula for savings account? Find out in the TechTudo forum.

How to customize an Excel spreadsheet

How to customize an Excel spreadsheet