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:
- Specify the date and year you want to track leave for in cells A1 and A2
- Change Employee Xin the Name column to your employees or students names
- 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:
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.
- 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.
- 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.
- 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.
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
- A lot of formulas (such as SUMPRODUCT, VLOOKUP, INDEX, and MATCH).
- Conditional formatting to highlight leaves and holidays.
- A few lines of code to unhide and hide columns.
- Drop-down Lists to select working/non-working days.
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:
45 thoughts on “Free Leave Tracker Google Sheet Template”
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 ?
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.
Hi
Can you add one for 2021 please
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!
Hi Sumit
I’m stuck with it, it’s not working for me at all. I need your help.
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.
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
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.
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.
It says its updated for 2020 – but all the links that take you to the sheets is for 2019. Am I doing something wrong?
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.
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.
This is great! Is there a way to see the whole year? Rather than by month?
The link isn’t working, can you please re-link?
This is amazing, but as below I think the Google link isn’t working – could you re-upload it?
the file is no longer available. are you going to upload it again?
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.
the file has bee deleted, please help
could you please upload the file again, seems to have been deleted
I am unable to download the sheet. :/
I am unable to download the sheet using your link
Hi there, The file has been deleted, could you please re-upload it.
Thanks.
Hello, the link to download is broken. When I click it says the file has been deleted. Can you please reupload?
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.
=IF(AND(K$3>=$G4,K$3<=$H4),$F4,"") formula not work in excel plz help me how can i do ?
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.
Hi,
Thanks again for this brilliant tool. Will you be doing a 2019 version, or have you had enough? Thanks either way
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?
Select the column and choose clear contents, the highlight will disappear.
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?
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 ???
How can I add colume in the sheet
Great spreadsheet Sumit.
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.
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?
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.
Hello – is it possible to include the bank/public holidays count within the total leave number of days? Thanks in advance for your help.
Everytime I mark H as Child Care, it counts as 0.5 wherein I want to consider it as 1 as well. Please advise.
Hi, How can I mark the starting date and end date of the leave duration
One of the best programs I have used.
This is great! Very close to what I was looking for.
Glad you found it useful!
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.
Hi Sumit! The uploaded copy seems to be deleted. Are you going to upload it again? Many thanks!
Comments are closed.