Download the Employee Leave Tracker Template in Excel, OpenOffice Calc, and Google Sheets to track employee leaves efficiently and ensure smooth operations.
With the help of this template, you can track different types of leaves taken by employees like paid leave, sick leave, maternity leave, or unpaid leave. You can track the balance leaves of employees for a complete year in this Employee Leave Tracker Template.
This template can be helpful to HR managers, HR Assistants, Admin Assistants, and Small and medium business owners to track their employees’ leaves.
Leave tracker helps employees and managers stay updated on available leave balances. This transparency prevents misunderstandings and ensures that employees use their allocated leave appropriately. It reduces the administrative burden on HR departments and managers, making leave management more efficient.
Table of Contents
What Is A Leave Tracker?
A leave tracker is a tool or system used by organizations to monitor and manage employee leaves, including vacation time, sick days, personal leave, and other types of time off. The primary purpose of a leave tracker is to maintain accurate records of employee leave requests, balances, and approvals, ensuring that leave policies and regulations are followed.
The tracker maintains records of each employee’s leave balances, including accrued leave, used leave, and the remaining balance for each type of leave. Leave trackers can be configured to align with an organization’s specific leave policies, including rules for accrual, carryover, maximum leave balances, and notice periods.
In summary, a leave tracker is a valuable tool for organizations to streamline and automate leave management processes, ensuring that employee leave is managed efficiently and in compliance with company policies and legal requirements.
Download Employee Leave Tracker Template (Excel, OpenOffice Calc, & Google Sheets)
We have created a ready-to-use Employee Leave Tracker Template to track employee leaves efficiently and ensure smooth operations
Additionally, you can also download other templates like Employee Shift Rotation Schedule, Payroll Template With Attendance, Timesheet Template, Paycheck Calculator, Employee Training Log, Job Application Tracker, Business Mileage Log Template, and IRS Compliant Mileage Log Template depending on the company’s requirements.
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 firstname.lastname@example.org.
How To Use Employee Leave Tracker Template?
Record attendance of your employees in just 4 easy steps:
- Enter the year and select the month.
- Mark attendance for each date from the dropdown list for each employee.
- Save data to the Yearly Attendance Record with a click of a button.
- Copy data in the columns from AK to AS and paste it into the “Leave Record Sheet” for the respective month.
That’s all you will need 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 3 sheets: Leave Record Sheet, Employee Attendance Register, and Yearly Attendance Record.
Let us understand the workings of each template in detail.
Employee Attendance Sheet
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. The formula used here is =IFERROR(TEXT(DATE($C$6,$C$4,E$6),”and”),”).
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:
- Go to the “Home” tab. Navigate to “Conditional Formatting” in the ribbon area under “Styles”.
- Click on the “New Rule” option.
- Insert the same rule for =E$8=” Sat” and select apply to $E$8:$AI$60. This will fill your Saturday columns with orange.
Employee Details section
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:
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 of 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 are 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 yearly. 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, many festive holidays are observed only in particular states.
Select “ML” for Maternity Leave. In India, the Labour Law entitles a female employee to take maternity leave for 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 inform the employer to observe 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.
Important Note: Please note that the specific policies and entitlements for these types of leaves can vary widely depending on the organization, local labor laws, and employment contracts. Employees should refer to their organization’s policies and employment agreements to understand the specific details of each type of leave and how they are applied.
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.
Yearly Attendance Record
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:
Leave Record Sheet
The leave record sheet consists of the following heads:
Monthly Leave Record
Once you have recorded the attendance for a month all you have to do is copy the data from the “Employee Attendance Sheet”.
You can also do that from the “Yearly Attendance Record Sheet”. While doing so you will need to use the filter option for the month column to copy the respective data and paste it in the respective month.
Above the headings you can find 3 “+” buttons as shown below:
We have grouped 3 months under each “+” sign. As you click on the button it opens the remaining months.
At the end of the sheet, there are 3 sections:
The “Taken” section calculates the leaves taken by the employees during the year. You don’t need to do anything here.
Under the Allowed section, you will need to insert the allowed leaves under each category based on your company policy or labor laws in your country.
Lastly, the Balance section displays the balance of leaves for each employee by subtracting Taken leaves from Allowed leaves.
We haven’t included Half Day, Training, National Holidays, Festive Holidays, and Weekly Offs because they are not counted. Usually, general leaves, Earned leaves, Paid Leaves, Casual Leaves, etc. are calculated.
Enjoy using this template and let us know if you want us to make any other template.
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.