Employee Leave Tracker / Schedule
The free Employee Leave Tracker template on this page allows you to track sick leave, vacation, personal leave, paid and unpaid leave. You can record either full day or half day leave for any number of employees and summarize the results on both a monthly and yearly basis. The template can also be used to create a leave schedule or shift schedule.
Employee Leave Tracker
for Excel
Download
⤓ ExcelLicense: See the Private Use License (not for distribution or resale).
Description
You can use this spreadsheet as both a leave tracker and a leave schedule. If you are entering planned leave, then you'll need to realize that the YearToDate worksheet is counting everything you've entered in the 12 monthly worksheets (not just the leave used up to the current date). In that case, it may be more accurate to call the "Year To Date" worksheet a "Yearly Summary" instead.
NOTE: Bug Fix 9/3/2021 - There was a bug in the formula for the Vacation(V) column of the Year-To-Date worksheet which affects the sum of the vacation for September. You can either download an updated version, or you can correct the formula in cell C9 so that the part that references September is the following: MATCH($A9,Sep!$A$6:$A$38,0) and then copy that formula down.
How to Use the Leave Tracker
STEP 1: Edit the Year and Labels in the YearToDate worksheet
I designed this so that you can edit the labels in the Year worksheet and those changes will carry over to the monthly worksheets. By "labels", I mean the types of employee leave and the letters used for different types of leave.
This template allows you to track up to 6 different types of employee leave. If you need to track more than that, it IS possible to add more columns, but doing so will require familiarity with Excel and editing formulas.
STEP 2: Enter Employee Names in the YearToDate worksheet
Enter the list of Employee Names in the YearToDate worksheet. The monthly worksheets use formulas to look up the name based on the Employee ID, so you won't need to enter the Employee Names in each separate worksheet.
The Employee ID column is used in lookup formulas, so it is critical that you use unique Employee ID values.
The template includes rows for up to 30 employees. You can add more rows by inserting rows above the bottom gray row and then copying formulas down from the previous row. You'll need to do this in all worksheets, and make sure the employee IDs are correct.
STEP 3: Save a Backup of the Template
To make things easier for next year, save a backup copy of the template at this point. You can use it as the starting point for your leave schedule for future years.
STEP 4: Use Monthly Worksheets to Record Leave
The main limitation of this template has to do with counting leave based on days rather than hours. If you want to track leave on an hourly basis, see the Vacation Accrual template.
The drop-down lists in the Monthly worksheets allow you to select the letters associated with the different types of leave. If you want to track half-days, you can change "V" to "HV" or "VH" to count a half-day. Look at the formulas in the "Totals" columns to see how this works.
