# How to Calculate Extra Night in Excel

8
23272

In order to correctly calculate the overtime, it is essential that the administrator has full knowledge of the relevant labor legislation or use a reliable tool to assist him in this task. One of the topics that most requires attention is the night work, which presents some peculiarities that must be well understood within the payroll calculation. Understand now how the extra night works and learn how our Extras-Time Calculation Worksheet can help you track the spreadsheet in your company.

The nightly surcharge is a right guaranteed by the Federal Constitution of 1988, more precisely in Article 7 of Chapter II. Its guidelines are established by the Consolidation of Labor Laws - CLT, the main legislative source of the area in force in Brazil. Firstly, it is important to know that the legislation considers night time, in urban terms, the period from 22: 00 to 05: 00.

Once you know the time interval that falls as night work, the next step is to check what the legislation provides for the hours worked in this period. The CLT stipulates that the remuneration of the night worker must be increased by at least 20% in relation to the daytime hours. Another factor of extreme importance is that the duration of this hour is computed differently. Instead of conventional 60 minutes, the night time, for work purposes, has 52 minutes and 30 seconds. Ultimately, this reduction means that the workday is seven hours, thus compensating for the natural biological wear that the night shift causes in the worker.

## Calculating nighttime overtime

To better understand the calculation of overtime in night work, let's work from a hypothetical situation. In our example, an employee whose journey ranges from 15: 00 to 21: 00 worked, on a given day, up to 23: 00.

When organizing the data related to the hours worked, we have the normal working day, which in this example is six hours, an extra hour worked in the daytime period and an extra hour worked in the night period.

Calculating the overtime worked from 21: 00 to 22: 00 follows the simple calculation system. The time worked from 22: 00 to 23: 00 falls under the rules of night time, in which, as we have seen, the time corresponds to 52 minutes and 30 seconds. Doing the conversion, we realize that the night time equals 1,14286 of the normal time.

To calculate the value, we will follow the following steps:

1. Apply the nightly surcharge of 20% to the value of the time worked;
2. Calculate the overtime based on this value (worked hour + additional night);
3. Finally, apply the night time index equivalent, to get the correct value. That is, multiply the value obtained by 1,14286. With this, we will get the effective value of 22 worked overtime: 00 to 23: 00, in this example.

## How to perform an efficient overtime control

Initially, in the "general premises" tab, employees are recorded, detailing their base salary and working hours, as well as defining the percentage applied for each type of overtime and how these hours are paid. This information must be accurately posted so that the calculations are performed correctly.

In the second tab, "monthly launches", should be recorded the working day effectively performed by the employees, individually, every day. We can see, in the right part of the image, that the worksheet automatically gives a summary of the hours worked in the month, including the amount due, according to the information recorded in the previous tab. In the case of night time, the conversion is also done automatically.

The other tabs are focused on information management, providing consolidated results, charts and reports, and issuing alerts and tips from the numbers obtained.

Overtime control is very important in the management of a company's human resources, and proper enforcement of legal provisions, such as evening overtime, must be followed to the letter. Meet our spreadsheet 3.0 and avoid problems in your company!

Administrator and turismologist, passionate about the possibilities that Excel gives to managers, entrepreneurs and students. He is the managing partner of the spreadsheet department at LUZ - Spreadsheets (luz.vc) and is responsible for the development of spreadsheets with business and personal focus commercialized in its website. He also teaches online Excel courses (cursos.luz.vc) and likes to help thousands of planners through the LUZ Forum and Blog.

1. Hi Roberto, I'm no expert, but if you have 1 slack a week, it looks like a scale of 6 × 1

2. Hello, I have some doubts:
A new company determined the following journey: from 06: 00 to 14: 00 with 1 slack in the week and 1 Sunday yes and another not on slack in the month? Would it be a 5 × 1 or 5 × 2 scale?
In the hours clearing agreement I have to put only: from 06: 00 to 14: 00 scale 5 × 1?
I await response.
That,

Roberto

3. Hi Jean, this type of formula is usually very complex and depends on the control structure you use, so it's difficult to show an example here

4. Good afternoon, great tips for beginners this helped me a lot when I started to work with calculations, but it would be very interesting if you teach a form / formula of excel in disseminating the daylight hours of the night hours, so it would greatly speed up the work, to observe in the calculation the totals of the nocturnal hrs to make the reduction of the nocturnal hour, of there would be only a work ...

Thank you!

Note: Thanks again if you have a solution!

5. Hi Luis, this spreadsheet really is very complex and as the rules for accounting for nighttime extra are very specific, it ends up generating a difficulty for its calculation automatically according to the entered values.

Have you reported this problem to our customer service team? If so, perfect, as this is the best way to correct any error that we did not detect at first. If you haven’t done so, just tell us here or by our support email ([Email protected]) OK? Hugs

6. Worksheet does not make the correct calculation of the nighttime surcharge as well as gets hampered in calculating hours when the workday starts one day and ends and another.

7. I believe there was a mistake in the orientation information of the overtime calculation (item 03). The correct one is to multiply by 1,14286 ... and not multiply by 1,4286 ...