Excel holiday planner

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.

Go straight to download

If you are looking for a robust absence tracking tool to avoid managing a spreadsheet, checkout our flexible & easy to use online leave planner.

Basics

Recording an absence in the Excel 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.

Leave types

Excel leave planner absence types

8 leave types are provided by default. These include:

  • Vacation
  • Half Day (morning)
  • Half Day (afternoon)
  • Sickness
  • Maternity/Paternity
  • Compassionate
  • 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.

Totals breakdown

Leave planner excel template totals page

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.

Customisation options

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

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.

Year

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.

Download

The start date of your leave year. Usually 1st January or 1st April.
This affects the number of rows created in the template.
The year in which the planner template starts.
Used for display purposes only
Your download is starting!

Your download will be starting any second.

Sick of downloading a new calendar every year?

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.

Leave planner
Sign up Find out more

How did we do?

We would love to hear how well our Excel template worked for you and how we could improve.

Your feedback

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.

For example:

=SUM(COUNTIF(April!D5:AG5,"W"),COUNTIF(May!D5:AH5,"W"),COUNTIF(Ju...

becomes

=SUM(COUNTIF(April!D5:AG5,"X"),COUNTIF(May!D5:AH5,"X"),COUNTIF(Ju...

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)

Empower your team and get more done

Improve your team's visibility of absences by enabling employees to book and track absences online.
Already using Absentia? Sign in.
Get Started
Start your 50 day trial