Ready-to-use Employee Attendance Sheet in Excel, OpenOffice Calc & Google Sheets to manage the attendance of 50 employees.
You can save monthly attendance every month just at the click of a button. Moreover, this template generates employee-wise and month-wise reports based on specific criteria. It also consists of 6 printable attendance sheets.
Additionally, this template consists of 11 types of attendance heads to mark the presence or absence of the employee. These heads include sick leaves, casual leaves, earned leaves, maternity leaves, half days, and training leaves. It also allows the user to mark national holidays, festive holidays, and general holidays.
Thus, this template can be helpful to office admins, HR assistants, and owners of small businesses to keep a bird’s eye on employees.
Table of Contents
Additionally, you can also download other HR templates such as Salary Slip Excel Template India, Salary Slip Format UAE, Simple Salary Sheet, and Employee Salary Sheet depending on the company’s requirement.
You can also download a fully automated Payroll Template With Attendance to easily perform all your payroll tasks.
In case, you want to customize any of the above templates feel free to contact us. You can hire us for our services on Fiverr or directly contact us at info@msofficegeek.com.
Employee Attendance Sheet is a fully automated attendance register that records attendance and generates attendance reports based on different criteria.
Record attendance of your employees in just 3 easy steps:
That’s all the user needs to do.
Furthermore, the template consists of two “Save Data” and “Clear All”. At the end of each month, just click on “Save Data” and it will automatically transfer the data to the Yearly Attendance Record sheet.
To record the attendance for the new month, click on “Clear All” and it will clear the contents of the attendance sheet.
Moreover, the template consists of predefined formulas. It automatically counts the number of presents, half-days, and all types of leave for all employees.
You can download this sheet in three different spreadsheet programs; Microsoft Excel, Google Sheets, and OpenOffice Calc. Instructions to use each type of spreadsheet program are given in the below section.
This template consists of 8 sheets: Employee Attendance Register, Yearly Attendance Record, and 6 different types of Printable Attendance Sheets.
Let us understand the working of each template in detail.
Employee Attendance Sheet consists of the following sections: Header Section, Employee Details section, Attendance Section, and Attendance Summary Section.
The header section consists of:
Year: Insert the year manually.
Month: Select the month from the drop-down list.
Date: This row consists of 31 days of the month. It automatically turns grey based on month selection. For example, if you select February, the columns of the last two or three days will turn grey.
Type of Day: This row defines whether it is a working day or a holiday. For each Sunday it automatically turns the type to “H”. Formula used here is=IF(E8=”Sun”, “H”, “W”).
Week Days: Displays the day of the month based on the selection of year and month against each date. Formula used here is =IFERROR(TEXT(DATE($C$6,$C$4,E$6),”ddd”),””).
Each Sunday column fills in orange automatically with the help of conditional formatting.
In case, you want to mark Saturday and Sunday both as a week off then you need to change the conditional formatting by following these steps:
Insert employee ID and employee name. This is a one-time entry and only changes when an employee leaves or a new employee is recruited. The month column is auto-updated based on the selection of the month in the header section.
All the cells in this section consist of a drop-down list. The drop-down list has been created using the Data Validation option. Mark the attendance against each employee by selecting the correct option from the drop-down list.
This dropdown list consists of the following heads:
P
L
H
HD
SL
CL
T
NH
FH
ML
EL
Select “P” for full day Present, “L” for Full day Leave (Unpaid Leave), and “H” for Holiday (Weekends).
“HD” for Half Day Presence. Every company has a different Half-day policy. Some mark for an actual half-day working and some even mark latecomers as a half day.
Select “SL” for Sick Leave. Those leaves that an employer grants an employee in case of illness. Generally, sick leaves per month dependent on the company policy.
Usually, most organizations allow 24 days a year which is 2 days of sick leave per month. If an employer is severely ill then he can observe more in one month. But this should not exceed the total number of sick leaves per year.
Some employers count it monthly and some count it on yearly basis. The employer cannot deduct salary for sick leaves if they are under the allowed limit.
Select “CL” for Casual Leave. Everyone has some unforeseen situations. When an employee observes a leave to handle such an unforeseen situation, it is marked as casual leave.
Generally, the number of leaves varies according to the industry, the employer, and the state in which an employee is located.
Similar to sick leaves, the employer cannot deduct salary for casual leaves if they are under the allowed limit.
Select “T” for Training Leave. Companies provide training to new joining employees or existing employees. Training takes place in-house or at other venues. These leaves are paid subject to training taken by employees for the company.
Select “NH” for National Holidays and “FH” for Festive Holidays. National holidays are those which are observed across the country whereas Festive holidays differ from state to state. Especially in India, there are many festive holidays that are observed only in particular states.
Select “ML” for Maternity Leave. In India, the Labour Law entitles a female employee to take maternity leave up to 12 months. The compensation is paid in full to female employees on maternity leave. Moreover, the employer cannot terminate a female employee from his job observing maternity leave.
Select “EL” for Earned Leave or Paid Leave. Earned Leave is also known as Privilege Leave. It is the long leave that an employee is entitled to observe annual vacation or travel needs. The employee has to priorly inform the employer for observing the Earned Leave.
Usually, the Labour Law entitles an employee to 20 days annually. These leaves differ based on the size of the company, location, and designation of the employee.
Usually, companies allow a maximum of 30 days earned leave per year. You can encash or carry forward the balance earned leaves to next year.
In the end, the columnar total displays the daily strength of present employees based on markings. It shows the total number of employees present on that particular date using the COUNTIF Function.
Attendance Summary displays the total of an employee for each head. Each column here also uses the COUNTIF function to calculate the total attendance under each head.
Total working days are calculated based on the following formulas:
P + L + HD + NH + FH + T + ML + EL + SL + CL
Whereas, Days Payable are calculated based on the following formula:
(P + (HD/2) + NH + FH + T + ML + EL + SL + CL ) – L
Moreover, the formula above divides half-days 2 to get the actual attendance. The template doesn’t count Holidays while calculating total working days as they are weekly day-offs.
The user needs to insert allowed Sick Leaves, Casual Leaves, and Earned Leaves in the summary section in light orange cells. If an employee observes these three types of leaves above the allowed limit, then you need to mark them as Unpaid Leave “L”.
The template consists of two “Save Data” and “Clear All”. At the end of each month, just click on “Save Data” and it will automatically transfer the data to the Yearly Attendance Record sheet.
To record the attendance for the new month, click on “Clear All” and it will clear the contents of the attendance sheet.
This sheet is a replica of the employee attendance sheet. It consists of the same headings. This sheet is auto-populated. Hence, you don’t need to make any entries on this sheet.
Important Note: Openoffice Calc and Google Sheet users need to copy the monthly attendance data manually to the yearly attendance sheet.
In addition to this, you can generate employee-wise, and month-wise reports using the filter option.
Just click on the filter button and select the criteria. See the image below:
Below given are 6 ready-to-use Employee Attendance Sheets. Click on the button below each image to download the respective printable version.
In case you want to enter the names of employee and other relevant details, excel sheets for each printable sheet is given in the template sheet. Just edit according to your need and print them.
Some companies manually log attendance separately for each day. This template is useful when you have multiple departments.
Similar to daily attendance, many companies log for attendance on a weekly basis. Thus, this template can be useful to such companies.
This is the replica of the template. You can use this sheet to manually log attendance for a complete month.
Dual attendance is for those companies who log for attendance twice a day. Usually, this type of attendance is common in the sales industry. This is because sales personnel reports daily evening to their team heads or managers.
You can use this sheet to record the presence of employees at a given meeting.
You can use this to calculate the presence of employees during training.
Frequent unavailability at work is bad attendance. This doesn't include occasional sick leaves instances that cannot be controlled such as illness, heavy rains, etc.
Absenteeism frequent and intentional absence of the employee from work either taking leave for a full day or through time theft. Time theft includes late comings, early departure, and even longer breaks.
Divide the total number of days absent by total working days during a specific period to derive the absenteeism rate of an individual employee.
The Maternity Benefit (Amendment) Act 2017 entitles a female employee to maternity leave 26 weeks. Moreover, it also entitles prenatal leave of eight weeks. This rule differs for those women who already have 2 or more children. The law entitles such women to 12 weeks maternity leave and prenatal leave of 6 weeks. Moreover, this Act allows a woman adoption leave of 12 weeks who adopts a child under the age of three months. Furthermore, the biological mother is entitled to 12-week leave from the date the child is handed over to her. Additionally, a female civil servant is entitled to maternity leave for a period of 180 days for their first two live-born children.
As per the shops and establishment act, you can allow an employee to take 12 sick leave in a calendar year. Moreover, these leave entitlement rules are regulated as per the concerned State's Shops and Establishment Act or Factories Act.
An employee is entitled to a minimum of 0.5 to a maximum of 3 days Casual Leave. If casual leave exceeds these 3 days then they are deducted from Earned Leave entitlement per year. An employee has to take prior approval If he/she plans to avail 3 casual leaves together.
Under the Shops and Establishment Act, an employee can avail of 6 days of casual leave. These leaves are not carried forward and get lapsed at the end of the year. Casual leaves are not encashable.
If you like this article, kindly share it on different social media platforms so that your friends and colleagues can also benefit from the same. Sharing is Caring.
Please send us your queries or suggestions in the comment section below. We will be more than happy to assist you.
