Excel Monthly Attendance Sheet with Formula

Walter Jul 15, 2026

Streamlining employee management involves tracking key metrics, one of which is attendance. Microsoft Excel provides an efficient and customizable tool for creating a monthly employee attendance sheet. In this article, we will delve into the process of creating such a sheet, incorporating formula best practices to enhance your tracking capabilities.

Attendance Sheet Template in Excel
Attendance Sheet Template in Excel

Before we dive in, ensure you have a basic understanding of Excel formulas and navigate to Excel on your computer or use the web-based version. Now, let's create an attendance sheet that calculates attendance percentages and average days present per month.

Employee Attendance Tracker | Free Excel Attendance Spread | Updated for 2026 | Leave Board
Employee Attendance Tracker | Free Excel Attendance Spread | Updated for 2026 | Leave Board

Creating the Monthly Employee Attendance Sheet

Start by setting up your sheet with columns for dates, employee names, and corresponding attendance status ( 현주, absent, or on leave).

the information sheet is displayed in this screenshote screen shot from www ms officegeeek com
the information sheet is displayed in this screenshote screen shot from www ms officegeeek com

Assuming your data spans from January 1st to January 31st, and you have 10 employees, your sheet should resemble the following:

DateJohn DoeJane Smith... Employee 10
1/1PA...P
Employee Attendance Calendar 1728
Employee Attendance Calendar 1728

Calculating Absent Days

To track absent days, use a simple formula in a new column labelled "Absent Days". In cell B13 (assuming your data starts from A1), enter the following formula and drag it down:

=IF(B2="A", 1, 0)

Attendance Sheets for Employees - Free Templates & Printable Formats
Attendance Sheets for Employees - Free Templates & Printable Formats

This will return 1 if the employee is absent ("A") and 0 otherwise. The formula calculates the total absent days for each employee.

Calculating Attendance Percentage

In a new column, calculate the attendance percentage. In cell C13, enter the following formula:

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

=ROUND(1 - SUM(B13:B13)/(COUNTIF(A$2:A$11,"<="&TODAY()), 2)

This formula calculates the attendance percentage by subtracting the absent days from the total days worked and dividing by the total working days in the month. The "ROUND" function rounds the result to two decimal places.

Employee Absence Schedule Template for Excel | Download
Employee Absence Schedule Template for Excel | Download
Redirecting...
Redirecting...
“Weekly Attendance Tracker – HR Excel Dashboard Template”
“Weekly Attendance Tracker – HR Excel Dashboard Template”
🔥🔥 Fully Automated Attendance Sheet in Excel
🔥🔥 Fully Automated Attendance Sheet in Excel
Monthly Attendance Sheet Templates |
Monthly Attendance Sheet Templates |
Employee Attendance Sheet in Excel 🔥 | Automatic Monthly System (No Mistakes)
Employee Attendance Sheet in Excel 🔥 | Automatic Monthly System (No Mistakes)
Easy Attendance Sheet Tutorial
Easy Attendance Sheet Tutorial

Using SUMIF to Calculate Average Days Present

To find the average days present per Employee, use the SUMIF function. In a new row below your data, enter the following formulas:

Sum of All Present Days

=SUMIF(B2:K11, "P" )

Total Working Days

=COUNTIF(A$2:A$11, "<="&TODAY())

Average Days Present

aruple impact across your organization, ensuring everyone is well-informed and contributing effectively. This sheet will not only streamline your attendance tracking but also provide valuable insights to optimize your team's productivity.