Excel Staff Holiday Planner
Managing annual leave is a must for teams of all sizes. This leave planner template is a great free way to get started with tracking absences.
If you are looking for a robust absence tracking tool to avoid managing a spreadsheet, checkout our flexible & easy to use online leave planner.
Absences are recorded by inserting the relevant code into the cell for the day. For exampe, in case of sick leave enter S into the cell.
All the absence types and their codes are shown in a handy key alongside the calendar month.
Monthly totals are displayed at the end of each row for each employee. Yearly totals are displayed in a separate tab called Totals.
Enter your employee names into the name cells on the first sheet, the names will update automatically on the other sheets.
8 leave types are provided by default. These include:
- Half Day (morning)
- Half Day (afternoon)
- Time off In Lieu
- Work from home
Half days can be recorded by using the H1 and H2 codes, these count as 0.5 in the totals.
The last tab of the template contains the totals of all absences for each employee as well as a breakdown by absence type.
The 'Absence count' field shows the sum of all absences while counting half days as 0.5 appropriately. It's a sum of the 'Absences this month' column across all months.
Our template is designed to be useful for the majority of teams with the default options but there are a number of customisations options.
Custom start date
Any start date can be chosen for your leave tracker. The template will contain a full year from the start date specified.
For example, if your annual leave year matches the financial year from April to March, select April as the starting month.
Number of employees
Any number of employees can be specified. Whether you have 10, 20 or 100 employees this template is for you.
The employee names are pulled through from the first month (first tab) therefore to set an employee's name you need only update the first sheet.
Working days can be specified in the generator. Non-working days are displayed as a light gray cell in the template. Non-working days are marked for display purposes only, they are given no special treatment when absence totals are calculated.
You can define the year in which the leave planner template will start. Since the template is generated dynamically any year is supported. The leave planner template is ready for year 2020, 2021 and beyond.
Your download will be starting any second.
Sign up for our simple online leave planner for worry free absence tracking. No more excel spreadsheet calendars. Optionally give your team access to view and book their own absences with an approvals system included.
How did we do?
We would love to hear how well our Excel template worked for you and how we could improve.
That's amazing !!! Thank you so much! It helped me a lot. 🙏 😊
Improvement: Added the year above every month to make it a bit clearer. (21 Jan 2020)
It was a great way to get me started. Thank you!
This is perfect! Can't believe it's a free download - thanks, guys. Love it!
Hi, Is there that I can add in additional leave types for example - exam leave, study leave, certified sick leave, uncertified sick leave?
Answer: Ofcourse, adding an additional leave type is not too tricky.
You can simply start using an unused letter for your new absence type and the monthly totals will work as expected.
In the totals tab you will need to copy an existing totals column and replace the letter in each of the COUNTIF statements to your new letter.
Make this change with the top cell then drag the fill handle down to apply the forumla to all the cells in the column. That's it!
You can add the new letter to the key without it affecting any of the calculations.
Hi, Is there a way that I can add in Public Holidays so that they are treated the same as weekends?
Answer: Weekends only marked a differnt colour for convenience and do not affect calculations. You may change the colour of public holidays to match weekends if you like.
Fix: Fixed a bug where teamplates which don't start on January showed the wrong dates when the year ticked over. (3 Nov 2019)
Very useful template, thank you very much!
In the totals page it need to have a breakdown of totals by code so you can tell straight away how many days annual leave have been had.
Status: Added a breakdown of absences by leave type on the totals page. (16 Sept 2019)
Brilliant fantastic - thank YOU so much, saved so much time XXXX
If you link the names from Jan all the way through each month it might be useful. Also, you should have a Bank Holiday tracker that can be set for each month.
Status: Names are now linked from the names in the first month. (24 Oct 2019)
Mark the weekends in another color (cells), but besides that - great work !!!
Status: Weekends (non-working days) are highlighted with a lighter colour. (24 Oct 2019)