### 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)`

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

**Absolute Referencing**in

**J7**.

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

**Download Practice Workbook**

Download the practice workbook here:

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