Free Leave Tracker Google Sheet Template

By

Looking for a leave tracker Google Sheet template that can track the leave of your employees or students?

You’re in the right place.

In this post, I am sharing one of my best templates – the employee vacation tracker template in Google Sheets (you can also use this as an employee paid time off tracking spreadsheet).

This template is perfect for keeping track of, paid time off, holidays, and sick leave. You won’t have to stress about making sure you account for everyone’s leave anymore!

Click here to access the Google Sheets leave tracker template [You will have to make a copy of it to use it].

To create a copy, navigate to File>Make a copy.

I first created this vacation tracker in Excel. Since then I have got hundreds of requests for a Google Sheets version of this leave. So I created this one in Google Sheets.

It’s a great leave tracker template and is loaded with functionalities. So let me also take through the tracker and how to use it efficiently.

How to Use the Leave Tracker Google Sheet Template

The basics of how to use the template are:

  1. Specify the date and year you want to track leave for in cells A1 and A2
  2. Change Employee Xin the Name column to your employees or students names
  3. Use the codes to mark the dates with the type of leave they’re taking

Here is a snapshot of what the leave tracker looks like:

Google Sheets Leave Tracker Template - Demo

In this leave tracker:

  • You can specify the year and the month. As soon as you enter the month number in cell A3 (1 for January, 2 for February), the tracker updates and displays only the columns for that month. The rest of the columns get hidden.
  • You can enter any leave code you want and the leave would be counted. There are some inbuilt codes that will get highlighted in red color. Also, you can use the leave codes H1 and H2 for half-day to mark a leave that is counted as 0.5. In case you want to use your own custom leave codes, replace the ones in this list with the ones you want. Note that the last two are programmed to be half-day leaves only.Leave Tracker in Google Sheets - Codes
  • The tracker gives you a month-wise total as well as the yearly total of all the leaves. It also gives you the leave breakup for the entire year (by leave types). So, it’s a PTO tracker Google Sheets can use for all types of leave calculations.leave tracker google sheets
  • In case you have a weekend other than Saturday and Sunday, you can specify the working days and non-working days by making the Yes/No selection for the days. If a day is non-working, it is highlighted as Gray in the tracker.Google Sheets Leave Tracker Template - Change Working Days
  • You can specify the holidays in the Google Sheets Holiday tracker tab of the worksheet. These holidays are also highlighted in orange in the tracker and the leaves on these days are not counted.leave tracker google sheets

IMPORTANT: In the template, the leave tracker is made to account for leaves for the month Jan-Dec. But if you want to change it to records leaves for Apr-Mar or any other year period, you can do that by changing the value in cell A1 (you will have to unhide the row). For example, if you want the year duration to be from April-2021 to March-2022, enter 4 in cell A1. 4 here would mean that the leave tracker would now start the year from April.

Note that this tracker is heavy on formulas and also uses a backend Google Sheets script. Hence it may take a few seconds to update when you change the value.

As of now, I have created this tracker for 50 employees/students. You can easily add more rows to it. Just copy and paste the leave tracker rows, and it will work fine.

FAQs on the Leave Tracker Template

I have been getting a lot of queries about this leave tracker template, so I decided to answer a few queries here first. In case you don’t find your query answered here, leave a comment and I will try to get back as soon as I can:

Q: Can I use my own custom leave codes in this tracker?
A: Yes! You can use your own custom codes by changing the existing codes in cells NX8:NX17. You also need to specify the same code in cells NL5:NU5. For example, if you change the Work from Home leave code to X, you also need to change it in NR5. Also, note that the last two leave codes would only count towards half-day leaves.

Q: Can I track multiple years of leaves in this same tracker?
A: No! You can not track multiple years in the same tracker. One single sheet will act as a simple annual leave spreadsheet tracker and can not track multiple years. You can, however, create a copy of the worksheet for another year of tracking.

Q: Why is this template so slow when I make any changes?
A: Ideally, it should be fast when you are using it to simply add leaves in it. However, while changing the month, it’s likely to take a few seconds. This is because this tracker is loaded with formulas and a backend script. So let me take a few moments to update.

Q: When I change the month, the exiting leaves are reflected in the changed month as well.
A: This happens when you use cell A1 to change the month. Don’t use A1, instead, use cell A3. A1 cells need to be used in a very specific case when you want to change the starting month from Jan to something else (such as changing it to April to rack leaves from Apr-Mar).

Q: How do I put a leave a tracker in Google Sheets?
A: You can always use a standard calendar template and use your own codes to mark employee leave tyes. Otherwise, you will have to use a lot of formulas. We recommend just sticking to using a free template such as the one in this guide instead.

What goes into making this Google Sheets Leave Tracker

Click here To Download Template

[You will have to make a copy of it to use it – navigate to File>Make a copy].

Whether you’re looking for a sick leave monitoring spreadsheet, PTO tracking template, or leave tracker, we hope you find this leave tracker Google Sheet Template useful. You can also use this as a holiday planner or Google Sheets vacation tracker of your employees.

