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

Get FREE Advanced Excel Exercises with Solutions!

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.

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


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.

Preparing Data Set with Additional Information as An Easy Step to Create Attendance Sheet with Time in and Out in Excel

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


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.

Setting Weekdays and Weekend as An Easy Step to Create Attendance Sheet with Time in and Out in Excel

Read More: Attendance and Overtime Calculation Sheet in Excel


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.

Read More: Labour Attendance Sheet Format in Excel


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

Inserting Fomrula for Determining Working Hours as An Easy Step to Create Attendance Sheet with Time in and Out in Excel

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

Applying SUM Function to Calculate Total Working Hours as An Easy Step to Create Attendance Sheet with Time in and Out in Excel

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

Showing Final Result as An Easy Step to Create Attendance Sheet with Time in and Out in Excel

Read More: How to Create a Monthly Staff Attendance Sheet in Excel


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.


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Araf Bin Jayed
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo