Calculate holidays in Excel

The Excel template to calculate the vacation afforded to you in Excel we will know how much legally allowed leave and how much vacation days you take. 

This is fundemental when we plan the holidays of each year. At times we don’t know how much leave we have taken and how much leave from work we can still take. For this the template for leave days taken we’ll know the days we have used up during a determined period and from there, we will be able to calculate the days remaining.

How many days of vacation do I have left?

To calculate your vacation days the template uses an Excel sheet. In this though you will have days of vacation per year are counted including weekends or not. In some countries weekends are counted when taking whole weeks off, and thus the amount of holiday available is increased. In other contexts its simply a matter of taking into account working days (excluding bank holidays).

Even if this might be strange it usually amounts to the same. If you have 30 days of holiday including weekends, then we use two weeks of holidays, we will have taken 14 days of natural holiday and 10 working days holiday. 

How to calculate vacation days left in Excel?

Once we have cleared up how to count the days of vacation we have available in Excel, we need to add in an Excel spreadsheet the relevent data at the beginning and end of the vacation you take. The Excel template to calculate vacation will have to have Excel functions inserted. These function need to be more adequate fits according to the type of vacation :

  • Calculate in Excel the vacation with all the dates off including dates.
  • Calculate in Excel the vacation without counting the weekends.
  • Calculate in Excel the vacation without counting weekends nor bank holidays.

Afterwards, we need to add the seperate holidays, the bank holidays that will be relevant to this period so that, in the case of needing format 3 (the one that does not take into account bank holidays nor weekends for the calculation of holiday used). The Excel template we design then takes them into account by default. The result offers us automatically the days used in whatever format that we end up using. Afterwards you will just have to substract these days from your total to know howmuch holiday is left. We’ll show you how its done.

Example of a calculation of vacation days

We want you to see the easiness with which you can realise this calculation. Thus, let us examine an example of how to calculate the vacation already taken by an employee in one year, so that we can then see what is left.

Let us say that it is September 2020 and an employee that has 22 days vacation per year and had two weeks holiday in August 2020. Concretely this means from 05.08.2020 to the 18.08.2019. In addition you have to take into account that the 15th of August was a bank holiday in his country of employment. So because he has used up a part of his holidays now he wants to know the number of exact days he used to know how much he has left. For that, let’s start with the first calculations.

The first that we need to do is the sum of the dates he has given us.

2 weeks x 7 days = 14 days used

In our vacation system we count all the working days, but the weekends don’t count towards vacation days. Thus the weeks are multiplied by 5 (working days).

However they also have to take into account that the 15th of August is a holiday, and thus they need to discount it from our used holidays.

10 days used – 1 bank holiday = 9 days used.

With this information we already know that they have use a total of 9 days of total vacation that they are entitled as a worker by our agreement. With this, it is not difficult to calculate the vacations that they have left.

22 total vacation days – 9 days used = 13 vacation days

We see that the worker of this company still has the right to use 13 days of vacation during this year. In this way, without the need for a calculator, we can know how many vacation days we have used and how many we have left.

Template for vacation in Excel to download

Now that you know how to calculate the holidays in Excel and by hand you have here a template to just do it automatically. If you do not know much of the Excel program, there is an Excel spreadsheet to download free and within it you can calculate the vacation you have in an easy and rapid manner. It depends on the elements of a spreadsheet required to know our vacation days. You just have to introduce the dates that you were abset from work from.

  • APP to manage employee holiday times.

If you are an employer and you need to take heed of what your employees are taking in terms of holidays or leave we reccomend that you use this Premium Template below. With it you will be able to manage all the leave of the employees and the different reasons for said leave. This way you will have noted down all the information when need. For example an  employee starts abusing the system.

Premium Template for Control of Leave and Vacation

With this Excel template you can easily control the leave and vacation days of your employees. Manage employee vacations and absences on a monthly or annual basis. It contains two Excel templates: one where weekends are not counted as vacations and another where they are. So you can use the one that best suits the business model of the company. This Excel template will allow you to record the employee, the dates and the reason for the leave on the leave tracking sheet. The information will be accumulated so that you can see in a table in a summarized and very simple way the information related to that employee: how many days he has worked, how many he has been absent and the reasons for which he has not worked. A very easy way to keep track of your employees’ absences and vacations. Discover everything this Excel template has to offer!

Premium Template for Controlling Schedule and Managing Vacation

We present to you the very best Excel template for managing your employees. This tool will allow you to control the working day and manage holidays and absences of all your employees in one single document! This template has everything you need to optimise the management of human resources in your company. You will be able to register the daily clock ins and export the working schedule of each employee in PDF for inspectors. Generate autmoatically the organigram of your company and each department.

Excel Template to Calculate Vacation

This Excel template will allow you to calculate the vacation days that correspond to you based on the start and end dates. You just have to enter your corresponding data in the boxes of the spreadsheet.