Employee Leave Vacation Tracker – Free Google Sheet Template

Posted on

Whether you are a HR Manager, a Small Business Owner or a Project Manager it is important to track leave/vacation/time-off taken by employees.  This Google Sheet Template is developed to help people in tracking leave/vacation in an organized way, calculating employee attendance.

This Google Sheet Template presents a calendar view of leave across multiple employees and also prints an annual employee report.

This template is designed for Google sheets, but if you are looking for an Excel template, please visit Employee Vacation Tracker

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

Features 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

How to Use Employee Leave vacation tracker in Google Sheets

Steps to use this template:

  1. Make a Copy and save this template in your Google Drive
  2. Configure the Settings information
  3. Input Employee Data, Leave Information
  4. View reports on Team Dashboard and Employee Report

Step 1: Make a copy of the template

Click on the link above and then make a copy of the sheet as shown in the screenshot below.

Make a copy
Make a copy

You can save the copy in your own google drive for your use. You can then share the copy with your colleagues to collaborate as needed.

Step 2: Configure the Settings information

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’.

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 and Saturdays as Working days.
  3. Sundays and Saturdays will not get counted as leave.  When an employees takes Thursday to Monday as Vacation, it will count only as 3 days of leave (Thu, Fri, Mon).

Step 3: Enter Input Details

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 distinctly.

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.

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 4: View Reports

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.

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

You may also be interested in...

13 thoughts on “Employee Leave Vacation Tracker – Free Google Sheet Template

  1. Can the template support more than 30 workes?. If can how to add the workers because when I added the 31 worker, it does not appear in team dashboard, leave and employee report.

    1. Thank you for using our template.

      You will get an email from support@indzara.com regarding the limit extension.

      Best wishes.

  2. As mentioned in one of the comments, changing the name of leave in the settings sheet and then applying a leave choosing that leave name does not add the color. It instead shows the name of the leave in the Team Dashboard sheet.

    For ex:

    I changed the name of leave from Vacation to Casual and then created a leave for an employee and chose Casual as leave type. However, the team dashboard shows Vacation text instead of showing the Black colour.

    Any advise here. Thanks!

    1. We are unable to replicate the issue from our end. Hence, requesting to share your sheet with some sample data and screenshots highlighting your concern to our support team at support@indzara.com to check further.
      Best wishes.

      1. Hi,
        There was a mistake in my earlier query. Let me rephrase

        leave type = Vacation
        leave background cell colour in team dashboard = Black

        1. I changed the name of leave from Vacation to Casual in the Settings page.
        2. I then created a leave for an employee and chose Casual as leave type.

        However, the team dashboard shows ‘Casual’ text instead of showing the Black color for the Employee.
        I would want the cell to show the Black Color rather than the text.

        Hope this is clear.

        1. Thank you for sharing the details. You will receive an email from our support team shortly.
          Best wishes.

  3. Hi!

    Absolutely love the template, thanks for creating it!

    I think there might be some troubles with the “leave” tab. After a few entries (4 or so) of the same employee, the days won’t update in the “TEAM DASHBOARD” tab. Is there a need to adjust the formulas if there are multiple entries of the same employee?

    1. Thank you for using our template and you are welcome.

      We regret the inconvenience caused due to the highlighted concern.

      Regarding more than 4 leaves for same employee, I have tried entering 5 same type leaves and different type leaves for same employee but unable to replicate the highlighted error. Requesting to share some screenshot to our support team at support@indzara.com to check further on your concern.

      Best wishes.

  4. Thanks for this great resource! On the “Employee Report” tab, is there a way to see an employees vacation days, etc for the entire calendar year, and not just through Today’s date? Would that require significant formula manipulation?

  5. How do I change the colours of the different leave types? Also, when I change the leave type name to something else, it removes the color settings in the calendar view and just displays the name of the leave type?

    1. Hello

      Colors are changed in Conditional formatting rules. When leave type name is changed, the input data (leave data) also have to be updated with new leave types. If any questions, please email file to support@indzara.com

      Thanks

  6. Thank you for this! This is awesome.. more google sheets templates please!!

    1. Thanks for your message.

      The list of templates compatible wit Google sheets are at https://indzara.com/free-google-sheet-templates/

      Best wishes

Leave a Reply

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