Related posts:

Popular Posts

45 thoughts on “Free Leave Tracker Google Sheet Template”

  1. The calendar is amazing and I really like the format, although it only runs until March 2021, also I noticed that the list drop down from Leave Tracker, A1 does not work properly. can this be fixed ?

  2. List of Alternative Linksbo Hyperlinks
    Today is increasingly developing, so that it
    is less difficult for those to entry computerized networks.
    Until now there are several on-line gambling
    games that can be played by anybody online. Therefore, gambling lovers
    no longer require to the particular casino to learn betting,
    this is very easy for them to be able to play anytime and anywhere without limits.

  3. Hi Sumit, thank you for uploading the spreadsheet. Unfortunately it is not updated for 2020 and additionally, the 1 month shows July, the 2nd August, etc. Is it possible for you to upload the updated 2020 Google sheet document? Thanks a lot!

  4. All the best things on the leave tracker google sheet and its use is listed here with a brief analyzed discussion and data on them. Such articles are not only knowledge enhancers but also very interesting to read and to learn to compare from.

  5. Hi Summit,

    This a great tool !!

    My only thing is that I need to add more 1/2 days codes… I’ve been messing around with the formulas but I can’t seem to figure it out… Always getting Value error.

    I need 8 codes Total…

    Code

    C Full
    Ca Half
    Cp Half
    M Full
    Ma Half
    Mp Half
    F Full
    Fa Half
    Fp Half
    S Full
    Sa Half
    Sp Half
    V Full
    Va Half
    Vp Half
    P Full
    Pa Half
    Pp Half
    Lt Full

    Any help would be appreciated… I will send you the new version
    Thanks

  6. This would be perfect but there are some problems.
    The sheet equations need updating for 2020.
    In 2019 mode, when I select “1” from the drop down for January, the label “July” is displayed and July 4th vacation is posted in orange, so the months are mismatched.
    Also, when i enter in 2020 i can’t get the holidays to display even when i update the days on 1st and 2nd tab.

  7. Hi Sumit,
    Thank you for putting up this template. It’s very helpful. I am using it for the year 2020. I have just one problem. I am unable to see the month-wise Leave total (green column). Am I missing something here? Kindly help. Thanks.

  8. Hi Sumit,

    Great Work! Dear, I am working with a private firm that denies to assign permission to access files from out side the organization. I would request you to please provide me a copy that requires no authorization request while using online.

  9. I’ve failed to access the google sheets version of the leave tracker. It keeps saying I need permission, which request I ask for but never get a response in my email.

  10. Sumit, would it be possible for you to upload this to Google Sheets again? The file has been deleted. Thank you for all the time you have devoted to making these resources available; in particular, I have learned a lot from your Excel series.

  11. Hello, the link to download is broken. When I click it says the file has been deleted. Can you please reupload?

  12. Hello Sumit. Many thanks for sharing the excellent leave tracker tool. I am having a problem displaying the correct month in cell A4 in the case where my year runs from July to June of the following year. January to June don’t display correctly instead the cell just shows #N/A. Please assist.

  13. Our employees are non-exempt and use there leave in hours , not days. Is there a way to change the formula to calculate in hours used, not days.

  14. Hi,

    Thanks again for this brilliant tool. Will you be doing a 2019 version, or have you had enough? Thanks either way

  15. I removed several holidays from the holiday list and they are still showing up highlighted in orange on the spreadsheet even though they do not appear when I select the holidays tab. How can I get rid of the orange highlight?

  16. Really useful sheet thanks. I’d like to add the first week of january 2019 into my figures ( so 12 months +1 week), could you advise on the easiest way to achieve this please?

  17. Hi,
    Can i have 2 or more different public holiday list in the Holidays tab of the worksheet??
    If can, how can i add 1 more public holiday list and these holidays will highlight in orange in the tracker ???

  18. This is such a great spreadsheet. Thank you so much for making it available. Is there any way you can think to use it to track Time Off in Lieu in half day increments? I thought about using the existing half day cells, but then they would add to the number of days of leave taken, where as I would want a separate tally. Again, thanks very much.

  19. When I continue in the “Holidays” table with a new order of 13 and beyond, the problem arises because this leave automatically counts in the total number of days?

  20. Hi Sumit,

    I want to update the font of the leave in different color. Can you please let me know if it can be change.

    Thank you.

  21. Hello – is it possible to include the bank/public holidays count within the total leave number of days? Thanks in advance for your help.

  22. Everytime I mark H as Child Care, it counts as 0.5 wherein I want to consider it as 1 as well. Please advise.

    • I found one limitation to the program as well. If we have future requests for the Vacation, there should be a separate column for it, e.g. Vacation Requested (using the code “R”. And there should be a total of it in that column). So whenever I plug in R in any of the cells, it should add in that column.
      p.s. RH for Half day request.

      • Hey Aaresh.. you can do this by changing the leave codes. So you can change 2 of the existing leave codes, and also change the column headers in NL6:NS6 accordingly.

Comments are closed.