How to Create Employee Attendance Sheet with Time in Excel

We have a sample dataset that contains the Employee ID numbers and Employee Names. We will create an employee attendance sheet with time for the first employee (i.e. Mike Adams).

Step 1 – Format Cells

Create a dataset as shown below, with columns Day, Date, Entry Time, Lunch Break, Exit Time, Overtime, and Total. We have created a dataset for Mike Adams in this example.

Select cells (B6:B12) and right-click, then select Format Cells.

The Format Cells window will open. Select Custom from the category and select d-mmm-yy from the Type option.
Click OK.

Select cells D6:H12 and right-click.
Click on Format Cells.

The Format Cells window will open. Select Custom from the category and pick h:mm from the Type option.
Click OK.

Step 2 – Assign the Dates and Days

Type the dates in the Date column and the days in the Day column.

Step 3 – Allocate Entry, Exit, Overtime, and Lunch Break

Type the Entry Time, Lunch Break, Exit Time, and Overtime for a specific date and day of the employee.

Assign all the Entry Time, Lunch Break, Exit Time, and Overtime for the whole week for that employee.

Step 4 – Calculate Total Time

Select cell H6 and insert the following formula:

=F6-D6+G6

Press Enter and you will see the total working hours for the day.

Select cell H6 and drag the Fill Handle down to cell H11.

Step 5 – Set a Weekend

Select cells D12:H12 and go to the Home tab, then click on Merge and Center.

This will merge and center cells D12:H12.

Select the merged cell and click on B from the ribbon to bold the text in that cell.
Click on the Text Color drop-down and choose the Red Color.

Type Weekend in the merged cell as shown below.

Final Output

Here’s the result for the first employee.

Things to Remember

You can extend the employee attendance sheet as needed and create employee attendance sheets for other employees similarly.
You can add or less more columns and modify your employee or staff attendance sheet as per your wish.

Download the Practice Workbook

Create Employee Attendance Sheet with Time.xlsx