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.

Dataset to Use Formula for Late Attendance in Excel


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)

Inserting Formula to Use Formula for Late Attendance in Excel

  • Press Enter.
  • Use the Fill Handle option and drag it down.

Using Fill Handle to Use Formula for Late Attendance in Excel

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

Using Fill Handle to Use Formula for Late Attendance in Excel

  • 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!
Zehad Rian Jim
Zehad Rian Jim

Zehad Rian Jim is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He's good with Microsoft Office and has written more than 80 helpful articles for ExcelDemy in the last year. Now, he makes fun and easy-to-follow Excel tutorials for YouTube as part of the ExcelDemy Video project. Zehad loves figuring out Excel problems, and his passion for learning new things in Excel shines through in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo