Salary Deduction Formula in Excel for Late Coming

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.