Salary Deduction Formula in Excel for Late Coming (with Example)

There are some quick steps to calculate salary deduction for creating a late coming formula in Excel. This article will walk you through each and every step with appropriate examples. As a result, you can use them easily for your purpose. Let’s move on to the article’s main discussion.

What Are Salary Deduction Rules?

Each company wants to apply and follow some rules and regulations in a company and joining at a fixed time is of great importance to improve efficiency. So, many companies follow strict rules for joining in the specified time. And if anyone fails to join in time then there will be a salary deduction. Some companies follow rules like there will be a one-day salary deduction for every 3 days he/she has been late. Some companies calculate a deduction per each minute that he is late to join. Also, there can be other rules as per company standards and requirements.

Salary Deduction Formula for Late Coming in Excel: Steps to Create

In this section, I will show you the quick and easy steps to calculate salary deduction for a late coming formula in Excel on the Windows operating system. This article contains detailed explanations with clear illustrations for everything. I have used the Microsoft 365 version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.

📌 Step 1: Create a Dataset and Layout Template

First, we have to create a layout where there will be allocated cells to insert the necessary data to calculate the salary deduction for the late entrance.

First, create 3 columns to get input of the dates, joining time, and leaving Then, add two more columns to get the value Late time in time format and in only minutes value, and lastly one more column to get the salary deduction amount.
Along with the previously mentioned data table, you have to insert some more information. You have to assign cells for the salary amount, workdays per month, and work hours per day and thus you can calculate the salary per minute for an employee.
Also, you have to specify the company rule of joining time and leaving time in the template.

After creating the template, insert the necessary data to calculate the salary deduction for late coming.

📌 Step 2: Calculate per Minute Salary of an Employee

Now, we will calculate the per-minute salary of the employee.

For this, we have to divide the salary amount by the workdays and the work time per day in minutes.
So, insert the following formula in cell J7:

=J4/(J5*8*60)

📌 Step 3: Calculate Late Time for Each Day

Now, you have to calculate the late time for each day of the month.

For this, insert the following formula into cell E5:

=IF(C5<$J$9,0,C5-$J$9)

🔎 Formula Breakdown:

Here, the IF function is working to insert only the late time, not the early entrance time.

Syntax of IF function:

=IF(logical_test, [value_if_true], [value_if_false])

Logical_test = C5 < $J$9: This portion denotes to take only those times that have crossed the joining time given in cell J9.
Value_if_true = 0: If the joining time is before 9:00 AM then insert 0.
value_if_false = C5-$J$9: If the joining time crosses 9:00 AM then insert subtract cell J9 from the cell value of C5.

Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.

Thus, you have calculated the late time that the employee joins after the fixed time.

📌 Step 4: Calculate Late Time in Minutes

Now, you will calculate the late minutes from the late time that is in Time format.

For this, use the following formula in cell F5:

=HOUR(E5)*60+MINUTE(E5)

Here, the HOUR function extracts the hour value from cell E5 and the multiply with 60 to get the minutes After that, the MINUTE function extracts the minute value and adds it to the previous minutes. Thus, the total late time is calculated in minutes.

Then, drag the Fill Handle to autofill the column with a similar formula.

📌 Final Step: Calculate Salary Deduction for Late Coming

And, at the last step, you will calculate the amount of deduction from the salary.

For this, you have to multiply the late minutes by the salary per minute of the employee.
So, insert the following formula into cell G5:

=F5*$J$7

You must use Absolute Referencing for cell J7 because you have to keep this fixed as it is while auto-filling the column.

Now, autofill the column to get the salary deduction for each late joining day. Then, you can use the SUM function to calculate the total salary deduction of the month for the employee.

Download Practice Workbook

You can download the practice workbook from here:

Salary Deduction Formula for Late Coming.xlsx

Conclusion

In this article, you have found how to calculate salary deduction for a late coming formula in Excel. I hope you found this article helpful. Please leave comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Salary | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!