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!


Download Practice Workbook

You can download the Excel workbook from here.


Step-by-Step Procedures to Create Employee Attendance Sheet with Time in Excel

In this tutorial, we will learn step-by-step procedures to create 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-by-Step Procedures to Create Employee Attendance Sheet with Time in Excel


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.

Step-by-Step Procedures to Create Employee Attendance Sheet with Time in Excel

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

Step-by-Step Procedures to Create Employee Attendance Sheet with Time in Excel

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

Step-by-Step Procedures to Create Employee Attendance Sheet with Time in Excel

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

Read More: How to Track Attendance in Excel (with Detailed Steps)


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-by-Step Procedures to Create Employee Attendance Sheet with Time in Excel


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.

Step-by-Step Procedures to Create Employee Attendance Sheet with Time in Excel

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

Read More: Attendance and Overtime Calculation Sheet in Excel


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.

Step-by-Step Procedures to Create Employee Attendance Sheet with Time in Excel

  • 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-by-Step Procedures to Create Employee Attendance Sheet with Time in Excel

Read More: How to Make Time Attendance Sheet in Excel (2 Easy Ways)


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.

Step-by-Step Procedures to Create Employee Attendance Sheet with Time in Excel

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

Step-by-Step Procedures to Create Employee Attendance Sheet with Time in Excel

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

Step-by-Step Procedures to Create Employee Attendance Sheet with Time in Excel

  • 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 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 attendance sheet as per your wish.

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. Follow the ExcelDemy website for more articles like this. Don’t forget to drop your comments, suggestions, or queries in the comment section below.


Related Articles

 

 

Md. Asaduzzaman

Md. Asaduzzaman

Hello! I am Md. Asaduzzaman. Currently, I am working as an Excel and VBA Content Developer and I will be posting my articles related to this here. I graduated from Bangladesh University of Science and Technology(BUET) in 2022. I completed my BSc in Naval Architecture and Marine Engineering. I like to solve real-life problems in Microsoft Excel and share the solutions through articles. I post here regularly. Hope you find the articles helpful.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo