Employee Vacation Tracker to track leave & attendance – Calendar & Report

Posted on
Vacation Tracker Dashboard - Excel Template - Leave Calendar view

Whether you are a HR Manager, a Small Business Owner or a Project Manager who is responsible for tracking leave/vacation/time-off taken by employees, you will find this simple and effective vacation tracker template useful. This leave tracker Excel template enables vacation tracking in an organized way, calculates employee attendance, presents a calendar view of leave across multiple employees and also prints an annual employee report.

Here is a sample of Leave Tracker dashboard you can create instantly using this Excel Template.

 

Employee Leave Tracker Dashboard for All Employees tracking vacations
Employee Leave Tracker Dashboard for All Employees tracking vacations

 

Key Features of Employee Leave Tracker Excel Template

The following are the highlights of the template.

  • Customize settings to meet your business needs
    • Track 5 types of employee leave
    • Customize which days are weekends (not working days)
    • Account company holidays
  • Practical Features
    • Track vacation for multiple employees
    • Employees can have start dates and termination dates
    • Can handle partial days of leave
  • Ease of Use
    • Easy to enter Leave in ranges of dates (example: 4 days of leave from Nov 21st to Nov 24th)
    • Easy to customize colors to suit your preferences
    • Can use the template continuously for many years (keep all your data organized in one file :))
  • Automated Calculations
    • Fully Automated Vacation Tracker Dashboard with monthly calendar view
    • Calculates Worked Days automatically
    • Automated Annual Employee attendance report

 

Download Excel Leave Tracker template

Employee Leave (Vacation) Tracker Template

Employee Leave (Vacation) Tracker Template with Sample Data


Learn to build this template yourself

A fully self-paced video course explaining each step in building this template is available.

Sign up


VIDEO DEMO

 

How to use the Leave Tracker Excel Template

The template has 5 visible sheets. (Settings, Employees, Leave, Team Dashboard, Employee Report).

 

Here is the overview of steps to use the template.

  • Enter Leave Types, Select Weekends and Enter Holidays in the Settings sheet
  • Enter list of employees in Employees sheet
  • Enter leave data for employees in Leave sheet
  • Choose a month and view vacation calendar for the team in Team Dashboard sheet
  • Choose an employee and view/print annual employee attendance report in Employee Report sheet

 

Detailed Steps

Now, let’s see the steps in more detail.

Before we begin, if you are new to Excel tables, please read Introduction to Excel Tables for data entry. Data entry in all our templates use Excel tables and a quick introduction to that will get you going in the correct direction. 

 

Step 1: Enter up to 5 types of leave to suit your company

The template by default supports 5 types of categorizing your leave. Since different companies may have different names and different number of leave types, the template supports customizing them.

Enter 5 types of Employee Leave types to suit your business
Enter 5 types of Employee Leave types to suit your business

 

By default, you will see the leave types (Vacation, Sick, Unpaid, Half Day, Other). You can just type over these names and replace with your own preferred names.

 

How to handle half-day leaves

You can see that there is an option to assign ‘Day Value’ to each leave type. This setting is present to handle partial days of leave. For example, I have entered 0.5 for ‘Half Day’ leave type. That means that when an employee takes Half Day leave, template will add 0.5 to leave taken total and reduces the Worked days by 0.5.

If your company allows 2 hours leave in an 8 hour workday setup, that would be 0.25 day. You can create a leave type and enter 0.25 as ‘Day Value’.

 

Step 2: Select your company Weekends

In order to calculate attendance of employees automatically, we need to know company weekends so that we can remove them from working days.

I learnt from our readers that weekend days can vary a lot by country. Though Saturday/Sunday is the most common weekend choice, there are so many countries that use a different work week and weekend setup. (Wikipedia article on Weekends)

Select Company Weekends to use for employee attendance tracking
Select Company Weekends to use for employee attendance tracking

 

Since our goal is to provide a global solution to vacation tracking, we have the option of selecting any combination of weekend days. In the screenshot above, I have chosen Sunday as Weekend.

So, this tells the template to do 3 things.

  1. Template will highlight Sundays visually differently on the vacation dashboard
  2. While calculating attendance, it will not include the Sundays as Working days.
  3. Sundays will also not get counted as leave.  When an employees takes Thursday to Monday as Vacation, it will count only as 4 days of leave (Thu, Fri, Sat, Mon).

 

Step 3: Enter company holidays

Enter list of holidays in your company.

Enter company holidays
Enter company holidays

 

The template will treat the holidays similar to Weekends for calculation purposes, but holidays will be displayed in a different color on the calendar. This allows us to view holidays and weekends distintly.

 

Step 4: Enter Employees’ data

Now, we move to the Employees sheet.

Enter Employee Name, Start Date and Termination Date (optional)
Enter Employee Name, Start Date and Termination Date (optional)

 

We will enter the Employee Name, Start Date (date when the employee began employment) and then Termination Date (if applicable). The Start Date and Termination Date allow the template to calculate the count of working days for each employee correctly.

Since the template can help you manage leave for multiple employees for long term windows (even many years, as there is no limit 🙂 ), it is quite possible that new employees join the company and current employees may leave. I want the template to work smoothly for you even when that happens.

 

Step 5: Enter Employee Leave Data

Now, for the last data entry section, we move to the Leave sheet.

We enter leave taken by each employee here. There are four fields (Employee Name,  Leave Start Date, Leave End Date and Leave Type) in this table.

Enter Employee Leave Data - Employee Name, Leave Start Date, Leave End Date and Leave Type
Enter Employee Leave Data – Employee Name, Leave Start Date, Leave End Date and Leave Type

 

If an employee takes a 5 day vacation, you can enter that in one row by providing Leave Start and Leave End dates.

If an employee takes a single day leave, please enter same date in Start Date and End Date fields.

Leave Type field has data validation built-in which shows the 5 options we set in the Settings sheet.

Leave Type - Drop down options for data entry
Leave Type – Drop down options for data entry

 

Similarly the Employee Name also has data validation to allow only employee names available in the Employees sheet. But the drop down option will not activate until a new row is created in the table. If you like typing in the name directly, you can go ahead. If you prefer the drop down options, please read further below in the ‘Extensions’ section where I discuss a work-around .

We are done with data entry. It’s time to see the template’ magic in creating the vacation calendar and report.

 

Step 6: View Team’s Monthly Leave Dashboard

The dashboard is interactive as it allows the user to choose the month for which we want to view the leave data for. We will enter the month by selecting Year and Month.

Choose Month for Vacation Tracker Dashboard
Choose Month for Vacation Tracker Dashboard

 

There are two pages in this Dashboard sheet. First is the leave calendar view.

Leave Tracker Dashboard - showing calendar for all employees
Leave Tracker Dashboard – showing calendar for all employees

 

As you can see from the above screenshot, the leave types are shown in different color. The legend is shown at the top indicating what each color means. In addition to the 5 leave types, we also have display for the following: Holidays (purple), Weekends (Dark Gray), Not Employed (Dot pattern) and Not Applicable (Diagonal Line pattern).

Order of priority for colors: Not Applicable > Not Employed > Holidays > Weekends > leave types

Holidays and Weekends are self-explanatory. Not Employed will represent the days before the employee began employment and days after the employee termination date (if applicable).

Not Applicable represents the days that are not actual dates. For example, if you choose November month, 31st day will be Not applicable. For February in a non-leap year, 29, 30, 31 will be Not Applicable days.

You can use this calendar view to assess if you will have enough coverage in your team for specific weeks (for example, during the holiday season). 

 

Monthly Summary

This sheet also shows the monthly summarized count of days taken off under each leave type, total leave taken and Worked Days.

Leave Tracker Dashboard in Excel showing monthly summary of leave days and worked days
Leave Tracker Dashboard in Excel showing monthly summary of leave days and worked days

 

Total Leave is the sum of leave taken under each of the 5 leave types.

If an employee’s leave overlaps with a holiday or a weekend, it does not count as a leave day.

Worked Days is calculated as (Company Working days – Holidays – Weekends – Not Employed Days – Total Leave days)

Worked days represents the number of days an employee worked or is scheduled to work.

You can look at this view during the last week of a month, to calculate the next month’s capacity (in work days) for the entire team. This will be helpful to evaluate whether you have enough capacity to meet the demand (work) ahead in the next month.

 

Step 7: View Annual Employee Attendance Report

While the previous sheet was about viewing all employees at once in a calendar view one month at a time, we also need to see one employee’s data across months. This is where the Employee Report comes in handy.

Here we have three inputs.

Select year, Employee name and End date for the Annual employee report
Select year, Employee name and End date for the Annual employee report

 

The Year and Employee Name selections should be easily understandable.

End Date is set to Today’s date by default. This allows the report to show attendance/leave data as of today.

You can change this End Date by manually typing in any date, if you want to create a report as of any specific date (a month end, for example).

 

This sheet has 2 pages as well. First, the calendar view.

Employee Attendance - Leave Report showing types of leave, holidays and weekends
Employee Attendance – Leave Report showing types of leave, holidays and weekends

 

As we discussed with the Team Dashboard, the colors and the legend work the same way. For the chosen employee and year, we can visualize the complete year’s leave patterns.

 

The second page presents the summarized count of leave and worked days.

Employee Attendance Report - Monthly Summary of leave by type and worked days
Employee Attendance Report – Monthly Summary of leave by type and worked days

RECOMMENDED TEMPLATE

EMPLOYEE LEAVE MANAGER

For managing leave for up to 12 leave types & tracking balances

Employee Leave Manager
Employee Leave Manager Excel Template

Extensions (How to extend the functionality of the template?)

 

What if I don’t need 5 leave types

No problem. You can just remove additional leave types easily.

In this example, I have selected last 2 rows of the Leave Type table. Then, right click to bring up the menu that you see in the image below.

How to remove leave types if not needed
How to remove leave types if not needed

 

Click on Delete –> Table Rows.

This will remove the two rows and you will be left with 3 leave types to track for your company. That’s how easy it is. 🙂

 

How to enable drop down values for Employee Name in the Leave Table?

First, we will extend the table to additional empty rows. Click on the end of the table and drag down.

Click on the end of table and drag down to extend table to more rows
Click on the end of table and drag down to extend table to more rows

 

Once you do that, the table will look like below, with new empty rows.

New Empty rows in Leave Table
New Empty rows in Leave Table

 

Now that you have new rows inside the table, the drop downs for both Employee Name and Leave Type will work.

Drop down for Employee name works
Drop down for Employee name works

 

It is recommended that you add only a few empty rows at a time. The more rows we have, Excel has to process more data when it is performing calculations needed for our dashboard and employee report. This could slow Excel down.

 

Can I remove employees from the file?

On occasions, you may want to remove an employee’s name from the file if he/she has left the team.

  1. Select the employee’s name in the Employees’ sheet
  2. Right Click and Choose Delete —> Table Rows

You can also remove the employee’s leave data from the Leave sheet, but that is optional.

 

How can I share this file with my team?

  • Print
    • Press Ctrl+P or select Print from the File menu.
    • Both Team Dashboard and Employee Report are set up ready for printing
  • Export to PDF
    • From the File menu, select Export and click on ‘Create PDF/XPS
    • Select the location to store the PDF file on your computer
    • Provide a name to the PDF file and click Publish.
    • You can then email the PDF to your team members or post in your local shared drive.

 

I don’t like the colors. How can I change them?

Colors are personal preferences. Sometimes, you may want to use your company’s color palette in the documents. Not a problem. Let me explain how you can change the colors used in the template.

As an example, I will show how to change the Holidays color.

Open the Conditional Formatting menu from Home ribbon.

Open Conditional Formatting menu from Home Ribbon
Open Conditional Formatting menu from Home Ribbon

 

Select ‘This Worksheet’ to show formatting rules used in the current sheet.

Select 'This Worksheet' to see conditional formatting rules used in the sheet
Select ‘This Worksheet’ to see conditional formatting rules used in the sheet

 

At the top, you will see the rules used to highlight Not Employed, Not Applicable, Weekends and Holidays.

 

Let’s select the Holiday rule (the purple one) and then click on ‘Edit Rule’.

Select a rule and then click on 'Edit Rule'
Select a rule and then click on ‘Edit Rule’

 

In the next window, click on Format to choose the new formatting.

Click on Format to change formatting
Click on Format to change formatting

 

Now, you can see the option to choose a color.

Select Color of your preference and click OK
Select Color of your preference and click OK

 

Select the color you prefer and click OK. You will need to click OK in the next couple of dialog boxes as well. Now, you should see the holidays represented in a different color.

You can then save the file (Ctrl+S) to save your changes.

In the conditional formatting rules, when you scroll down, you will see the colors used for the 5 leave types.

Conditional Formatting rules for the 5 leave types
Conditional Formatting rules for the 5 leave types

 

Click on those rules and change colors if needed. The procedure is the same as we did for the Holidays.

 

How can I edit the formulas? The sheets are protected.

You can unprotect the sheets using indzara as password.

If you are new to this, please read this article on unprotecting/unlocking sheets.

 

What if I have more than 30 employees?

