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.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here:

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

## Steps to Create a Salary Deduction Formula for Late Coming in Excel

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.

**Read More:** **Attendance Sheet with Salary in Excel Format (with Easy Steps)**

**📌 **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)`

**Read More:** **Per Day Salary Calculation Formula in Excel (2 Suitable Examples)**

**Similar Readings**

**How to Calculate Income Tax on Salary with Example in Excel****Calculate HRA on Basic Salary in Excel (3 Quick Methods)****How to Calculate DA on Basic Salary in Excel (3 Easy Ways)****Create a Monthly Salary Sheet Format in Excel (with Easy Steps)****How to Create Tally Salary Slip Format in Excel (With Easy Steps)**

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

**Read More:** **How to Calculate Midpoint of Salary Range in Excel (3 Easy Ways)**

**📌 **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)`

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

**Read More:** **How to Calculate Monthly Salary in Excel (with Easy Steps)**

**📌 **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`

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

**Read More:** **How to Calculate Net Salary in Excel (With Easy Steps)**

## 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. You can visit our website, **ExcelDemy**, to get more Excel-related content. Please leave comments, suggestions, or queries if you have any in the comment section below.

**Related Articles**

**How to Calculate Annual Salary in Excel (with Detailed Steps)****How to Model Salary Regression Analysis in Excel (2 Ways)****Leave Salary Calculation in Excel (With Easy Steps)****How to Calculate Prorated Salary in Excel (3 Suitable Ways)****How to Create a Salary Increase Matrix in Excel (With Easy Steps)****Daily Wages Sheet Format in Excel (with Quick Steps)**