Md. Araf Bin Jayed
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.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
5 Easy Steps to Create Attendance Sheet with Time in and Out in Excel
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.
Read More: How to Track Attendance in Excel (with Detailed Steps)
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.
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: How to Make Time Attendance Sheet in Excel (2 Easy Ways)
Similar Readings
- Make Automated Attendance Sheet in Excel (With Easy Steps)
- How to Create a Monthly Staff Attendance Sheet in Excel
- Create Employee Attendance Sheet with Time in Excel
- How to Create Student Attendance Sheet in Excel with Formula
- Attendance Sheet in Excel with Formula for Half Day (3 Examples)
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.
Read More: Labour Attendance Sheet Format in Excel (Create with Easy Steps)
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 following SUM function formula 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.
Read More: How to Make Daily Attendance Sheet in Excel (2 Effective Ways)
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.
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.
The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.
Related Articles
- QR Code Attendance Tracking with Excel (with Easy Steps)
- How to Use Formula for Late Attendance in Excel
- How to Prepare a Meeting Attendance Sheet in Excel
- Attendance Sheet with Salary in Excel Format (with Easy Steps)
- How to Create Training Attendance Sheet in Excel
- How to Create Biometric Attendance Report in Excel
