Free Leave Tracker for Google Sheets
We just love giving away great free tools. Remember, Leave Dates itself is free, we have an annual leave entitlement calculator (also free), and now we are also offering a free leave tracker for Google Sheets.
Track your team's leave on Google Sheets
- You can enter up to 100 employees (you could enter more but you’d need to adjust the formulas a little)
- Use up to 20 different types of leave, each can be customised, including which types of leave deduct from the staff leave entitlement.
- Track leave booked and taken, and check how much each employee has remaining.
- Staff can have allowance in either Days or Hours
- Use for as many years as you like, simply adjust the filters to select which year you want to view.
- Filter the wall chart by department or employee name
- It’s completely free
A calendar sheet to help you see everyone's leave in one place
This is the wall chart where you can track everyone’s leave in one place (example image at the top of the page). It is a report for viewing only. The only fields you should need to touch in this sheet are the Month and Year fields in the top left corner.
Any leave you add into the Leave sheet, you will see it automatically appear in the Calendar sheet.
Input your employees, their departments and their details
In the Employees sheet, you can enter their names, departments, whether they take leave in days or hours, and their allowance.
- The Taken column is calculated for you based on the leave you have entered for each employee, and the dates filtered above. ‘Taken’ leave is considered as any leave which is on today or earlier.
- The Booked column is similar to the Taken column but considers leave which is for future dates (and therefore not taken yet).
- The Remaining column shows how much leave each employee has remaining to book. It is calculated as the Allowance – Taken – Booked.
Put everyone's leave in to start tracking
You then enter leave into the Leave sheet. Each row should be 1 day of leave – so if the employee takes 5 days then this would be 5 rows. If the employee takes a half-day then the amount would be 0.5. The amount should be given in the unit (days or hours) which the employee is set up for.
Columns A and B are automatically calculated for you, but if you insert a new row then you will need to copy these formulas to the new line.
Lookups to customise the leave types in your business
The lookup sheet allows you to configure the different types of leave which you manage. Add the codes, and colour code them to easy referencing. If you change the code/colour combinations then you will need to update the conditional formatting on the Calendar sheet.
Deduct from allowance is Yes/No and determines whether the leave of this type will deduct from the allowance of the employee.
Updating the sheet for next year
You don’t need to update the sheet for next year, you can simply change the reporting period and it will continue to work correctly. However, you cannot track changes to employee allowances across years, so if you need to track this then you will need to take a copy of the Google Sheet and update it for next year, here are the steps to follow.
Need an even better solution? Leave Dates is the easiest way to track staff leave for free.
Create your Leave Dates account and manage leave for up to five people for free.