How to Create Attendance Sheet with Time in and Out in Excel

Attendance sheet is a very important feature for an organization or institution. It helps to keep track of employee performance. Creating an attendance sheet with time in and out in Excel is quite easy and simple. By following some format and simple formulas, you can create an attendance sheet in Excel. In this article, I will show you how to create an attendance sheet with time in and out in Excel.

How to Create Attendance Sheet with Time in and Out in Excel: with Easy Steps

In this article, you will see five easy steps to create an attendance sheet with time in and out in Excel. I am dividing the entire procedure into these steps for a better understanding.

To illustrate my article, further, I will use the following sample data set. Here, I have the basic information of working days and hours regarding a random employee of a company.

Step 1: Prepare Data Set with Additional Information

In the first step, I will prepare the primary data set with all the additional information. To do that,

First of all, make a data table like the following image with all the necessary fields.
Then, name the column as per your convenience and proceed to the second step.

Step 2: Set Workdays and Weekend

I will set the workdays and weekends for the attendance sheet in this second step. Here, I will not use any formula to set them.

Firstly, in column B under the Day header, insert the name of the days serially.
Here, remember to start the week with Monday and the last day in this serial will be Sunday.

Secondly, fill the Date column with dates respective to the days in column B.

Thirdly, to set the weekend, select cell range D11:H11and go to the Home tab of the ribbon.
Then, from the Alignment group, select Merge & Center.

Finally, format the merged cells as per your choice and set the weekend in the attendance sheet.

Step 3: Provide Necessary Information Regarding Time

The third step of this procedure will show you how to insert time into the sheet and how to format it. You need to have the proper format before inserting time for further calculation.

First of all, select cell range D5:H10 and go to the Home tab of the ribbon.
Then, from the Numbers group, select the little arrow at the lower right side of the group.

Secondly, in the Format Cells dialog box, go to the Custom tab after the previous step.
Then, under the Type header, select h:mm as the format of the selected cell range.
Lastly, press OK.

Thirdly, type the entry time or time in for the first day in cell D5.

Fourthly, provide the lunch break in the following cell which is 40 minutes every day.

Fifthly, provide the exit time or time out in the next cell of the same row.

Consequently, provide the amount of overtime done by the employee if required.

Finally, fill up the entire sheet for all the days with the necessary time just like in the following image.

Step 4: Determine Working Hours

In the fourth step, I will show you how to determine the working hours for each day with the available information from the previous steps. Here, you will have to do arithmetic calculations to determine those hours.

Firstly, to calculate the working hours for Monday, insert the following formula in cell H5.
Here, I will subtract the entry time or time in from the exit time or time out and then add the respective overtime to calculate the working hours.

=F5-D5+G5

Secondly, press Enter to see the desired value.
In order to get the values for the lower cells, use AutoFill.

Step 5: Show Final Result

Lastly, it is time to see the final result of this procedure. In order to do that, I will need to apply the formula and need some formatting of the cell.

In the beginning, to calculate the total working hours, insert the SUM function in cell H13.

=SUM(H5:H10)

Secondly, to get the value press Enter, but the format of the value is not right here.
So, I need to change the format of the cell to show the right answer.

Thirdly, to do that, select cell H13 and go to the Home tab of the ribbon.
Then, again from the Numbers group, click on the little arrow at the lower right side of the group.

Fourthly, go to the Time tab in the dialog box.
Then, under the Type heading select 37:30:55 as the cell format.
Lastly, press OK.

Finally, after the previous step, you will see the correct result in cell H13.

Things to Remember

While entering the time and date in the cells, remember to set the format before entering. Otherwise, you will not get a correct result.
If you mistakenly, subtract time out from time in then Excel will automatically show ###### as a result.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Attendence Sheet.xlsx

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to create an attendance sheet with time in and out in Excel. Please share any further queries or recommendations with us in the comments section below.

<< Go Back to Employee Attendance Sheet Excel | Excel HR Templates | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Cynthia Mar 2, 2024 at 12:25 AM

Am so happy to contact please I need help for formula
Attendance sheet for many employees and many shift
I have to check in/check out and break in /break out
How to calculate hour late in the morning or over Time in excel sheet for week or monthly
- Reply
  Lutfor Rahman Shimanto Mar 10, 2024 at 10:18 AM
  Dear CYNTHIA,
  
  Thanks for reaching us. I understand that you want to create attendance sheets for many employees and shifts, including check-in/check-out time, break-in/break-out time, hours late in the morning, and overtime hours.
  
  Although the current article includes an attendance sheet for only 1 employee, our site contains multiple other articles that can help you to fulfill your requirements. Here are some recommended articles for creating an attendance sheet with your given requirements:
  Attendance Sheet Format in Excel
  
  This article contains the steps to create a weekly attendance sheet for many employees with check-in time, check-out time, and total work hours. You can apply the steps to create a weekly attendance sheet for each shift and each month.
  
  Attendance and Overtime Calculation Sheet in Excel
  
  This article shows how to calculate overtime and overtime pay for any employee. You can use the formulas in this article to include overtime in the weekly attendance sheet for each sheet and each month.
  
  Create Employee Attendance Sheet with Time in Excel
  This article shows how to include break time in an attendance sheet in Excel.
  
  For the remaining requirement, the hours late value in the morning, you can add a column after the check-in time column and simply subtract the check-in time value from the advised check-in time (use absolute cell reference if it is specified in a single cell).
  
  I hope you will be able to develop your required attendance sheet with the help of the above-mentioned articles. Let us know your feedback.
  
  Regards,
  
  Seemanto Saha
  
  Team ExcelDemy