How to Calculate Extra Night in Excel

Additional Night: Learn how it works and how to use an overtime control worksheet
Excel Spreadsheets

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 control the spreadsheet in your company.

Banner Overtime Calculation Worksheet

Rules of Night Addition

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. In the first place, it should be noted 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. In the end, this reduction makes 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.

Additional Night: Learn how it works and how to use an overtime control worksheet

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.

Additional Night: Learn how it works and how to use an overtime control worksheet

Calculating the overtime worked from 21: 00 to 22: 00 obeys the simple calculation system. Already the period worked from 22: 00 to 23: 00 fits in the rules of the night time, in which, as we have already 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.

Additional Night: Learn how it works and how to use an overtime control worksheet

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 equivalent night time index, 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-over time: 00 to 23: 00, in this example.

Banner Overtime Calculation Worksheet

How to perform an efficient overtime control

To more efficiently control the point record of your employees and the calculation of overtime, we present our spreadsheet 3.0, with great resources to help you with this task.

Additional Night: Learn how it works and how to use an overtime control worksheet

Initially, on 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.

Additional Night: Learn how it works and how to use an overtime control worksheet

In the second tab, "monthly launches", the working day must be recorded by employees individually, each 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 hours, the conversion is also done automatically.

Additional Night: Learn how it works and how to use an overtime control worksheet

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

The control of overtime is very important in the management of a company's human resources, and the correct application of legal devices, such as nighttime add-on, must be strictly followed. Meet our spreadsheet 3.0 and avoid problems in your company!

Banner Overtime Calculation Worksheet

Excel Spreadsheets


  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.


  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 ...


Please, write your comment
Please enter your name