Excel holiday planner

Free Excel Staff Holiday Planner Template

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.

Basics

Enter your employee names into the name cells on the first sheet, the names on the other sheets will pull through from the first sheet.

Monthly totals are displayed at the end of each row for each employee. Yearly totals are displayed in a separate tab called Totals.

The template differs slightly based on the 'Track absences in' option selected in the customisations.

Track absences in Days

Recording an absence in the Excel leave planner

Absences are recorded by inserting the relevant code into the cell for the day. For example, 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.

Track absences in Hours

Absence tracking template in hours

Enter the number of absent hours into the cell relating to the day of the absence.

Leave types

Excel leave planner absence types

8 leave types are provided by default when using the 'Track absences in Days' option. 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

If tracking absences in days, 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 customisation options.

Custom start date

Any start date can be chosen for your leave tracker. The default 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) so you only need to set them once.

Working days

Non-working days are displayed as a light gray cell in the template. This is 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 the year 2020, 2021 and beyond.

Number of months (multi year)

How many months do you want your template to cater for?

By default the template contains 12 months (1 year), you can generate a multi year calendar template by entering 24 months (2 years) or more.

Track absences in Days/Hours

This option changes the template quite a lot.

When tracking absences in hours you simply enter a number into a cell to signify the hours of absence taken. The totals are a sum of the hours.

Tracking absences in days allowances you to specify the type of absence by entering a letter into the cell instead of a number. This option also allows you to track half day absences.

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
The number of months in the template. More info.

Questions about how to adjust the template once it's been downloaded? Check out the FAQ at the bottom of the page.

How did we do?

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

We are planning to add support for public holidays - let us know what countries you would like to see supported!

Please consider sharing if you found the template useful!

Your feedback

This looks excellent! Is it possible to have a conversion to hours taken as annual leave (vacation) per day; our annual leave works in hours not days as staff work different shift patterns - UK. Thanks :)

Answer: There's now a new option to track the absences in hours rather than days. Just select 'Hours' in the 'Track absences in' dropdown box when generating the template. Hopefully that helps cater for your needs!

It would be good if we can have week numbers also mentioned in the calendar. Thanks.

Answer: Week numbers have been above the dates. Thanks for the suggestion

This is excellent, could you add public holidays for the UK.

Answer: We're working on it 🙂

Thank you for the very simple yet useful template. Silvie, UK

Nice work, saved a pile of effort. Thanks

You guys are awesome. To improve this template, I suggest these couple changes.

  1. The count for absence should exclude those entered accidentally on excluded work days (e.g. Sundays...
  2. Will be one of the happiest to receive the updated version which you were planning for July 2020... If it is already available, please let me /us know.
  3. for public holidays, I would suggest you use dummy holidays which anyone can change on himself. Currently I am in Austria, but I might be migrating to Tanzania in the coming years. Thus it would be nice if I could just replace the default holidays to the corresponding country.

I can't find enough words to praise you guys.

Answer: Thank you so much for your kind words! I will drop you an email when the updated template is ready.

I think the idea of being able to switch out the public holidays is a good idea, although it may be trickier to implement. I will definitely look into it.

I think it is important for values on the weekends to still be counted in the off chance that someone does want to mark an absence on a non-working day.

Thank you for your great feedback!

It would be useful in the holiday tracker to have sick days, but not just 1 full day, having a option to input half day sick days

Answer: We're working on a new version that will support this! Thanks for your feedback 🙂

Number of leave options to pre-fill in yourself

Answer: We're working on it!

Fantastic, customized for employees and year. Simple Interface and easy to understand. It's perfect and 10 on 10. Brilliant work guys!!!

Brilliant, perfect employee attendance tracker for our small business, thanks a million.

How do I add somebody in half way through the year or delete a leaver without messing up the macros? I have tried to make changes on the first page - January and the last page, totals but it does not follow through? Can you advise? It's a great holiday chart.

Answer: Ofcourse, I've added two sections in the FAQ to cover your scenarios. Adding an employee and removing an employee.

Let us know if you have any more questions 😊

Can you make one that is multi year?

Improvement: That's a good idea. We've added a field for the number of months under the additional options section! (19 June 2020)

This vacation tracker template has been a godsend for tracking all absence for staff, thank you so much. Public holidays for the UK would be nice but can be entered easily

LOVE IT!!!! Great work!!

Great!

Love it !!!! One Thing missing was public holidays which i have added as a Absence Type. How can we reach out for more templates?

Answer: The public holidays type is a good idea! You can reach us from our support page!

I'd like a dayshift/nightshift option for each day which i can add as "D" or "N" without counting this as absence

Answer: Maybe we could add a section for types that don't count as an absence. If more people ask for this we can add it :)

Awesome. Such a time saver

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. I've added the answer to the FAQ.

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 different 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 templates which don't start in January showed the wrong dates when the year ticked over. (3 Nov 2019)

Very useful pto 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 tracking time off 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)

Frequently asked questions

Here are some answers to frequently asked questions about maintaining the template once it's downloaded.

Adding an additional employee after downloading the template

You can simply select the bottom row of the table and drag the little square at the bottom right (the fill handle) down to the next row.

Excel fill handle

Excel will automatically adjust the formula on your new row to work. You will need to do this on every tab in the template, including the totals tab.

Removing an employee

You can simply delete the user's row from each of the tabs. Excel will adjust the formulas to continue looking at the right fields.

Deleting a row in Excel

The totals row for that employee will break as you start deleting the rows but you can delete the totals row for the user too.

Adding an additional leave type

You can use any letter for your absence types and the monthly totals will work as expected.

To add another column on the totals tab for a breakdown of that specific leave type, do the following:

In the totals tab copy an existing column (e.g. the Work from home 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 to the top cell then drag the fill handle down to apply the formula to all the cells below it in the column. That's it!

You can add the new letter to the key without it affecting any of the calculations.

Request a free tool

We're looking for more business software that we can provide for free.

Got any tedious tasks that could be solved with a tool that you can't find online? Let us know!

Leave an email address to get a notification when we build the tool.

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 30 day trial
and help a 4 legged friend
Puppy looking left