Weekly Attendance Sheet in Excel with Formula

Gregory Jul 09, 2026

Keeping track of employee attendance is a crucial task for any organization. While there are numerous software solutions available, sometimes a simple and familiar tool like Excel can suffice. In this guide, we'll walk you through creating a weekly attendance sheet in Excel using formulas to automate the process.

Weekly Attendance Tracker in Google Sheets
Weekly Attendance Tracker in Google Sheets

Before we dive into the formulas, let's first set up our sheet with the necessary columns: Employee Name, Date, Present/Absent, and Hours Worked. We'll also include a total row at the bottom to sum up the hours worked for the week.

“Weekly Attendance Tracker – HR Excel Dashboard Template”
“Weekly Attendance Tracker – HR Excel Dashboard Template”

Setting Up the Attendance Sheet

Start by listing your employees' names in the first column. In the second column, input the dates for the week. You can use the Excel AUTO_FILL feature to quickly populate the dates.

Attendance Sheet Template in Excel
Attendance Sheet Template in Excel

In the third column, you'll mark whether an employee is present or absent. You can use 'P' for present and 'A' for absent. In the fourth column, employees or their supervisors can input the hours worked each day.

Automating the Total Hours Worked

Weekly Employee Attendance Sheet, Excel Google Sheets Template (Digital Download)
Weekly Employee Attendance Sheet, Excel Google Sheets Template (Digital Download)

To automatically sum up the hours worked for each employee, use the SUMIF function. In the total row, enter the following formula in the Hours Worked column: `=SUMIF(C2:C7, "P", D2:D7)`. This formula sums up the hours worked (D2:D7) only for the days the employee was present (C2:C7).

Adjust the range (C2:C7, D2:D7) according to your sheet's size. This formula assumes that your data starts from row 2 and you have 6 rows of data (including the total row).

Automating the Grand Total

Download Employee Attendance Sheet Excel Template - MSOfficeGeek
Download Employee Attendance Sheet Excel Template - MSOfficeGeek

To find the grand total of hours worked for the entire week, use the SUM function. In the total row, enter the formula `=SUM(E2:E7)` in the Hours Worked column. This formula sums up the total hours worked for each employee (E2:E7).

Now, whenever an employee's hours are updated, the totals will automatically adjust.

Adding Conditional Formatting for Quick Insights

Student Attendance Record Template For Excel
Student Attendance Record Template For Excel

Conditional formatting can help you quickly identify trends or issues. For instance, you might want to highlight days with low attendance or employees with consistently low hours.

To highlight low attendance days, select the Present/Absent column, go to Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the formula `=COUNTIF($C$2:$C2, "P")<4` and choose the formatting you want. This will highlight days with less than 4 employees present.

Attendance Tracker in Excel
Attendance Tracker in Excel
Attendance tracker templates for Google Sheets
Attendance tracker templates for Google Sheets
“Weekly Attendance Tracker – HR Excel Dashboard Template”
“Weekly Attendance Tracker – HR Excel Dashboard Template”
Fully Automatic Advance Attendance sheet in Excel
Fully Automatic Advance Attendance sheet in Excel
Attendance Sheet Templates - Excel Word Template
Attendance Sheet Templates - Excel Word Template
Employee Attendance Calendar 1728
Employee Attendance Calendar 1728
Easy Attendance Sheet Tutorial
Easy Attendance Sheet Tutorial
Advance ATTENDANCE SHEET in Excel \
Advance ATTENDANCE SHEET in Excel \
the attendance list is shown in this printable sheet for students to practice their work
the attendance list is shown in this printable sheet for students to practice their work
“Weekly Attendance Tracker – HR Excel Dashboard Template”
“Weekly Attendance Tracker – HR Excel Dashboard Template”
Attendance Sheets for Employees - Free Templates & Printable Formats
Attendance Sheets for Employees - Free Templates & Printable Formats
Digital Attendance Template Excel and google sheets Tenure calculation
Digital Attendance Template Excel and google sheets Tenure calculation
How To Make Attendance Sheet In Ms Excel
How To Make Attendance Sheet In Ms Excel
Attendance Tracker Excel | Template Business
Attendance Tracker Excel | Template Business
How To Make Automatic attendance sheet in Excel |
How To Make Automatic attendance sheet in Excel |
Automate Attendance In Excel
Automate Attendance In Excel
Attendance Sheet Template | Free Word Templates
Attendance Sheet Template | Free Word Templates
the printable weekly attendance sheet is shown in this image, it shows an important schedule for
the printable weekly attendance sheet is shown in this image, it shows an important schedule for
Attendance Sheet - Black & White Letter
Attendance Sheet - Black & White Letter
Employee Absence Schedule Template for Excel | Download
Employee Absence Schedule Template for Excel | Download

Highlighting Low Hours

To highlight employees with consistently low hours, select the Hours Worked column, go to Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the formula `=$E2<=$AVG($E$2:$E$7)` and choose the formatting you want. This will highlight hours worked that are less than or equal to the average hours worked for the week.

Remember to adjust the range ($E$2:$E$7) according to your sheet's size.

With these formulas and conditional formatting rules in place, your weekly attendance sheet in Excel is now automated and insightful. Regularly reviewing and updating this sheet will help you maintain a pulse on your team's attendance and productivity.