How to Use Formula for Late Attendance in Excel

Get FREE Advanced Excel Exercises with Solutions!

This tutorial will demonstrate how to use a formula for late attendance in Excel. Every company or educational institution maintains a late attendance sheet. This sheet represents which employee or student enters the workplace within or before the entry time. It not only represents the dedication of the employee or student but also creates a good image for the employee. But if anyone makes a late entry on a daily basis, then sometimes companies deduct the salary of the employee. So, it is essential to learn how to use a formula for late attendance in excel.


Download Practice Workbook

You can download the practice workbook from here.


Step-by-Step Procedures to Use Formula for Late Attendance in Excel

Our main aim is to use a formula for late attendance in excel. There are 3 easy steps for learning the whole process. If you follow the below steps correctly, you should learn how to use the formula for late attendance in excel on your own. The steps are:


Step 1: Arranging Dataset

Our goal is to arrange a dataset for ease of our understanding. In this case, we have the Name in column B, the Starting Time in column C, and the Actual Time in column D. We will gonna use this dataset to describe the whole process. The whole dataset image is below.

Dataset to Use Formula for Late Attendance in Excel

Read More: How to Make Time Attendance Sheet in Excel (2 Easy Ways)


Step 2: Counting Late Time

Now, we want to count the late time attendance by the employees. We will do this by using the IF function. In Microsoft Excel, the IF function is used to check if a condition is met and then the defined statements will be shown based on the given condition. In this article, you’ll learn how you can use this IF function efficiently with different criteria and by combining it with other functions in Excel. You’ll learn more about the dataset as well as the methods to use the IF function properly in the following sections of this article and how to use it to count the late time of the employees.

Steps:

  • First, add a new column naming the Late By in column E.
  • Second, Insert the following formula in the E5 cell.
=IF(D5<C5,0,D5-C5)

Inserting Formula to Use Formula for Late Attendance in Excel

  • Third, you will get the result for the cell and then use the Fill Handle option.

Using Fill Handle to Use Formula for Late Attendance in Excel

  • Last, you will get the desired result.

Read More: Make Automated Attendance Sheet in Excel (With Easy Steps)


Similar Readings


Step 3: Determining Late Points

Next, we want to count the late points of the employees. We will do this by using the combination of the IF and VALUE function. The IF function is used to check if a condition is met and then the defined statements will be shown based on the given condition. Excel provides several text functions to perform your desired text-related tasks easily and swiftly. One of them is a text function called: VALUE. Depending on the circumstances you need to use the VALUE function such as Date to Number, Time to Number, Text to Number, etc. We will use the combination of these two functions to fulfill our goal by following the below steps.

Steps:

  • To begin with, add a new column naming the Late Points in column F.
  • In addition, 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)))))

  • Furthermore, you will get the result for the cell and then 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.

Read More: How to Create Employee Attendance Sheet with Time in Excel


Things to Remember

  • We have to write the formulas properly. If any comma or semicolon isn’t given properly, it won’t give any result.
  • Before starting, we have to decide which formulas we want to use. We have to learn about the formulas and then modify and use them accordingly.
  • It is suggested to download the excel file and use it for better understanding.

Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you to use formulas for late attendance in excel. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.


Related Articles

Zehad Rian Jim
Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo