Extra Time Calculation: how to do it using Excel

Surely you've heard about overtime worked, haven't you? Making adequate control over the dates and hours worked is essential. But do you know exactly how to perform your calculation?

In this article, I'll explain how to calculate overtime, in addition to providing you with a spreadsheet to optimize your results.

Calculating overtime is essential for any company, which is why it is the subject of our post today. Prepared?

Point Registration with Extra Time Calculation

This registration is the way used by most companies to record the entry and exit times of their employees. Taking advantage of this database to calculate your overtime, also considering holidays and weekends.

In addition to explaining what it is and how to calculate overtime, this article aims to present you with a spreadsheet in order to help you control employee points, making it easier to control this information with the automation of most calculations.

Extra time calculation: Understand the tool

The homepage is the starting point for calculating overtime and has the structure shown below.

Use the buttons to navigate between the tabs by just clicking on the buttons that direct you to the respective tab.

When you click on the arrows located in the upper left corner of each tab, you return to the home page.

Extra hour calculation: General Information

The following image represents the general information tab of the worksheet. It should include information from the user regarding the name, position and monthly salary of the employee.

The year of analysis, weekly hours, Sundays and holidays should also be inserted, in addition to the increase of at least 50% of the hour worked.

This spreadsheet already is based on the 50% increase, however, this value may vary from company to company. As already mentioned, this value is only the minimum value.

It is important to highlight that there should be a spreadsheet of this for each employee of the company. So feel free to copy it.

Extra time calculation: Monthly Point Registration

For each month there is a tab, totaling 12 different tabs. The overview of this tab is given as follows:

Columns from D to H are the only ones that need to be filled in the others contain formulas that calculate the total hours worked and overtime.

Note that in columns D and H the user must record the employee's entry times, while columns E and G are the departure times, for each day of work performed.

In this part of the spreadsheet, the hours per day are inserted, and at the end, we have the value of hours worked in the month. Thus, the value of the night overtime is already calculated and the value of the salary can also be obtained.

If the employee works part-time, for example, the user can leave the time fields blank, so that the hours are not counted.

If an employee misses work for some unjustified reason, 0 hours can be inserted in the entries and exits that the total day will be counted in the working day for this employee in the field of negative hours.

In the columns of total hours and negative hours, respectively, the total hours worked and the total negative hours (if any) appear.

It is important to highlight that times must be filled in as time format (8:00) if the user types 8 only the spreadsheet will not recognize the format as time and this can cause errors in the calculations.

Still in this spreadsheet, there is a field for filling out the holiday (column H). As shown in the following figure.

Are you tired of missing out on incredible career opportunities because you don't know about the main Excel tools?

So you are in the right place! In the Excel for Beginners course, you will learn to work with data in an agile way.

In addition, the course is available for FREE, just click on the banner button below and embark on this journey of knowledge!

Extra hour calculation: Report

The report tab is nothing more than a summary of all the hours recorded in the months tabs, as shown below:

Negative hours are the hours the employee owed to complete his time bank.

The time bank is the total overtime taken from negative hours. For the calculation of the monthly workday time bank, the period in which the employee made or not overtime is not considered, since an extra hour made on the holiday is more expensive than an hour made during the week.

Note that next to each result of the time bank it is possible to appear two symbols (one positive in green and one X in red).

X indicates that the time is negative, that is, the employee owes working hours. In the example above, in January the employee owes 25 min and in July he owes 6 hours.

Positive symbols indicate that the employee has overtime in the company or that he has no overtime or negative hours. The last group of columns is shown below.

It should be used when you want to perform calculations with overtime values. The previous information is in date format, which is easily viewable.

However, when you want to perform mathematical operations with the values of the Extra Hours (such as the total to be paid, for example), it is easier to use this group of information, because it is already within the appropriate format.

If you made it this far, I have a question for you...

Several times we get lost in so many Excel functions, and we don't know where to start and what to learn. To help you, we created the First Steps eBook in Excel to guide your practice with the tool, as well as how and where to learn about its most complex resources. Learn how to solve everyday problems quickly and efficiently in Excel.

By downloading our eBook, you will learn the first steps to create your spreadsheets and use their basic and essential functions.

Let's get started?