How to Create Employee Attendance Sheet with Time in Excel

An employee attendance sheet is very important for any company or organization to keep track of their employees. Every company has its own employee attendance sheet of different styles. Employee attendance sheets can be of different kinds like daily attendance sheets, weekly attendance sheets, monthly attendance sheets, and yearly attendance sheets. In this article, we will learn how to create employee attendance sheet with time in Excel. Let’s get started!

How to Create Employee Attendance Sheet with Time in Excel: with Easy Steps

In this tutorial, we will learn step-by-step procedures to create an employee attendance sheet with time in Excel. Here we have a sample dataset that contains the Employee ID numbers and Employee Names of a company. Now we will create an employee attendance sheet with time for the first employee (i.e. Mike Adams). In order to do so, follow the steps below.

Step 1: Format Cells

To create an employee attendance sheet with time in Excel, we have to format cells of the dataset first.

First, create a dataset as shown below for an employee with columns Day, Date, Entry Time, Lunch Break, Exit Time, Overtime, and Total. For example, we have created a dataset for Mike Adams in this example.

Second, select cells (B6:B12) >> right-click on your mouse >> click on Format Cells like the image below.

As a result, the Format Cells window will open.
Furthermore, select Custom from the category and select d-mmm-yy from the Type option.
Then, click OK.

Third, select cells (D6:H12) >> right-click on your mouse >> click on Format Cells like the image below.

As a result, the Format Cells window will open.
Furthermore, select Custom from the category and select h:mm from the Type option.
Then, click OK.

Step 2: Assign Date and Day

After formatting cells, we have to assign Dates and Days for particular days of the week.

Subsequently, type the dates in the Date column and type the days in the Day column serially as shown below.

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

To keep track of the employees, we have to allocate Entry, Exit, Overtime, and Lunch Break for the employees.

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

Next, assign all the Entry Time, Lunch Break, Exit Time, and Overtime for a whole week for that employee as shown below.

Step 4: Calculate Total Time

Afterward, we will calculate the total working hours of the employee for specific days by Excel formula.

Now, to calculate the total working hours of a particular day for that employee, select cell H6 and type the following formula:

=F6-D6+G6

Next, press Enter and you will see the total working hours for the day of Mike Adams.

Afterward, select cell H6 and drag the Fill Handle down till cell H11 as shown below.
Hence, it will display the total working hours of other days of Mike Adams.

Step 5: Set Weekend

In order to set weekends for the employee, follow the steps below.

Now, to set weekend, select cells (D12:H12)>> go to Home tab >> click on Merge and Center as shown below.

Consequently, it will merge and center cells (D12:H12) like the below one.

After that, select the merged cell >> click on B to bold the text in that cell >> click on Text Color drop-down >> choose Red Color like the image below.

Furthermore, type Weekend in the merged cell as shown below.

Final output

Finally, you will see an output like the image below where it will display the employee attendance sheet or monthly attendance sheet with time for the employee Mike Adams for a week.

Things to Remember

If you want, you can extend the employee attendance sheet as per your need.
You can also create employee attendance sheets for other employees in the same way.
You can add or less more columns and modify your employee or staff attendance sheet as per your wish.

Download Practice Workbook

You can download the Excel workbook from here.

Create Employee Attendance Sheet with Time.xlsx

Conclusion

Hence, follow the above-described steps. Thus, you can easily learn how to create employee attendance sheet with time in Excel. Hope this will be helpful. Don’t forget to drop your comments, suggestions, or queries in the comment section below.

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

Get FREE Advanced Excel Exercises with Solutions!