# Salary Deduction Formula in Excel for Late Coming – 5 Steps

### Step 1 – Create a Dataset and a Layout Template

• Create 3 columns to input dates, joining time, and leaving time.
• Add two columns to enter the value of  Late time in time format and in minutes only.
• Create one more column to see the salary deduction amount.
• Assign cells for the salary amount, workdays per month, and work hours per day to calculate the salary per minute.
• Specify the company rule of joining time and leaving time.

• Enter the data to calculate the salary deduction for late coming.

### Step 2 – Calculate the per Minute Salary of an Employee

• Divide the salary amount by the workdays and the work time per day in minutes.
• Enter the following formula in J7:
`=J4/(J5*8*60)`

### Step 3 – Calculate Late Time for Each Day

• Enter the following formula in E5:
`=IF(C5<\$J\$9,0,C5-\$J\$9)`

Formula Breakdown:

The IF function inserts late time only, not the early entrance.

Syntax :

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

• Logical_test = C5 < \$J\$9: selects times that have crossed the joining time given in J9.
• Value_if_true = 0: If the joining time is before 9:00 AM, it returns 0.
• value_if_false = C5-\$J\$9: If the joining time crosses 9:00 AM, it subtracts J9 from C5.

• Drag the Fill Handle icon to paste the formula to the other cells or use Ctrl+C and Ctrl+V to copy and paste.

### Step 4 – Calculate Late Time in Minutes

• Enter the following formula in F5:
`=HOUR(E5)*60+MINUTE(E5)`
The HOUR function extracts the hour value from E5 and multiplies it by 60 to get the minutes The MINUTE function extracts the minute value and adds it to the previous minutes. The total late time is calculated in minutes.

• Drag the Fill Handle to autofill the column.

### Step 5 – Calculate the Salary Deduction for Late Coming

• Multiply the late minutes by the salary per minute.
• Enter the following formula in G5:
`=F5*\$J\$7`
You must use an Absolute Referencing in J7.

• Drag the Fill Handle to autofill the column.
• You can use the SUM function to calculate the total salary deduction

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

Get FREE Advanced Excel Exercises with Solutions!
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF