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.

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

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.

Read More: Attendance and Overtime Calculation Sheet in Excel

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.

Read More: Labour Attendance Sheet Format in Excel

Step 4 – Determine Working Hours

• Insert the following formula in cell H5.

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

Step 5 – The Final Result

• Insert the SUM function in cell H13.

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

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

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

Related Articles

• How to Prepare a Meeting Attendance Sheet in Excel
• How to Create Training Attendance Sheet in Excel
• How to Create Biometric Attendance Report in Excel
• Attendance Sheet in Excel with Formula for Half Day
• How to Create Monthly Attendance Sheet in Excel with Formula

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