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.
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)
- Third, you will get the result for the cell and then use the Fill Handle option.
- Last, you will get the desired result.
Read More: Make Automated Attendance Sheet in Excel (With Easy Steps)
Similar Readings
- Attendance Sheet in Excel with Formula for Half Day (3 Examples)
- QR Code Attendance Tracking with Excel (with Easy Steps)
- Attendance Sheet with Salary in Excel Format (with Easy Steps)
- How to Track Attendance in Excel (with Detailed Steps)
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.
- 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.