Step 1: Arranging Dataset
Put the Name in column B, the Starting Time in column C, and the Actual Time in column D. We will use this dataset throughout.
Step 2: Counting Late Time
- Add a new column named Late By in column E.
- Insert the following formula in the E5 cell:
=IF(D5<C5,0,D5-C5)
- Press Enter.
- Use the Fill Handle option and drag it down.
- This fills the E column.
Step 3: Determining Late Points
Let’s say that employees receive 1 point for being between 5 and 15 minutes late, 2 points for being between 15 and 30 minutes late, 3 points for being late up to an hour, 4 points for being late up to 4 hours, and 5 points beyond that.
Steps:
- Add a new column named Late Points in column F.
- Insert the following formula in the F5 cell:
=IF(E5<VALUE("0:05"),0,
IF(E5<VALUE("0:15"),1,
IF(E5<VALUE("0:30"),2,
IF(E5<VALUE("0:60"),3,
IF(E5<VALUE("4:00"),4,
5)))))
- Press Enter.
- Use the Fill Handle option.
- Finally, you will get the desired result.
How Does the Formula Work?
- VALUE(“4:00”): In the first portion, it represents the value we want to use in the formula.
- IF(E5<VALUE(“4:00”),4,5))): This portion represents the conditions of the formula.
- IF(E5<VALUE(“0:05”),0,IF(E5<VALUE(“0:15”),1,IF(E5<VALUE(“0:30”),2,IF(E5<VALUE(“0:60”),3,IF(E5<VALUE(“4:00”),4,5))))): this portion represents the whole formula with proper conditions.
Download Practice Workbook
You can download the practice workbook from here.
<< Go Back to Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!