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

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

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

## Related Articles 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 Advanced Excel Exercises with Solutions PDF  