▷ OVERTIME liquidator in Excel | Day and Night Surcharge 2023

It becomes a confusing topic when it comes time to liquidate the extra hours and nightly surcharges of a worker. Even if you are an employee and you want to know what you are getting paid it is the right thing to do. In this article we are going to solve several doubts about the labor settlement, we will also teach you how to make a table overtime payer in excel.

Are you looking for an Excel overtime adjuster? This is the format needed for your labor calculations. We designed a work template in Excel that makes it easy to calculate overtime, nighttime, daytime, Sundays and holidays surcharges, among other important items of the labor settlement.

We will explain how to calculate overtime in Excel, formulas and how to use the downloadable document format, overtime liquidator format in Excel whose link is at the bottom of this article.

According to the substantive labor code. These are the values ​​to settle overtime and surcharges

In its article 161 of the substantive labor code, it refers to the timely payment of overtime, it also talks about how the price of overtime should be paid on Sundays and holidays. In the table we show you the values.

Description of Extra hour Surcharge per hour Extra daytime: 25% Extra night: 75% Extra Sunday or daytime holiday: 100% (25% + 75%) Extra Sunday or nighttime holiday: 150% (75% + 75%) Overtime table day days of the week

▷ It may be interesting: Excel table for labor settlement by days.

Table of overtime and surcharges 2023

The values ​​included in the overtime liquidator are to calculate extras and surcharges of the current legal minimum wage for 2022.

Description: Minimum Wage 2022WorthDay value$33,333.33Night Surcharge per hour$1,458.33Hours. Daytime Extras$5,208.33Nighttime Extra Hours$7,291.67Daytime Holiday Surcharge per hour$3,125.00Nighttime Holiday Surcharge per hour$4,583.33Table of overtime hours and surcharges 2022

How to make an overtime liquidator in Excel, night, day and holiday surcharges

You can make your own overtime adjuster in Excel following the following parameters, and simple formulas.

1. Include Basic Salary:

Include a box in Excel where you indicate what your basic salary is, not including transportation assistance, commissions and other labor taxes.

Base Salary:$908,256Salary Description

2. Calculate the day value:

In another box, divide the basic salary by 30 days, in this way, you will know the cost of a working day.

Day value: $ 30,275.20Formula: Basic Salary / 30Calculation table of the day in the overtime adjuster in Excel

3. Ordinary Labor hour price:

The price of the hour comes from dividing the basic monthly salary, in 240 ordinary working hours. Ordinary working hours are 8 hours a day, from 6 AM to 9 PM

Ordinary Hour Value$3,784.40Formula: Basic Salary / 240 Table for calculating the value of the hour in the overtime adjuster in Excel

4. Extra night charge per hour:

To find out the nightly surcharge price: multiply the ordinary hour price X 0.35. It is important to know that the night surcharge goes from 9:00 PM to 6:00 AM.

From here From now on we are going to address the ordinary hour value, in this way: VHO

Night Surcharge x Hour: $1,3324.54Formula: VHO * 0.35Night surcharge data table

5. Daytime overtime:

The cost of the extra daytime hour comes from multiplying the ordinary hour price X 1.25. Daytime overtime is reflected from 6:AM to 9:PM

Daytime Overtime: $4,730.50Formula: VHO * 1.25Table of daytime overtime

6. Nightly Overtime:

The value of the nighttime overtime is the multiplication of the ordinary hour value X 1.75. Remember that: the nightly extra hour is the one that goes from 10: PM to 6: AM.

Overtime Night: $6,622.70Formula: VHO * 1.75Nighttime overtime table

7. Extra hour charge for daytime holidays:

Multiply the value of ordinary time X 0.75

Daytime holiday surcharge per hour: $2,838.30Formula: VHO * 0.75 Overtime value table on holidays

8. Value of the nightly overtime on holidays:

It comes out of multiplying the ordinary hour price X 1.1

Surcharge for Night Holidays X Hour: $4,162.84Formula: VHO * 1.1Table of values ​​of nighttime overtime on public holidays

9. Number of overtime hours worked per month:

After knowing how to calculate each of the items of the overtime adjuster in Excel. Now we are going to average the amount of daytime and nighttime overtime. Note that these data should be known to you.

10. Total value per month X amount of overtime

Let’s make the formula to know the total value of each of the items. It’s simple: multiply the value per hour X number of hours worked per month.

Description (Cell 1) Number of hours worked X month (cell 2) This is the formula Nighttime surcharge $1,324.54 = cell 1 * cell 2 Daytime overtime $ 4,730.50 = cell 1 * cell 2 Extra nighttime surcharge $ 6,622.70 = cell 1 * cell 2 Surcharge for daytime holidays $ 2,838.30 = cell 1 * cell 2 Surcharge for night holidays $ 4,162.84 = cell 1 * cell 2 Table to settle the total for each type of overtime

11. Grand total settlement of labor overtime:

It comes out of adding all the items described and related in the overtime adjuster in Excel.

Description (Cell 1) Number of hours worked X month (cell 2) Totals Nighttime surcharge $1,324.54 = cell 1 * cell 2 Daytime overtime $ 4,730.50 = cell 1 * cell 2 Nighttime extra surcharge $ 6,622.70 = cell 1 * cell 2Daytime holiday surcharge$2,838.30 = cell 1 * cell 2Nighttime holiday surcharge$4,162.84 = cell 1 * cell 2Table to total overtime settlement

▷ Important: Table with the formulas to liquidate layoffs.

Example of the overtime adjuster in Excel

If you followed the step by step of what is indicated to design the overtime template in Excel, daytime nighttime surcharges and the price of holidays and Sundays. you will have something like this:

How to make the settlement of overtime in the sample Excel template?

The main idea was for you to learn how to make your own template in Excel to settle overtime and surcharges. But if you don’t have time We advance the work for you.

Download the format overtime adjuster in Excel and surcharges, this example already has the formulas of a labor calculator for overtime settlement, night and day surcharges. Write in the blank boxes (monthly basic salary and number of hours worked per month) ready to print!