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

We will use the basic information for the working days and hours of a random employee in a company.

Step 1 – Prepare the Data Set with Additional Information

Make a data table like the following image with all the necessary fields. You will need seven rows, one for each day of the week.

Step 2 – Set the Workdays and Weekend

In column B under the Day header, insert the name of the days serially.

Fill the Date column with dates respective to the days in column B.

Select the cell range D11:H11 and go to the Home tab of the ribbon.
From the Alignment group, select Merge & Center.

Format the merged cell however you want it and insert “Weekend” into it.

If the company weekend is on another day, repeat the process for that day.

Step 3 – Provide the Necessary Clock In Information

Select the cell range D5:H10 (cells that don’t contain “Weekend”) and go to the Home tab of the ribbon.
From the Numbers group, select the little arrow at the lower right side of the group.

In the Format Cells dialog box, go to the Custom tab.
Under Type, select h:mm as the format.
Press OK.

Type the clock-in time for the first day in cell D5.

Insert the lunch break duration in the following cell (40 minutes every day in our sample).

Enter the clock-out time in the next cell.

Enter the overtime done by the employee.

Fill up the times for the other days in the week.

Step 4 – Determine Working Hours

Insert the following formula in cell H5.

=F5-D5+G5

Press Enter.
Use AutoFill to fill the column with the formula.

Step 5 – The Final Result

Insert the SUM function in cell H13.

=SUM(H5:H10)

Press Enter.
We need to change the format of the cell to show the correct value.

Select cell H13 and go to the Home tab of the ribbon.
From the Numbers group, click on the little arrow at the bottom-right corner.

Go to the Time tab in the dialog box.
Select 37:30:55 as the cell format.
Press OK.

You will see the correct result in cell H13.

Things to Remember

Remember to set the format before entering values. Otherwise, you will not get a correct result.
If you subtract time out from time in, Excel will automatically show ###### as a result.

Download the Practice Workbook

Attendence Sheet.xlsx

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