How to Use Formula for Late Attendance in Excel

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:

• 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:

• Press Enter.
• Use the Fill Handle option.

• Finally, you will get the desired result.

Download Practice Workbook

You can download the practice workbook from here.

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