You can enter more than 30 employees in the Employees sheet. There is no limit imposed here. However, the Team Dashboard shows only 30 employees. To extend that, follow these steps. To support 35 employees,

  • Unprotect the Team Dashboard sheet with the password
  • Select cells B36:AO36 (this is the row of Employee 29)
  • Drag down for 6 rows as to fill them with the formulas.

Now you should see 35 (29+6) employees accounted for.  If you need to extend for more employees, follow the same steps but increase the number of rows you are copying.

If you print or export to PDF, adjust print settings accordingly to include these new additional employees.

 

How to add calculations such as Attendance %?

You can unprotect the Team Dashboard sheet and add calculations if needed.

For example, if you need to calculate employee’s attendance %, you can follow these steps.

  1. In cell AP8, type =AO8/(AN8+AO8)
    1. This calculates Attendance % as Worked/(Leave+Worked)
  2. Then, select cell AP8 and drag down to fill the cells AP9 to AP37.

This will add a column of Attendance % to the Dashboard.

This was just an example. You can add any other calculations as needed for your company’s needs.


Learn to build this template yourself

A fully self-paced video course explaining each step in building this template is available.

Course - Vacation and Attendance Tracker Dashboard
Course – Vacation and Attendance Tracker Dashboard

Sign up


RECOMMENDED TEMPLATE

EMPLOYEE LEAVE MANAGER

For managing leave for up to 12 leave types & tracking balances

Employee Leave Manager
Employee Leave Manager Excel Template

 


Related Excel Templates

 


I hope this template is useful. If you like it, please share it with your friends. If you have any suggestions or questions around tracking vacation in Excel, please leave them in the comments section. I will continue to enhance this template based on feedback.

329 thoughts on “Employee Vacation Tracker to track leave & attendance – Calendar & Report

  1. Great template. It would be good to be able to send a notification when a vacation date is near due or due

    1. Thanks for your positive feedback.

      The template does not interact with any external email or messaging systems. Hence, a notification cannot be sent to an external system.

      However, one can filter the data to see the upcoming leaves.

      We will try to incorporate these features in future releases.

      Best wishes

  2. Tnks for this template.

    I have working whit it to manage a small group almost a year and its great and easy too use!

    1. Thanks for your encouraging comments. We will try to add more features in future releases.

  3. Hello after I added leave after line 55 the stopped showing up on the dashboard and advise?

    1. Thanks for using our template.

      Please ensure you stretch the table down so that the related formulas can be copied below.

      Best wishes

  4. Hello

    Can you advise on how to change the Employee Report tab so that the weekends line up (like the team dashboard tab)?

    Thanks,
    Savanne

    1. Hello

      The Employee Report is created for a year, where you can select an employee and see the leaves availed in the calendar year. While the team dashboard shows the days worked for all the employees in a calendar month.

      Best wishes

  5. Hi there,
    the template is amazing, really easy to use. One question, my employees have 21 PTO per year, can extra days be added to the calculator? It looks like it’s blocking after 12 days.

    1. Thanks for sharing your positive experience.

      Please ensure that you have filled the correct data in the Settings tab.

      Best wishes

  6. Hi! Your template is excellent. I enjoy using it. I have already managed to add two extra types of leave to your template that I am using. I was trying to add the eighth one but am unable to. Can you help, please?

    1. Thanks for your feedback.
      Please clarify how you added the two and the third one.
      Best wishes.

      1. I went to the Settings sheet and added two additional rows to the ‘Leave Type’. I named the two additional leaves as Biz Travel and 2 hrs Leave.

        Next, I went to the Employee sheet and pasted =IFERROR(INDEX(L_LEAVE,5),””) adjacent to the existing five leave types. I changed the 5 to 6 and 7 respectively.

        Then chose the colour setting in Conditional Formatting. Voila!!

        Thanks to your training provided on top.

  7. Hi

    how do we calculate annual leave balance ? and carry forward to next year .

    Regards
    Anu

    1. Thanks for using our template.

      This template is designed for a calendar year. Please review our PTO template at https://indzara.com/product/small-business-paid-time-off-manager/

      Best wishes

  8. Hi, if you have a global team. Team PAK and Team PHI – how can you have the PAK holidays reflect to Employees based in PAK and vice versa?

    1. Thanks for your message.

      As you define the weekends and holidays, the template calculates the rest. If you have two different sets, please use separate templates.

      Best wishes

  9. Hi,
    Is it possible to add more than 5 leave types?

    1. Hello

      As of now, the template supports 5 types.

      Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *