Free Excel Leave Tracker Template

One of my teammates has the responsibility of creating a leave tracker template in Excel for the entire team. This tracker template is then used to track vacations/holidays and planned leaves of the team members.

Till now, she used a simple Gantt chart in Excel but wanted something better with more functionalities.

So, I created this Excel Leave Tracker Template to make leaves management easy and track monthly and annual leaves by her team members.

You can also use this as a vacation tracker template or student attendance tracker if you want.

Excel Leave Tracker Template

This Excel Leave Tracker template can be used to record and monitor employee leaves for a year (of a financial year where you can choose the starting month of the year).

You can track 10 different leave codes for an employee  – vacation leaves, sick leaves, maternity/paternity leaves, casual leave tracking, leave in lieu of overtime, and half days, etc.

It also provides a monthly and yearly total of different types of leaves that can be helpful in project planning and leave management.

It uses a bit of conditional formatting, a few DATE functions, array formulas, and a simple VBA code.

Download the Excel Leave Tracker Template (tracking for 20 employees/people)Download File

Looking for the Google Sheets version of this Leave Tracker Template? Click here!

How this Excel Leave Tracker Template Works?

  • Use the triangle icons next to the month name to move to the next/previous month (the template updates itself to show the dates for the selected month). There is a short VBA code that runs in the background whenever you change the month. It shows you the selected month only and hides all the other months.Excel Leave Tracker Template 2020 - select first month of the financial year
  • This Excel template can be used to track leaves for over a year. You can select a start month and can track leaves for a year. For example, if you follow the April-March cycle, select April 2023 as the starting month.
    • Note: The value in cell A1 is to change the time period of the leave tracker ONLY. DO NOT use Cell A1 to move to the next month while recording leaves. Use the triangle icons next to the month names to go to the next/previous month and mark leaves.Excel Leave Tracker 2020 - Change the months using the arrows
  • You can specify the working days and non-working days (Weekends). At the right of the leave tracker, there is functionality to specify the working days by selecting Yes from the drop-down. If you select No, that day is marked as a non-working day in the leave tracker.
    • As soon as you specify the non-working days, those weekdays get highlighted in gray color in the leave tracker.

Excel Leave Tracker Template - Select Working Days and Weekends

  • You can update the holiday list in the worksheet named “Holiday List”. It will automatically be reflected in the tracker by highlighting those days in Orange color.
  • To enter the leave record for employees, use the relevant codes based on the leave type (you can customize these leave codes). For example, in the case of sick leave, use S, in the case of Vacation, use V, as so on.Excel Attendance Tracker Template 2020 - Holiday List
    • There are two codes reserved for half-day leaves. you can enter H1 or H2 for a half-day leave.Leave Codes You can use in the Leave tracker template in Excel Vacation Tracker Attendance
  • As soon as you enter the leave code for any employee, it gets highlighted in red (in the case of half-day, it gets highlighted in yellow). If that day is a weekend or holiday, the color would not change.
    • Column NJ (highlighted in green in the pic below) has the number of leaves of that employee in that month. It counts the leaves on working days only (those on weekends and/or holidays are not counted). Half-day leaves are counted as 0.5.
    • UPDATED: Column NK (highlighted in light red in the pic below) has the number of annual leaves taken by an employee. It counts the leaves on working days only (those on weekends and/or holidays are not counted). Half-day leaves are counted as 0.5.Leave planner in Excel - Free Template - number of Leaves Month Year
    • Columns NL to NU gives the leave break-up by leave code (for the entire year). This could be helpful to keep a track of the type of leave that has been availed. Note that while Half Leaves are counted as .5 leaves in the total count, in the leave break-up, it is counted as whole numbers. For example, 2 half leaves would lead to 1 leave count, but you’ll see two half leaves in the leave breakup.Leave Tracker Template in Excel - Leave Breakup by Type

I have created this leave/attendance tracker template for 20 employees. If you want to add more, just copy-paste the formatting and formulas for additional rows.

Also, since there is a VBA code involved, make sure you always save it with .xls or .xlsm extension.

Download the Leave Tracker TemplateDownload File

Note: To update this template for any year, simply change the year value in cell A2. For example, to make it for 2017, just change the value in A2 to 2017. Also, you need to update the holiday list for the specified year.

The download file is completely unlocked so you can customize it to your needs.

Here is another version of the template that can track leaves for 50 employees.

Want to learn how to create awesome templates and dashboards? Check out the Excel Dashboard Course

FAQs on using this Leave Tracker Template

Since I created this vacation/leave tracker, I have been inundated with emails and comments. What you see now is a refined version that has been possible due to all the feedback that I have got.

Based on the questions I get repeatedly, I have created this FAQ section so that you can get an answer faster (instead of waiting for me to respond).

Here are the most common questions I get about the Leave tracker template:

Q: I tried downloading the file but it downloaded as a zip. How do I use it?
A: I have fixed this issue, and now you should be able to download the Excel file directly.

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. You need to use the arrow icons to change months. Cell A1 is to be used only to set the starting month of the calendar. For example, if you want the calendar to start from April, make cell A1 value 4. Now to move to March, use the triangle icons.

Q: I need to track multiple years of leaves. Do I need to create a new worksheet for each year (financial year)?
A: Yes! This leave tracker can only track leaves for a 12 month period. You need to create a copy for each year.

Q: Can I use my own leave codes?
A: Yes! You can change the leave code 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.

I hope you find this leave/vacation tracker helpful.

Are there any other areas where you think an Excel template could be helpful? I am hungry for ideas and you are my gold mine. Do share your thoughts in the comments section 🙂

Related Excel Project Management Tutorials and Templates:

Excel Ebook Subscribe


Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

1,188 thoughts on “Free Excel Leave Tracker Template”

  1. It would be great if there was a column for days available or accrued and days remaining. The days available could be a number entered, like 30, or it could be based on a formula of x days per month. The days remaining column should subtract either total days out or a selected subset. For example, I don’t get charged for my companies newly formed refresh/recharge days (covid inspired) but I am still out those days and added it as a code R above the half days.

  2. Hi, How can I share the file only allowing the users to navigate from month to month using the arrows while keeping the data protected? I tried different ways of protecting the sheet, but was not able to make it work. Please advise

  3. Is it possible to copy the sheet and update it to a new year. I tried that by changing the year, the months change but 2020 stayed. When you try using the arrow to go from month to month, it get a VBA Debug error. Please get back to me at your convenience. Would be happy to pay for a 2021 template if that is an option.

  4. This is a really great tracker. One thing I would like to be able to do is when I add a new employee, I would like it to not include that employee in previous months; and obviously i would like to be able to delete an employee without affecting their previous months. I know it is possible to do this, just looking for the best method to do so. Thanks in advance!

  5. Hello, this has been helpful.
    Question: I have been asked to create an extra column for “the number of days taken” after each month and I have tried several formulas but I am not getting it can you please help with a formula?

  6. There should be another sheet where it has to give summary like employee name, jan leaves, Feb leaves and finally total leaves used for that month. Do you think is possible?

  7. Thank you for this. I would like the sheet to count certain types of leave on holidays, such as comp time earned. If they work a holiday, they receive one comp day to use at a later time. Is it possible to formulate so only these 2 types of leave (full comp day and half comp day) are counted on holidays and/or weekends? Thank you again for sharing this, it has been very helpful!

  8. Hi I love the template. I do have an issue. Having filled everything in and revamped some items to suit I have to password protect so that the staff members viewing it cannot edit, delete cells or make entries. they file a request based on the availability in the holiday window and I unprotect then make entries and then protect.
    The problem is that due to the macro that changes the view from month to the next month they cannot view the months as the macro does not work whilst the sheet is protected? Any fix?? thanks

  9. I love this tracker! Thank you for sharing. Since we track FMLA on a rolling calendar, I’d like to track one person per tab. I tried duplicating the Leave Tracker Tab but the Starting Month function did not operate properly on duplicated tabs. Can this be done?

  10. Any chance to have a 2021 version too? I love this file and would like to keep using it for next year.

  11. Thank you very much for sharing this, it is phenomenal! I am looking to extend this to include multiple balances of different leave types (vacation, sick). I would like to include the accrual in real-time for each month in addition to deducting any time taken. If they accrue 13.33 vacation hours the last day of month, with no probationary period, how can I have the beginning balance minus used time plus the accrual in real-time? Example, John has 120 hours vacation in March, used 20 hours in May and accrues 13.33 at the end of each month. I need the balance automatically populated for the date I open the workbook. Any guidance is truly appreciated! Thanks again!

  12. If the working days are different for different employees could we customize working days for each employee?

  13. Thanks for this excellent stuff…really helped me a lot.
    But i have small concern about it that when i mark for vacation it doesn’t add up the holidays which falls in between..i want that to be added..this is how calculation goes here in my country..any solution to this?

  14. Hi,
    I just want to know that I dont want to count Work from home in leaves monthly and annually. How can i change it?


  15. Great file. Thank you. Any ideas on how I can add this into the existing document?

  16. i want the vacation count only in leave this month and leave this year column. I have to change the formula in leave this month and leave this year. please help.

  17. Hello Summit, Thanks for the template. Really excellent and it solves my purpose. My project resources are based on multiple locations and can you help me with the below additional requirement

    To create one dropdown attribute named Country and based on the country selection for that particular employee, the respective public holiday needs to be applied for that particular resource.

    Please help.

    Best Regards

  18. Is there a way to pull reports or trends (graphs) for all employee leave taken during a month?

  19. I would like to be able to enter hours per day (9 sick) and also total hours for each code (sick 23). This is already great, tracking hours would make it FANTASTIC!

  20. Hi… this spreadsheet is really great and has helped me tremendously… I just have one question please. Is there anyway made the cells different colours for each leave code please? Ive tried to suss it out but can’t find how to do it. Thank you.

  21. This is amazing! 🙂 I copied the tab and changed the arrow macros to ActiveSheet which worked but the month stays as ‘January 2020’ in the text box. How do I get it to automatically change to the next date? The formula is =Sheet3!G2 but doesn’t make sense to me 🙁

    • Hey Sophie, not sure when you asked this but have you tried changing Column A, Cell 1 from 1 in the drop down options to the month you want it to start from? Worked for me when I changed it to 4 & now starts at April .

  22. Thank you for this – its truly amazing – I really like the way you can scroll through the months with the arrows, rather than navigating a long sheet – how do you set this up?

  23. Hi the tracker is excellent and has saved me a lot of time.
    I have some colleagues who work part time, is there a way to mark the days of for those people so it doesn’t show as a work day?

  24. Hi,

    The tracker is superb but I need one more help on it. I would like to track the leave balance too. It is doable?

    Hope to hear from you soon.


  25. Hi , i want to remove Work from category from leave. When i removing from column all others cell being red. Pls help

  26. can a rolling year be added for the sickness? Also can you create tabs to add additional trackers for multiple years or does it need to be a new document each time

  27. Hi – I love this leave tracker, it’s great! Just one question, I like to protect the spreadsheet to avoid people make changes or overwriting formula – the problem is when I protect the formula area (the 2 red triangles either side of the month /year) I am unable to change the month and an error is issued saying that I need to unprotect the sheet. Can you help?

  28. This has been SUPER helpful for me! Thank you!
    Question: I know that we can only use the leave sheet for one year. However, is there a way to duplicate the leave sheet and the reference sheet within the same workbook and just change the year??

  29. How can I do only half year? means separate Jan – Jun & Jul – Dec? Thank you

  30. Hello Sumit,

    Your Leave Tracker is amazing and has really opened my eyes even wider to what Excel can do. I am trying to use this tracker for my employees but instead of tabulating the days and half days can this be modified to tabulate hours instead?

    Thank you so much!

  31. Can it be customized to include different work days per employee? For example, not all employees have the same days on or days off. The ‘Select Work days” to be customized per employee?

  32. Hi. Its a great solution. I could use it to track leaves across multiple projects in separate sheets. Can a dashboard be added to it that will pull the total leave information of individual employees month wise for each project and colour code the individuals record if it exceeds a certain number of leaves for a month?

  33. Hi There, Thank you it’s helps, just a question, I have teams in different country and the public holiday is not same across all country, next to name can you include a column to add country?

  34. Can i use other colors for each leave code, like Green for Vacation, Red for Sick, Yellow for Maternity and so on.

  35. This template is the best. I’d like to change the fill color for the Sat & Sun columns. Can I do that? Thank you.

  36. Is there a way to assign points to this spreadsheet to automatically calculate your company’s assigned point system based upon absence, tardy, etc?

  37. Love this sheet, thank you! How do you recommend using time carried over from a previous year?

  38. How do I change the color for each Code? How can I put in by hours instead of days?

  39. I need a 12 month rolling for sick leave, but each employee has different allowances depending on length of service, so I need to be shown a total of amount used and what’s left each month…can you help?

  40. How can we change the color for the code we enter, for example i want to change color of P code from red to other color?

  41. Is it possible to have the Working Days only Grey out Non Working Days, but still include counts in the totals?

  42. hi
    when using the arrow buttons to change the month – it still reflects as January 2020

  43. I absolutely love this and has been a great help for my team, I have a challenge though:-

    We all work with different start dates so for example my leave year runs 1st May until 30th April.
    My colleagues may run 1st June – 31st May.

    Thank you though as it’s brilliant.

  44. Thank you for the Leave Tracker. Can we calculate the leave hours instead of days? Sometimes employes take vacation hours not full days The sheet calculate by days. How can I change to hours?

  45. Thank you for the Leave Tracker. Can we calculate the leave hours instead of days? Sometimes employees take 2 hours for an errand or a Dr.’s Appointment. The sheet calculate by days. How can I change to hours?

  46. This is fantastic! Exactly what I’m looking for. However I’d like to be able to track work from home days, over time days, sick days etc without it counting as leave in columns NJ & NK? Whats the best way to amend this formula?

  47. I am unable to download the file. Following error occurs. Please help
    This site can’t be’s server IP address could not be found.

  48. Hello! I have added 21 columns before all calculation (changed in VBA module new ranges), but facing issue with macros while moving months, as currently I see half JAN, full FEB and rest months of half starting in the middle of 31 columns. Could you advise what needs to be changed?

  49. Would also like some additional Half Day Leave Types/Columns. I can’t figure out how to change the formula to accommodate when I replace other existing categories. Help please!

  50. Hi, I would like to additional enter personnel number to the column before the name. Is this possible? I tried to enter it but it messes up the dates of the month.

  51. A person take leave from 8th Feb to 10th Feb i.e sat, sun & mon. After entering leaves on 8,9 & 10th Feb, excel formula only count leaves of 8th & 10th Feb. It should count all three days right? can you help me for that formula for the leave tracker template given in this page for all the months?

  52. how can i change on of the h2 to count as a quarter day holiday? I have changed the code, but the formula is the same and it still counts quarter day as half day.

  53. Could you advise how to use this fantastic template as a session worked for e.g. I have GP’s who work just the am and on another day a full day. I can do the half day which is great but I don’t know how to change the leave (vacation) to two sessions worked as it is not calculating the true amount of leave taken

  54. Hi
    I want the leave total column to add the leave days .

    Ex : If an employee is on vacation continuously for 30 Days. It should also consider the other (off) days as annual leave. Currently if vacation is marked on the leave days/off days, it is not capturing they. How would I make count the leavs uncluding the off dasy.

  55. Can you help me please I need to add column before column A but when add any column reflect to the days of the month 🙁

  56. This is fantastic! Exactly what I’m looking for. However I’d like to be able to track work from home days without it counting as leave in columns NJ & NK? Whats the best way to amend this formula?

  57. I want to lock the past date cells to prevent to edit because if some one took the leave and record it in the template it should not be edited after leave date passed , can you modify this and share the template on below email

  58. Hi,

    I have downloaded the excel template and it is very good to use and I have a query can we update the template based upon location for holidays.

    Ex: If suppose in a team there are total 12 members and 4 are from Banglore, 2 from Pune, 2 from Gurgaon, 4 from Hyd so the holidays differ from location to locaion so how can we distinguish the holidays among the team members.


  60. The excel spreadsheet for tracking leaves of 50 Employee’s isnt working properly. I am unable to move to the next month. Is there any way the spreadsheet can be reviewed by your website?

  61. I would like to use your tracker but I am trying to modify it. I would like to add a functionality wherein, I will put only the range of date leave on a separate sheet, example Jan. 15, 2020 until, feb. 1, 2020. I should not be manually plotting the dates one by one. Instead, after clicking an approve button, it will automatically be plotted.

  62. Hi,
    I have downloaded the 50 employees version and have changed the year for 2020, however in the process the macro to change month has become defective.When I saw to debug code, it highlights this part – Range(“A3”).Value = Range(“A3”).Value + 1 Could you please advise how I fix this so that I can continue to use.

    • Please ignore my post. I found a workmate had protected the sheet and therefore it had restricted the access. Thankyou.

  63. Hello! Love the template. Thank you for making it available!

    I’m trying to tweak it some to add additional tracking capability. When I add the columns and codes for what I want to check it is working just fine, but I would like to exclude 3 codes of the 9 being used from being tallied in the ‘Leaves this Month’ and ‘Leaves this Year’ sum cells. I’m not having luck modifying the formula as I don’t have a lot of experience with so many layers of nesting.

    Could you point me in the right direction? Thanks!

  64. Is it possible to import it to gmail/google account so my co-employees can edit it and view it.?
    Actually, I tried to import it and send the link to them to access, but some details missing like the month with ‘arrow left’ and ‘arrow right’. Also the holidays marked are missing.
    And the ‘click here’ to copy the leave tracker is not accessible.
    Hope you could help me with these.
    Thank you so much.

  65. Hi, I also want to track the number of times a person is late within the month. Is there a formula for that?


  66. My company allows leave to be taken in hourly increments, is there a way to create codes such as V1(0.125) V2 (.25) V3 (.375) V5 (.625) V6 (.75)& V7 (.875) into the leaves formula’s? The complexity of the formula is beyond my scope I cant figure out how to edit it myself help!!

  67. Hi, I have uploaded the excel sheet in SharePoint but the triangle icon is not working. When I tried to use the filter, it’s not going to any month. Please advise how this gonna work? Thank you

  68. I have DL the 50 Employee template, i have selected the full 7 days as working days in my industry. Within the tracker there are orange columns which when you edit with any of the Leave Names it does not collate this in the totals, why is this and can it be rectified?

    • Click on the second worksheet titled HOLIDAY a yellow icon will pop up just above the sheet requesting you to enable, just click enable, then return to the first worksheet, the arrows would now be able to work. I hope this is helpful to u.

  69. i have been using this tracker for over a year now as we have over 250 employees in our department. it is working well so far. thank you. I have a question, what if an employee gets separated from the company for example in October but i have data in previous months, I don’t want to delete the record, i want to keep the data for record keeping, how & what can i do ???

  70. Is it possible to have 3 Half Day Codes and have those half day included in other codes? i.e: Vacation Half Day also gets added to regular Vacation code, etc.

  71. Amazing spreadsheet!!
    Would it be possible to implement half-day Holidays on the holiday list? Thank would be very convenient!

  72. I admire the spreadsheet created. However I have tried copying and pasting for the next year in the same sheet without it working – no doubt because of the referencing of the formulas. Is there a way to have multiple years shown here?

  73. Hello, I have downloaded the spreadsheet but everytime i open it up it asks for the start date of the year repeatedly and I cannot go past the pop up box constantly coming back up. I have to force quite excel. Could you please tell me how to get round this?

  74. Hi, we have different working days for different employees- Sunday to Thursday, Monday to Friday and Tuesday to Saturday. Is there any way we can configure the working days for each employee?

  75. I wish there was a way to have some leave codes not add to the monthly/yearly totals. Not all our leave is chargeable. I tried to edit the code but it is way above my skill level. Even if I don’t edit the monthly/yearly cell and only select it and hit enter it gives the #VALUE error.

    • This would be very helpful! I would like to track employees who work from home but that isn’t technically a “leave”.

  76. How can I have the calendar reflect holidays where the day before is a half day for all employees, i.e. Christmas Eve and New Years Eve? My company doesn’t normally give these as holidays but this year we are closing a half day. I want to make sure the employees are given only a half day for this day if they choose to take PTO during this time.

  77. How can i change the “Leave This Month” and “Leave this year” formulas, so that only “Vacation” is counted as 1 and H1 and H2 as half but all else ignored?

  78. My company allows for time to be taken in hourly increments, not just half or full days. it this template able to handle this or will I need to look for a different solution?

  79. This is an awesome template. The only thing I am wondering if it’s possible is whether we can designate certain Holiday’s for certain employees. I work for President of an International Team so we have folks in Canada, Australia, Mexico and we are expanding into more countries. It would be great if I could somehow designate the different holiday’s by region for each person.

  80. Hello – i have downloaded your Leave Tracker template and customised for x 33 employees for 2020. How do i print the document so that all x 12 months print please – in one go ? thank you.

  81. Hi, can you create a template tracker for capital expenditures? Like monthly we will input the actual capital expenditure spent for the month, then we will do a re-forecast for the remaining months. Also, different sites with different list of capital expenditure projects (additional new projects may be added to the list as time goes by) also add complexity for monthly report use.

  82. Hi this is a great tracker unfortunately my business has disabled the ability for macros to use – any ideas on how to use the template without the macros?

  83. I’m having trouble with the Holidays. Some of the holidays are not off days for our company. But even when I remove those holidays on page 2, they still remain as days off on the tracker.

  84. In a seperate sheet, can I get the summary of leaves month wise for all the employees.

    Here it shows month wise. I need the detail of leaves one year at one place.

  85. This is very helpful. Can you add a column after the “Leaves This Year” to count the total of Leaves they took on a holiday. Thank you!

  86. Hi! Great spreadsheet! How can I change the holiday year? Instead of starting from January to start from April. I have done it in Sheet3 but once I change it then the days are offset.
    Thank you!

  87. Hi! I absolutely LOVE what you’ve created! I’ve been playing around with this and think it’s amazing! I did have one question. I’m wanting to be able to track hours and not just half or full days off. I’ve been playing with the formulas and can’t seem to get it to work. Is there a way that you can have 0.1, 0.2, 0.3, 0.5, 0.6, 0.7 as codes for 1 to 7 hours off and have it calculate just like you did with the H1 and H2?

  88. Can I change color codes for example annual leave green, sick leave not paid red, sick leave paid blue and maternity pink

  89. Hi,

    Thank you very much for this.

    I want to include “IN” and “OFF” on the days that employees are working and have off days but I do not want these to be counted when entered into the cells? How to exclude these so they are not counted in the leave totals? Thanks very much.

  90. Hi I have 80 employees, please suggest how can i add more to track leaves template of 50 employees?

  91. i really love and like your “Leave Tracker Template”, for now i create for custom but still used your template 95%, but mostly i would like to ask about how to make change Leave tracker into attendance mean i would like make your leave as attendance in a month, put NJ8 = NL8, but formula NJ8 same as NL8, so every month change will keep as many leave track/”as leave code” for now leave track count in a year! i wish i can send my file to you…

  92. This works really well, except how do i edit the formula so that it does not deduct sickness and home working from your leave? If an employee gets 22 days holiday, I do not want the formula deducting leave when they work from home as this template seems to do that? I simply only want it to look at certain codes and deduct those eg vacation and half days.

    Is it possible to do this?

  93. When I saved the tracker and then reopened it, the macros would not work. I tried resaving enabling macros and it still would not work. Suggestions?

  94. Great Tool. Is there a way to create a summary of the tracking so you can see the whole year at once?

  95. Hello,

    This is very nice, but I am using SharePoint. It doesn’t allow for macros. You don’t have a version without the VB macro to move from month to month or ideas on how to create one without VB macro?



  96. Thank you for the helpful leave tracker. One simple thing that can be done to faciliate customization is to use cell references in rows NL5:NU5, rather than hard-wired codes. Just have cell NL5, for example, =NX8; NL6 = NX9, etc.

  97. Hey creator! i love this solution. however how can i only count leave labelled “V” and “H” whereas the rest are just indicating they are on “S” without adding to the total monthly and yearly leave?

  98. This spreadsheet is awesome!!! I’ve made few tweaks to match our organization’s attendance policy. However, I now need to be able to see a detailed listing of missed days (and the type of missed days) for one individual at a time….a report of some type that can be printed and placed in an employee’s personnel file.

    How can I do this?? I’ll be more than happy to share the current sheet I’m using.

  99. Are you able to add a rolling total of holiday taken month on month so that you can see how many remaining days the employee has left, I’m not sure how to add this into a workbook that has macros (so what I need is annual entitlement, leave taken this month, leave remaining, so that it carriers across the entire year. If you have any advise it would be appreciated. Thank you

    • I got the same problem as well. Also, I would like to add one more column of the carry forward vacation days plus the total annual entitlement. Please advise.

  100. how can i change the color when i enter codes into the cells. for instance i’d want Vacation cells to turn green and sick days red

  101. Fabulous work. We just started using this. Hope it works for us well. Was simple to use but will know more once folks start to use it.

  102. Hey this template is amazing. but i need further help. In our organisation, employee fiscal year is from the date of starting.. how do i do that for every employee. please help

  103. Thank you so much ! I am loving it. Will you do a new one for 2020 ? please say yes 🙂

  104. This is awesome! I created it on excel but was trying to convert it to Google Sheets. It doesn’t copy all the formats..etc. Any tips on how to get it to google Sheets?

  105. The Excel Leave Tracker Template is wonderful, but One question – how do I change some of the values from 1/2 day to full day. I’m looking at the formula and have not yet figured it out. Any suggestions?

  106. This is really useful, thanks. How do I change the colour so that each type of leave is a different colour?

  107. Thank you for sharing this tracker, I want to share this information with all our staff. How can I protect it so others have access to view only?

  108. I want to add more type of leave, how to make it automatically counted either as 1 day or 0.5 day? I tried to copy to formulas but it does not work, and the new leave I added in is not marked as highlight as well.
    Thank you.

  109. Yes, its so helpful and make my job in tracking annual leave for each individual so much easier~ thanks!

  110. how to set working days for specific employees,because their non working days are different.
    Also what can I do if the time frame of employees allowed vacation are not the same ??
    I tried to duplicate the sheet to change years but wasnt working

  111. Hi this is a very good tool that helps managing the team capacity.
    Here some input for improvement:
    – Add the Indicator of team capacity (target) and a rule to automatically check the target with availability? for instant my if my expected capacity of a team is 8 people and i have a rule of only 2 planned absence in the team i would like to get an alert when the absences exceed the rule.

  112. Hi. This template is very useful and serves a very god purpose. However, what if I don’t want to include work from home as part of leave? I want to exclude work from home from monthly as well yearly leaves. I tried to change the formulas a couple of places, but didn’t work. Appreciate your help on this.

  113. Thank you very much for sharing this you have just saved me hours of creating my own version i am truly grateful.

  114. I don’t need that many holiday types, just want to keep it simple for Leave and the half days. But when I delete the rest leave types the conditional formatting to show the red and yellow colours disappears. What should I do to keep the conditional formatting?

  115. This is very helpful to tack leave for all employees, but how can I download this excel file directly.

  116. This is very helpful. Anyway, i have one question. If employee take half day of sick leave, how could we track it? Because from your template, we cannot track half day leave by specific type of leave.

    I’m looking forward to hearing from you and many thanks in advance.

  117. It would be nice to have a spreadsheet to track hours, OT and calculate PTO. If it could be attached to this one, you would have a winner.

    Thanks for the help. I needed this.


  119. Very neat tracker. Thanks so much. & My question is how can this be made so that when an employee put their details on an input bar or a different sheet, their data will automatically entered into the “group” sheet? This is in order to get data from individuals without them seeing others’ input that they can accidentally mock up.

  120. Hi,

    Does this template has the capability to handle multiple region public holiday calendar. For e.g. employee in America have 4th July as public holiday but employee in Europe will be working on that day.

  121. This is a very awesome Leave Tracker, Thank you so much! Been using it for a while now. Was wondering if you can maybe make a google sheets version that has months from Feb 2019-Jan 2020. Our company renews their leave every year on the 1st of Feb.

  122. The Leave Tracker works great! I do have one suggestion. I have employees whose working days, are not the typical Monday – Friday with Saturday and Sunday off. I have employees that have different schedules. I was hoping you will be able to modify the “Select Working Days” function towards individual employees that way the formula can calculate Leave Name and code on each employee on any day of the week.

    For example: Monday – Sunday cells on calendar for each employee should have an option that designates working day a “Yes” or No” selection.

  123. Hi,
    I like the tracker and would like to use it also. However, I would like to add designation and date of joining to the tracker next to employee name. Whenever I insert a column, it shifts the date ahead. Please help.

  124. I want alternative Saturday as a working day. How to do same changes in Leave tracker excel sheet?

  125. Dears, how can I introduce/modify a leave reason weight so that, for instance, remote working is not counted as “leave” ?
    Tried modifying the Leave formula but can’t get it to work … Thanks !

  126. I have entered a Leave Name for Provisional Leave and I do not want it to add a day to the leave taken sum. How do I format the code I enter to have a zero effect on leave taken?

  127. Your Excel sheet id quite excellent. I have few doubt to ask..!

    1. i’m working in a company, my job is daily updation of leaves. So we use multiple sheets for working. now i need to work using your excel sheet, but i can’t copy the sheet to another. some problem occur during copying.
    2. Using your sheet for a year. one employee is joined in January, their details remains till the end of that year. if we delete when the resigned, whole data would goes with it.
    we want the month changing system, but in data should enclose with that month only, is that possible.
    please give me a solution. i’m waiting for your concern.

  128. Attan: Dara Pettinelli. Never trust Abby Perlman because she was planing to lock up Dara Pettinelli using CBS anchor Otis Livingston!

  129. fantastic sheet… was wondering if i only wanted may and june, how do i do that?

  130. Hi, we are trying to use this but someone has noted that if you enter ‘W’ eg Work from Home day – it is counted as holiday. This logic is wrong. How can I edit this? Thanks.

  131. hello… liked the tool. i just want to enables multiple employees can open the sheet at a time.. it shows error “cannot be shared because has Maps or HML … ) how to fix it

  132. hi, i was wondering if i can modify one of the codes for certain period of time less than a half day, lets say 3 hours. Thanks!

  133. Hi this is great thank you. I’m wondering if you can tell me how to add employees as we have 70?

  134. Hi,

    How come I can note Enter V-(vacation) on a Holiday.
    How can I do this with another code?

    Looking forward to your soonest response.



  135. Hi I need to calculate comp days for when an employee works on a weekend due to work travel. I still want to leave the sat/sunday as non-work days but I can’t get the excel doc to calculate any annotation (OT) on the weekends. Any ideas on how I can add this into the existing document?


  136. Hi, the sheet is fantastic, however I would want to add time for employees for each day. When, say if I add 10:03 to employee 1, leaves increase by 1. How can I mitigate this issue?

  137. Great spreadsheet!

    How can I make it to where an absence will drop off after 90 days?

  138. I want to keep track of total SICK days taken, but they are not to be counted against (deducted) from leaves remaining. How to make this change?

  139. I really like the look of your template and would like to use it. Do you have a leave template that includes part-time workers whose holidays are set in hours, not days?

  140. Hi! Your template is greatly useful. However, there is one thing you haven’t mentioned. It’s the compensation day. For example, we have a holiday from Friday to Tuesday and the next Saturday we will go to work to make up for Monday (this day isn’t a holiday but we’re off to have full holiday). How can I mark up only that Saturday as working day. I think there may be a similar table as holiday sheet.

  141. How can I change the leave codes to reflect different colors? I wante Maternity to be different color than regular vacation.

  142. Hi! Thank you for this. However, I am unable to use is as the triangle icons do not work. The pop reads : Macros in this document have been disabled by your enterprise administrator for security reason. How can I fix this?

  143. Thank you for this! Saved me a ton of time. Two small comments:
    1 – Macros are worrying. I inspected them before I allowed them to run. They are safe but you might want to mention them in your blurb.
    2 – Give some instruction about how to create new conditional formatting rules for custom coloured leave codes.

    Thanks again!

  144. I want to add one more leave type that is short leave and assign it value 0.25, can anyone help me for that?

  145. Hi. Thank you. This is very helpful template. Do you happen to have one for timesheets for employees?

  146. It would be great if you could actually track numbers of hours of leave taken per day for each category (so rather the “A,” It’s “A8” for a full day’s annual leave; or “A5” for five hours annual leave on a day). For instance, if someone goes to the doctor and it takes three hours, it would be great to log three hours of sick leave on the specific day.

    Also, it would be great to test the number of days leave taken against the maximum allowed (for example; a maximum of 10 day’s annual leave per year; or 30 day’s sick leave per three year cycle).

  147. This is amazing! I cannot see the triangles where you change from month to month but they are there as I can click on them and change the month – how do you make them visible?

  148. Hello,
    how can I change the colour for highlighting the noted spots??
    For example, once I add s, v or h1, I Would like to have green, light green, blue and yellow instead. And I would like to have a few different colours for highlighting.

    Can you please help me with this?

    • I just adapted the sheet for this very thing. It’s not too tough as it’s done with conditional formatting.

      For example: Create a new conditional formatting rule using Classic and Formula =B8=$NX$10 applying to ‘Leave Tracker’!$B$8:$NI$27 and give it some custom colours. You will need to remove the NX line from the rule that currently turns it red. Good luck!

  149. thank you
    it is fantastic
    I have 2 questions to reduce the employees number
    2. in some countries the holidays are more than one day, even up to 7 days how to adjust it

  150. Hello, this document is amazing. However, I would like to add a column between the employees name and the first day of the month and I can’t seem to find how to do that without impacting everything. Any thoughts?

  151. i wanted to know how i can remove employee 3 from February month. i want this employee only in January. From Feb onwards i dont want this employee in the leave track as he is resigned.

  152. hi, since we have different week offs for employees how can i mark week ends for each employee seperately?

  153. Hi Instead of H2 which is Half Day Leave 2, I want to create a new code altogether as Unplanned Leave but still the value shows as 0.5 whereas it should get changed to 1 ideally. Could someone please tell me how to alter this change?

  154. This leave tracker template is the best I’ve seen. Thank you.

    Just wondering if it’s difficult to add a section where you can enter the number of leave days that are allowed and so the days left over can be tracked.

    Thank you again and looking forward to a possible solution.

    • Hi Marc,

      It’s not difficult. I just inserted some columns at the end and added in the formulas.

  155. Hi! Thanks for the amazing tool! Any idea how I can print a document showing all months at the same time?

  156. Hi! Thanks for the amazing tool! Any idea how I can print a document showing all monts at the same time?

  157. Hello.

    This is a great tool to track the annual leave for the workers in the company. I am a big fan of the different reason for leave. However, there is something that I think will be useful and maybe someone can help me with. For example: If we have a worker who works in some of the holidays, later need to be compensated with an extra day. How we can track the extra days for the workers who work on holidays?

    Thanks for your help!

  158. Hi, this is a great tool for leave tracking, thank you!! Is it possible to change the colour of each leave type? For example all codes highlights the cell in red, is it possible to have different colours for each different code?

  159. Hi, this is a very nice leave tracker, but i have employees from multiple locations and their holidays are different; is there any way we can set that up?

  160. Hi This Leave tracker is fantastic. Thanks for sharing. However my HR is running on monthly cycle of 16th – 15th every month. First month 16Dec18-15Jan19, Second month 16Jan19-15Feb19.. and so on. Can the dashboard and vba formula be adjusted to achieve this?

  161. Hi,

    This is an excellent leave tracker.
    I have few questions:

    1- We do not half days leaves so how can i remove it from formula and instead use my own codes for full day. I tried removing 0.5 with 1 in formula but it didn’t work.

    2- How can week ends be included in tracker calculation if someone is assigned to work on weekends?


  162. This Leave Tracker Template works very well. The one addition I would like is ability to assign points to each leave type instead of just counting 1 occurrence or the half day. Attempted change but could not get to work. Any change to yearly or monthly column generates InValid error.

  163. This tracker is really cool , just what I need. I have one query though, if I want to change the leave codes and delete few of them , the formula for the color coding does not work can you please help me with that

  164. Thank you very much for this. It’s simple, works on a complex level and is EXACTLY what I need. Thank you for making it available.

  165. hi

    i am not able to change the month to feb in leave tracker.
    i get a message that macros disabled
    can u help

    • Hi Ritu,
      You need to change the spreadsheet type to a “macro enabled” Excel Workbook.

  166. This is tracker is great!!! i have a question though. we have limited leaves at work and no overtime pay but we have “offsets” which are excess work hours that we can use in place of leaves at certain cases. how can I count cumulative offsets and offsets used apart from the leaves in one spreadsheet?

  167. fantastic work
    i really thank you for this
    but i have question, how can I print all the months in on paper?
    i tried and all i can print is one month, the rest months does not appear to be able to be printed,

  168. I am trying to add a few codes in the table and cannot figure out how to get them to calculate correctly in the leaves this month and leaves this year columns. Can you assist?

  169. I know this was created a few years ago but I am hoping you can still help. The spreadsheet is perfect for me except the holidays listed are not part of our holidays. I was able to make the change to the list but it doesn’t include the column in the calculations. Can you help me? For example, my employees work on MLK and President’s day.

    • Just delete those days your employees work off the holiday list. For holidays you have but are not on the list, simply type in the name and the date. They will populate.

  170. Great work. I am loving it. Please if i want another column (between NJ and NK) that displays the amount of half days taken per month so as i go to the next month it resets; so that at a glance i can know how many half days an employee took per month…id be really grateful.

  171. This spreadsheet is excellent, exactly what I was looking for to track leave. Thank you 🙂 Is there a way to change the colour coding of vacation leave so you can see the difference immediately between sick and vacation

  172. Hello! Thank you so much for sharing this resource, it is amazing. Is there anyway you could please guide me through changing the dates? Our pay period isn’t based on the month, it’s from the 20th to the 20th (for example, 1/20/19 – 2/20/19 counts as one pay period). Any help you could provide would be greatly appreciated!

  173. This excel is very nice. But how can I add minutes of late in the tracker so that it will add the leaves and lates. Thank you

  174. Do I have a control on this tracker once downloaded? im afraid it will be corrupted since this is my tracker for my company’s leave/s

  175. Please check the week number – its not working “#N/A” this is what I see in the box….
    I have both of your versions – 10 and 20 employees. . . .

    Thanks so much for your amazing work.

    Kind Regards,


  176. To the Hayley Bama and Bircbox. Ex editor from More magazine Abby Perlman recently got involved in dirty coraption business with crazy CBS anchor Otis Livingston to steal money from Bircbox employees bank accounts. Never deal with Abby Perlman and Otis Livingston they belong in prison!!!!!!!!!!!!!

  177. How do I create a column after the employee name . I need 4 columns. but when I add the date changes.

  178. Is there any way to have multiple sheets of these work on one file? When attempting to use it for multiple departments it gives an error “Runtime error “1004” Method ‘Range’ of object’_Worksheet’failed

  179. Good day! I have loved using your leave tracker this past year for 2018, I modified it for my vacation schedule in Canada 😉
    Will you be providing a 2019 leave tracking soon?

  180. helo this leave tracker is very helpful, just having a problem on changing the working day example in this program SAT and SUN is considered day off but in my case our day off is Friday how can I edit the codes. Please kindly help me because I am not really good in these. Thank you!

  181. i love the template, but would you be able to share how did you create the top portion where you click the arrows for the months to change, as i would love to use that for some other of my sheets. Thanks

  182. Hi. Can you protect the Leave Tracker worksheet so the cells cannot be manipulated except the scroll bar – moving month to month, brining up the data for each month?

  183. This is a great sheet !!!! Thanks so much.
    The only issue I am having that if I put a password on the sheet so users are only able to change the month and edit the cells, the arrows gives me an error when going through the months

  184. Hi,

    This is awesome tool! We have employees with different work week. I would like to add all the names in the same spreadsheet rather than copying the same workbook for different work week. Is this possible? Looking forward to your reply. Thanks.

  185. Hi,

    Thanks for the tracker. I would like to customize the tracker to run with our financial year from July to June. could you kindly assist with the codes for that. in addition, i’d like the leave to only count vacation days and half days.

    how do i change the code to that?


  186. I have changed some of the info on the holiday calendar…adding some more lines and they did not change to orange on the spreadsheet indicating they are statutory holidays. How do I correct that?

  187. Hello there! This spreadsheet is awesome! However, the only problem that I see relates to the counting of holidays.

    Per the US Department of Labor’s Employer’s Guide to The Family and Medical Leave Act (WH-1421): Calculating FMLA Leave…

    Time that an employee is not scheduled to report for work may not be counted as FMLA leave. Only the amount of leave actually taken may be counted against the employee’s leave entitlement.

    When a holiday falls during a week in which an employee is taking the full week of FMLA leave, the entire week is counted as FMLA leave. However, when a holiday falls during a week when an employee is taking
    less than the full week of FMLA leave, the holiday is not counted as FMLA leave, unless the employee was scheduled and expected to work on the holiday and used FMLA leave for that day.

    An employee does not accrue FMLA leave at any particular hourly rate.

    Would you be able to address how to correct the leave count?

  188. I am with Sharon Williams & Laura. I really need to be able to change the color of the codes so they are more distinguishable for my upper management team. Please advise ASAP

  189. Can I change colours of a code. For example, I may want casual to be a different colour from vacation

  190. This is exactly what I’ve been looking for. However, I changed the list of holidays to reflect the holidays that we offer. How do I remove the orange highlight on the column for holiday that is a workday for my employees and not a day off?

  191. Hi, this is very useful in my end.
    -I want to know what is the formula if I am going to breakdown the Leaves per month.



  192. Hi this is great and super useful. I want to ask if I can do the following:
    – Assign certain Holidays to employees (example certain holidays only apply to certain employees located off-shore etc)
    – How can I add a “Late” or “Tardy” counter?
    – I want to change Work from Home as a valid working day therefore will not count as a leave

  193. Hello, this is exactly I need, just it would be nice to have the month, days and some other words translated into slovenian, how xould I do this?

  194. I love your tracker. If there is option to mark 2nd & 4th Saturdays as Non Working day, it will be more use full. I know there is option to mark as leave days, but i need it show as non working day. Please look in to it and add this if possible.
    Thank you.

  195. Hello, please advise how I could add two columns for back-up persons within the view for each month. How should I edit the formula? Many thanks for the help!

  196. I love your tracker, but is there any way to track using hours instead of days? A few employees work 10 hour days instead of 8, and we are also allowed to take a few hours of vacation or sick time. Thank you.

  197. Hi,
    I’m trying to add the Team manger, however, got error with the date and is there is any way to get over view of each month team manger wise and month wise.

  198. Hi, want to ask if there’s a way to summary into another sheet the balance of leave taken from leave tracker sheet? I have main menu to show all the balance used but got error when I link formula to the “Leaves this year”. Anybody can help? thx

  199. Is there a way you can assign each employee a total number of leaves alloted through the year so the they can be deducted. For example, employee 1 gets 10 “leaves” a year and as she/he uses a vacation day/personal day then it gets deducted from the total number? thank you!

  200. The leave tracker sums W worked from home, it is quite cool idea to track this but it is not counted as holiday so how can I amend the sumproduct formula?

  201. I’d love to have a way to specify the working days for each employee. In my organization, we don’t all have the same workweek. That said, what a fantastic resource this tracker is (especially for nonprofits like us) — thank you!

  202. Wondering if it is possible to add another column next to Employee name. The additional column would be group/reporting manager.

  203. Hi Sumit,

    Excellent tracker, thank you so much. Would like to check if I can delete the leave codes as I do not need so many in my tracker.

    Your reply will be highly appreciated.

    Thank you!

  204. If I want to add more leave name and code can I do it? I tired doing it it works however when I put the code in tracker it wont give me the red color.

  205. Do you have a 2019 one yet? I tried changing the cells and it does not work. it will not add days of leave.

  206. Just wanted to ask for the 2019 template. I have tried changing the year but it did not work correctly.

  207. is there a way to log days worked in Lieu (eg. code L day worked in Lieu reduces the total days taken)? great spreadsheet by the way!

      • Hello I did change the date and edit the holidays but is does not change the leaves per year column. How do I change that?

        • you should only change 2018 to 2019 then it auto populates with the dates, the leaves per year auto populates when you add holidays, maybe you have lost some formatting, download and try with a fresh sheet

  208. Can you please update for 2019? I like this spreadsheet but cannot seem to edit correctly for 2019.

  209. This is awesome!! Nice skills! I’ve tweaked the layout a little and added a team column next to the name column. I’ve adjusted to macros to show the first 2 columns (A&B) when the month skip arrow is pressed. The only thing I’m having an issue with is now January 1st is now the last entry on the calendar (NJ5 is Jan 1 every year, should now be in C5). This is clearly because I’ve added a column and its making reference to the wrong cell to start on, but not sure which of you formulas to adjust. Its not even an issue as all the dates are there but, my OCD in overdrive!! Please help…

  210. Can you fix your formula for “Week #”? You have it as a hidden row. It’s row 7. It won’t work since it references Sheet3 when of course there is no Sheet3.

  211. This is fantastic and has helped me so much with my employee’s leave. Can you please do one for 2019? pretty pretty please!

  212. I like the leave report but I need more information – Each one of my associates accumulates time/hours on their anniversary. I want the tracker to track time used as well as time remaining for the year. How can I do that without messing up the sheet?

  213. How to add columns without disturbing the formulas. need extra colum for department and id #.

  214. When an employee leaves and want to delete the row in the middle of the leave tracker spreadsheet. The leave tracker does not work when leave code is entered for other employees how do you delete the row??

  215. Hi
    If I want to see two months data together then what changes I should do in the file. like I want to see October’18 and November’18 at once on sheet.

    Please let me know what changes should I do in macro and date formula.

    It will be huge help.

    Sachin Sharma

  216. Good day, i hope that there is some help that i need with the following problem, i have a sheet, kolom A has the name APG with all kind of codes.
    and on row 1 starting from kolom B there are 30 cels with code AA AB AE enz. the problem is that i need an formula to get the result of the APG and the code AB or something like it to give as a result for example 50%, is this posible with a formula.
    Hope that you are able to help me with this problem.
    Best regards Teet.

  217. Hi hope you can help me, there’s an error on my leave registre it says ‘ unable to set the Hidden property of the range class” . What is this means?

  218. Is there a way to delete some of the leave codes completely from the form? For example, I only need 4 codes, when I delete the extras I disturb majority of the form.

  219. Dear all,

    first of all, thank you very much. the excel to count annual leave is very helpful.

    is it possible to insert an extra cell or column for starting date of each employee my leave, let say, 12 days per year, and “as of today”, how many days he or she is entitled? Then, there’ll be the balance of each staff.

    Also, just in case, use the financial year Apr 2018 to Mar 2019 instead of Jan 2018 to Dec 2018, will it be difficult to modify the excel?


  221. Hi Sumit, this tracker is very useful! I would like to know if there is anyway we can record in one day, an employee took 1/2 day annual leave and another 1/2 day unpaid leave?

  222. Thank you LOVE this!! Is there any way to get this to track hours down to 15 min. 1/4 of an hour? We do time in hours and down to 15 min. of Personal Time. Thank you again for this it is AWESOME!

  223. I love this tracker! Thank you so much for making my life easier during 2017 and 2018 🙂 Is there a chance for 2019 one?

  224. Great Job!
    Can you please help me how to add additional columns to set Total annual leave for each employee and to display total leave taken and remaining for each employees.

    • Hey Lisa, You can do that using conditional formatting. Select the entire leave area section, go to Condition Formatting (in Home tab) and click on manage rules. There you can create your own rule

      • So, I have to do this for each month? Is there a way to apply this once for all months/years?

      • Hello Sumit, I love this tracker but I need to use it to track points that my companies drivers get for certain driving violations. That being said, I was wondering if you could let me know how to change the values for the different codes? For example I would be changing Sick to Preventable accident and would need to change the 1 to a 2. Any help would be greatly appreciated!

  225. How do you use the triangle icons next to the month names to go to the next/previous month? so that it shows you the selected month only and hides all the other months. can you plz share the method?

  226. Hi Sumit. The leave tracker is good. I have one query though. When I select Work from Home, it gets added as a leave in the “leaves this month” and “leaves this year” column. I don’t want it to be counted under the “Leaves this Month” and “Leaves this Year” column. How can I enter WFH without increasing the leave count?

  227. Hi, Many Thanks for the template. However, even though the boxes change color when I enter staff leave codes, there are no changes at all from column NJ to column NV. Please assist.

  228. Hey Sumit, you have done awesome work with this excel sheet. One request, if you can also break up monthly leaves instead of just giving total for the month would be very helpful. I tired playing with it with countif formula but once i change the month count of monthly leaves don’t change as columns gets changed for that month. Can you please email me if I am not asking too much. Thanks

  229. hi, if an employee is already separated and supposedly not included let say in do you arrange the employee column? thanks!

  230. Hi Sumit,
    Thank you for sharing this spreadsheet. I am trying to add something that would show the number of PTO/Vacation/Leave days each individual employee had and then as they used them and they were inputted in the tracker, it would minus them from the individual employees “bank” of days. Can you please help?

  231. May i know how can we change the leave break up code for half days as well. If i want it to be reflected as .5 and not 1 in the leave break up of half day. Thank you

  232. HI Sumit, thank you for this very helpful tracker.. Quick question, how to add more leave code?

  233. Hi, I’m not able to change the month by clicking the triangle icon.

    It says “The macro may not be available in this workbook or all macros may be disabled”

  234. Hi, I’m not able to change the month by clicking the triangle icon.

    It says “The macro may not be available in this workbook or all macros may be disabled”

  235. Hi Sir,
    Your Leave Tracker templet design is wonderful If it possible for you, a humble request from my end please make a tutorial video on this.

    Rajib Das

  236. Hi! First, your work is just great! Thank you that you share it, making my life easier 🙂 I have one question regarding the leve types. I would like to remove possibility of half day leave. When I do it however, the table becomes marked with gold (yellow). How can I get rid off those options (H1 and H2) and still keep the functionality of the table?

  237. Hello Summit,
    Thank you for this excel sheet – I am trying to change the colors to the different leave codes, can you tell he how to do that?

  238. Thank you so much for this! Actually, our employees have different days off… Is there a way to reflect this?

  239. What should i do if individual staff a have a different off date? How do i indicate in the excel sheet?

  240. hello – thanks for this great spreadsheet. Is it possible to assign a different color to the different leave types.

    • Hey Tracey .. You can do that by changing the conditional formatting rules. In the current tracker I have kept only 2 colors – red for full day and yellow for halfday

      • Hey Sumit I need you help. in your summary of leave. It shows the total of leaves continued from previous months . for eg.
        june I added Sicksheet 2 and annual leave 3 and while again going to enter in july month it gives the same total + adding the july sickksheet and annual leave.

        Isnt there anyway where it shows the summary for only july aug sept n so on..please do let me know.
        If possible please reply me on my email.

  241. Great tool!! Some of the holidays you have listed are not holidays for us. I deleted that row from the Holiday table but it did not change in the leave tracker. What am I doing wrong?

    • Hey Michelle.. instead of deleting the row, simply delete the data and enter the one you want to be considered as a holiday.

  242. can you make one for shift workers, 7 working days and 2 off then 7 working days and 2 off then 7 working days and 3 off

  243. Hi Sumit, I love your template thank you!! Question though, I need to add another category, Leave upcoming approved. Are you able to assist in how I would add this and the formula I need to use? Thanks Faye

  244. Hello, is there a way to copy this sheet multiple times? I tried to do this to separate groups of people to track, and the month arrows give me an error when I try to change it. the debug highlighted this:
    “LeaveTracker.Range(Columns(Range(“A3”).Value * 31 – 29), Columns(Range(“A3″).Value * 31 + 1)).Hidden = False”
    I’m not sure why it wont work, the first sheet works just fine.

  245. This is awesome Sumit! Is there a way to break up the holidays between off-shore and on-shore?

  246. Only my Sunday is highlighted in Grey, How can I get back the Saturday one? I used last years and made the changes to the year as discussed above

  247. Hi, My team members are located in some different countries. Is it possible to add Location , and according to the location – National Holidays, and accociate the location to a team member?

  248. Hi, first I want to say how handy and easy and amazing this leave tracker is; however when I shared it with my boss via excel online it does not allow you to click to the next month. Why?

  249. Where can I put the monthly leave. For ex. 1.7 days per month, as well as the leave brought forward from previous months?

  250. It would be nice to be able to have different colours for different leave. i.e Sick Leave red with white text, Vacation Leave green with white text so you can easily see what leave is taken

  251. Great template – amazing what Excel can do. There seems to be a typo in the formula for Leaves this Year – the IF statement is checking if H1 or H2 are used for calculating 1/2 days, but the second part of the formula repeats $NX$16 instead of $NX$17. For some reason, any change I make to a formula results in a #VALUE error and not sure why.

    • I’m having exactly the same issue. Need to include a H2 value and it’s subtotalling correctly in the leave for that month but not for the year total. Anybody have any suggestions please?

  252. Hi Sumit. great thank you.
    Could you please add the following.
    I need to be alerted if there is an employee who took more than 2 leave days in a 8 week cycle. And if an employee takes more than 1 consecutive leave days.

  253. Can I delete every code but vacation days? we do not need it broken up. Also, how do I go about putting our company header on it?

  254. Hi! Thanks so much for this template. It works very well. I was wondering though if it would be possible to customise the ‘weekend’ days for each employee, as where I work we take different days off. Or maybe you could give me some tips on how to do that. 🙂

  255. hello. i have altered the holiday list,but i cant get the automatic change in color on the tracker

  256. Is there a way to track hours instead of whole days or half days…. for example we had an employee leave to bring his child to the doctor and was gone for two hours.

  257. @ Sumit Bansal :
    Hi, I found this tracker very helpful and easy to handle in the single sheet for various months. I was trying to change the leave categories and add new one as some are not applicable in my working context. Could you please help me in getting to know how to change the categories/Delete Categories. my email id

  258. This spreadsheet is great! How do I change the colours of the different types of leave please? I need to be able to see standard vacation leave in a different colour to compassionate leave, paternity leave, etc.

  259. Very nice template Sumit, thank you!
    Is there an easy way to modify the template to show 2 or more month at the same time? I would like to use it to log vacation that usually span either jul-aug or dec-jan.

  260. Hello,
    I only need certain leave name, code, and leave breakup information. Once I delete certain columns and information, the dates are automatically filled. Please assist.

  261. Hello,
    I have employee working different days of the week, how I allocate the working days to each employee, thank you

  262. Excellent sheet… one question… is it possible to insert leave by the hour… we allow staff to take single or multiple hours of leave that do not necessarily fall under the bracket of a half or full day. Was wondering if this sheet could be edited to incorporate this.

  263. Hi This is a great software but I’m having trouble with the month scroll arrows. When I start at January, 2018, and click the orange arrow next to January to go to February, there is only a blank page with the columns and rows but no info in them. I had already done a couple of people in January and wanted to see February. So I filled in the columns for February then went back to January and the names are there but the columns are empty but the days off that I filled in are there.

  264. hi i have a question how did you link those arrows to the next month ive tried everything. please help btw very inspiring template well done

    • Hi Steven:

      DId you ever figure out how to go to the next month? I’m still having problems with those scroll arrows. Thanks

  265. Hi Summit, in my case I have to select different weekly off days for each employee. How to execute it.

  266. That somebody can do this and leave it for us for free is quite very commendable.

    Whao! May God bless you real good.

  267. Amazing ~ Thank you very much.
    Could I insert more row for over 50 employees like 130? I am try to insert the employees code and number column before employees name. But the sheet does not work. How can I fix it?

  268. Hello, Well done for the planner. Is it possible to deduct two days for a Public holiday instead?

  269. Was wondering if there is a way to enter a code for unpaid leave days that would allow for there to still be a count of paid leave days used/left

  270. i would really like the spreadsheet for Excel vacation tracker. I would like to have a sheet with 50 plus employee and one that i can copy and paste to another sheet within the same workbook. We have 3 shifts with different departments. I would like to set this up so that each department in on a different tab.

  271. can i copy and paste the vacation tracker to another spreadsheet within the same workbook?

  272. I tried removing some of the unwanted leave codes.. it changes the cell color. Also, I don’t want Work from Home to be marked as red…please help

  273. Its really helpful thanx, my issue is a little different although. Can we set a limit for the no. of leaves that each employee can avail for eg: Annual Leaves should be less than or equal to 15 and also that the no. of leaves available to each employee can be set differently as per their allotted leaves.

  274. Hi, is there a way to set it to keep track of hours taken and not days? I believe right now it is set to days only.

  275. How to use Use the triangle icons next to the month name to move to the next/previous month (the template updates itself to show the dates for the selected month). There is a short VBA code that runs in the background whenever you change the month. It shows you the selected month only and hides all the other months.

    Let me know how to select it.

  276. Hi. Need to add “P” (Number of days present) to the list which needs to be highlighted in green. How to do it?

  277. Hi Sumit,
    First of all a big thanks to you. Great work Dude !!

    I need to include “Comp-Off” in my leave type which should not be considered as leave. Please let me know if there is a way to accomplish that?

    Thanks in advance

  278. Hi Sumit! Thanks so much for this! I was trying to add two identifier columns in addition to employee name but i was unable to do so without making the sheet incorrect. Could you please inform me how to complete this?

  279. Great work Sumit! If an employee takes a 1/2 day sick how do I get it to reflect on the sheet as a half day sick?

    • Also if I have to keep track of historical data e.g. 2017-2020, would I be required to develop a new sheet for each year?

  280. Hi Sumit, So glad to see this really i was in need of this.Thank you so much. Please teach me more because want to learn more from you.

  281. Hello, I downloaded this template and it come in a zip folder. And I do not see any xl sheet there. Can you please guide me ? Thank you

  282. Hi Sumit, I’d like to add 2 more rows from A sections, please advise how do i do that? 🙂

  283. Hi Summit is it possible to add Summary Sheet for easy checking of Employee? and showing Employee and all the date of the leaves they taken for whole year?

  284. Hi Sumit. Thanks a lot for sharing this Leave Tracker. Really good. Please how can I move the Scroll Bar from the bottom of the sheet to the top? Thanks. Abraham

  285. Hi Sumit, I need your help on this. How can I change the colour of the leave record for employees? and am I able to create A1 to select for different department? Please assist me on this. Many Thanks

  286. This there any way to return the dates from which the S,C,V,H,M (types) are applied to the calendar, and output them to a cell via I’m guessing what would need to be an INDEX by type?

  287. Hi – I have downloaded the zip and did the extraction. I am not seeing the executable file to launch the spreadsheet. I’m not sure if I’m doing something wrong, so would appreciate help in opening the spreadsheet. Thank you!

      • Sumit, I made tons of changes in December to prepare for 2018….and just saw your revised version… 🙂
        I just want to add more half days – the new version has 2 half days and I tried adding other codes but counldn’t get them to count as 1/2 day. How is that done?

  288. Hi I love this spreadsheet thank you for sharing. Is there a possibility of editing the employee’s? So say we have some employees who work full time and part time. So It calculates the leave of their individual hours?

  289. The VBA for the scroll bar needs an edit but I can’t determine what that is…when clicking on the right arrow of the scroll bar it jumps from February to December.

  290. Hi- Is there a way to change the highlighted colour for leave code? Process is for the TL to enter the leave type, and then once confirmed in Pulse update that particular leave to another colour – this will help to track any non updated leave for Payroll purposes.
    Thanks in advance!

  291. Trying to leave a blank column before A1 but it seem not working. How can I insert an addition column without affecting the formula?

  292. can i add another leave type for which it counts it as 0.5 day like it does for half day??
    i want to bifurcate planned half day and unplanned half day.

  293. Hi Sumit, thank you for the amazing piece of work.. i want to have a link of updates sheet .. which include summary of the leaves as well as employee ID and position.

  294. This is the best format out there for leave trackers, as it deals with the whole team not just individual employees. But I’m running into 2 issues. (1) I change the number in A1 and use the scroll bar to switch months. But while it’s in one month, say November, and I click on the right arrow on the scroll bar, it doesn’t go to Dec. It’ll go straight to Feb of the next year or some other random month. It skips, it never just goes to next month. (2) I want to add more employees. So I copy the data and formulas and insert a new row. Everything works, formula-wise, but then now the scroll bar is covering the bottom most row, since the scroll bar doesn’t move even if you add rows.

  295. can anyone guide me how to calculate the leave balance, considering leave balance is different every year
    Mr. ABC joined 01 Jan 2014- what should be his leave entitlement until December 2018
    everyone is entitled for a leave balance as per below
    2014 – 34 days
    2015 – 34 days
    2016 – 30 days
    until 08 July 2017- 30 days
    after 9 July 2017 – 34 days
    2018 – 34 days

  296. Hi,

    Is there any way I can add additional sheets as tabs at the bottom of the spread sheet? When i try copy the tab and use the scroll bar i receive a run time error 🙁

    Brilliant other than this Sumit… top marks sir!

  297. the file only downloads as a XML am I doing something wrong? I’ve also tried your direct link

  298. Hi Sumit – I desperately need to use the leave tracker but we track based on anniversary date rather than calendar year. Can the Tracker be tweaked to accommodate me? Thanks! This tracker is the best on the internet!

  299. Hi Sumit, is there anyway to make this template based on a bi-weekly (2weeks) schedule? With teh first bi-week defined by the template user?

  300. Hi, maybe a silly question but where must I download it too for it to work? It looks great

  301. Hello, is there a way to change the color? i mean, i would like it to be green instead of red, how could be it done? Thanks

  302. I just downloaded the tracker, but not able to find how to open the xls, where it’s located in the downloaded folder

  303. your holiday tracker is excellent with one issue. the scroll bar doesnt work and doesnt advance by a month, it just goes to the end of the year. I cannot see a fix for this. Do you have any suggestions?

    • Hey Siti, you can change the value in cell A2 to change the year. Note that if you already have one for 2017, you need to create a copy, delete all leave already marked in the tracker, and change the value in cell A2 to 2018.

      • OK.. another matter for me is how i can add listing for the leave break up? because we have half day unpaid leave and half day for annual leave

        btw, thank you so much for sharing this excel leave tracker..

  304. hello, thank you as this is a very very fantastic template,i am wondering how do I add more employees as I have 160 employees. it would be great if you can advise me how to do that.

  305. I love this template. I’m not good at Excel, but this doesn’t require me to be. How would I change the casual leave, which we don’t use, to something that we do use, comp time? I would love to have this box allow me to put in, for example, C2, which would equal two hours of comp time and have that reflected in the month and year totals. I figure the values would be based on .125 per hour, i.e. C8 would equal 1 day. Is this possible? I already added columns for the fluctuation in comp time off to the right and did a sum formula for the columns to reflect adding and subtracting comp hours. It would just be nice to look back on the month and see how much and when comp time was taken. As of now though if I put anything in a day box it equals one and I have to go back and change the total, which really doesn’t work because the template is not set up for three decimals and it rounds up.

  306. I have employees with different days off on a regular basis, how can it be customized for each employees days off? When I try to change each days off, it is applied to all employees. We work shift works and have schedule through the week with employees having different days off. thank you

  307. Dear Sumit whenever i add new rows the scroll down bar comes in the middle of the sheet please tell me the solution for it so that can manage the template accordingly. and by the way uve done an awsome job. kindly do reply its very urgent

  308. Hi Sumit,
    I love this tracker, I was wondering if its possible to add .25 hours to it?

  309. Hello, no matter how many times I’ve tried, the zip file doesn’t open to an excel spreadsheet. I do get a variety of folders but couldn’t find a spreadsheet within it. Is there a different way of downloading and accessing this template?

  310. This is really great! Thanks Sumit!
    I have one question, how can I add a field after the Employee Name? I’d like to add more like ID, Team, etc. Thank you!

    • I had the same problem. I was using Microsoft Edge. Use Google Chrome and it should download the excel file instead of a zip folder that Edge did.

  311. hi , this leave template is so amazing.
    can we add training leave without adding to the total in leave column?

    • Hey Serena.. You can use a code for training leaves (for example, replace C with T) and the leave breakup section will show the total for that leave code for all months.

      • Thanks Sumit. I tried to do that but it’s adding up to the leave total. How to exclude the training leaves from the total ?

        Thanks a lot. Love your templates

          • Oh i tried to do that too on Column NJ and NK to subtract the training leaves but maybe i did it wrongly, its not getting a value after that

          • Did you try and do this column NJ or NK? If yes, it wouldn’t work as it will break the formulas in it. You can do this in a separate column that doesn’t have any formula in it. For example column NT or NU

          • oh ok thanks. I got what you mean but then it will stay put when i change the month, Hem its ok then thanks. i figure out something else.

      • Hi Sumit,
        I tried deleting few leave codes but after that I do not see the color code. Please advise.


  312. Hi this is amazing. i love it. im just trying to add to the leave training days without having to add to the total number of leaves. is that possible?

  313. Hello Sumit!

    First of all, thanks for the awesome excel spreadsheet! It is definitely efficient, clean, and pretty easy to use.

    I’ve added some changes to the excel, I’ve added additional years since we’re halfway into 2017 and modify some of the codes to be in alignment with our policy. With that being said, the modifications took place, I’ve crossed path with issues with the additional years.

    As I was scrolling through next year 2018, the excel spreadsheet did not clear all data from the current year. What codes that was already plugged in this current year carried over to 2018. How can I go about clearing the codes for next calendar year?

    Also, I would like to update the Holidays page for the year of 2018. How can I go about updating next years holidays chart and so forth? If I am able to update the holidays sheet, will the holidays carry over in the leave tracker sheet for the year of 2018?

    Thanks a bunch!!

  314. Splendid work Sumit !!
    I want to add one more column as “comp-off” in Leave break up as well as in absence code mentioning comp-off as “Comp”. But the thing is Comp-off code should not get calculated or added to Leaves This Month and Leaves This Year but should get calculated or added only in Leave Break up.
    Please let me know how to do this ?

  315. I cannot run macros due to restrictions in work area. How can i use this template without macros

  316. Hi, I would like to add more Employees however please can someone help as to how you move the scroll bar down in the document once employees are add?

  317. Thanks Sumit for the tracker, however the half day leave is not summed up with the corresponding types of leave like Vacation or Sick. For example since half day is not a separate types of leave but if I need mark half day vacation or sick leave it should be summed up with vacation or Sick leave instead of separate H list.

  318. When you create the leave tracker for 2018, can you add a column after the employee name. I’d like to add information for each employees without having to scroll all the way to the right of the spreadsheet. Thank you

  319. I love this tracker so much; well done — thank you for the share! We use all time off (sick, personal, vacation, etc.) as PTO (Paid Time Off) which is accrued on bi-monthly pay periods (26 per year). Do you have any spreadsheets to calculate accrual PTO for salaried and non-salaried employees?

  320. Hi Sumit. Love this template. I was wondering if you could explain how you set up H/h to equal .5 and all other values to equal 1. I was hoping to adjust this leave tracker to focus only on one type of leave, and then break it down by the amount of time, in quarters of hour, taken in one day: W=1.0, H=0.5, Q=0.25, HQ=0.75…
    Not sure if that’s possible. Any thoughts?

  321. Hello Sumit,

    Can you add three more columns on the right like Back Up Resource, Approved By Client and other? I need that for 30 employees

  322. Is there a way to add a second half day or change one of the existing days to a half day as well?

  323. What if I want to deduct the half day from the available leave types (EL,CL or SL)?

  324. Hi.. the leave tracker is one time saving workbook specially for the startups. Thank you for this creation. But I am facing an issue with the sheet. When I mark half day for an employee the leave breakup counts it as 1 and not as 0.5. Please help me in resolving this.

  325. Hi Sumit!

    I am trying to add Employee ID & Location in the beginning of the sheet but I am not able to move the formulas. Is there a way to fix this please?

  326. I really like this tracker, but would like to do some customizations. The first one is a bit of color coding. Right now a half day shows up as yellow & all other absences are red. I am hoping that I can custom color it to have vacation days as green. Can you tell me how to do this?

  327. Hi
    On all saturdays my employees work half day. Is there a logic that I can input such that if i put in unpaid leave/urgent leave, it will become 0.5?

  328. Hi, I have just downloaded this template. It’s fantastic, but i’m not sure if it will work for me. At my organisation we all work different hours, so our leave entitlement is recorded as hours and not days. I am no good with formula’s or VBA’s. Does anyone know how I change the formula from daily annual leave to hours? Any help would be appreciated. Thanks

  329. hello Summit, thanks a lot for this template. saved my day. keep up with the good job.
    is it possible to move the scroll bar down to add more employees on the list?

  330. Hi. I am using the 50+Employee excel and its great!
    But I need to add 200 names….so I though copy and paste formulas down……however the scroll bar starts doing weird things. Instead of scrolling per month on the right, it scroll all the way to the end of the year on one click.
    I looked at the VBA code and couldn’t figure out why it is doing this….

    Any ideas?

  331. hello sumit thanks for this file, i’m getting error when i select 1st month it is displaying as march 2017….what may be the error. can you please help me the get this resolved. thanks in advance

  332. Hi Sumit, is there a way to add two half days one for vacation and one for sick day?

  333. This seems to work great. My company CEO uses it. I have a question/problem though. After he has entered 12 months of data, is there any way to add months, or do you have to start a fresh, blank template? He would like to have a continuous, single spreadsheet that keeps adding months to the end, or has 60 months or more. Otherwise, when a new sheet is created, the previous few months of data will be on a different sheet.
    In other words, is there any way to have a sheet which was started in May of 2016 have data through December of 2018 or longer?

  334. Dear Mr. Sumit Bansal
    Thank you very much. This template is useful and simplified. I’ve solved all the problems.again thank you very much i really appreciate .

  335. Hi Sumit,

    This is a great spreadsheet, thanks for sharing.

    I’m trying to adapt it to use it to track all hours worked by my employees for the month, quarter, and year.

    So in each day on the calendar, I enter the hours worked by the employee. Can you advise how I can create columns that total the hours worked for the current month, current quarter, and the entire year? I’m trying to learn how you set up your {sumproduct:offset} forumula, but don’t quite understand them as of yet.

    Thanks again,

  336. Hi Sumit, Thanks for the template. However, I am unable to open the worksheet. Its downloads a zip file in all kinds of xml and other formats which are not accessible. can this be shared on email –

  337. Hi Sumit thanks for the great work. I am trying to add more employees in the list but the scroll button is not moving down when I am trying to add more rows can you help me on this please.

  338. HI SUmit,

    I would like to add to column with repsect to leave breakup like “L” for Late and “P” for permission. But I do not want to add this in Leaves this month and Leaves this Year column

    How do I proceed for this


  339. Hi Sumit, I tried, the months are not changing properly and once i try switiching from Jan to Feb the leaves entered are wiped out or stay there.

  340. Hi Sumit, I just downloaded. I am not sure how to get to the template. I see al XML files. Could you assist please. Do I need to open those XML files with any application?

  341. Excellent program and I use it regularly. However, can I have it amended to not subtract Sick, Personal Days etc. I just need to add these days, not subtract from any other entitlements. Since our company has no benefits, I use sick days/personal days as 1 day and I need to add these up through out the year….Thanks. John

  342. Wow… what a great post! That helped me a lot. I would like to share with you a great service to fill a form online. If you ever need to fill out a form, here is

  343. I have 80 employees to be added but not able to add employees in the sheet.If I insert new Rows then all formula gets disturbed. Please suggest me how Can I add new employees?

  344. I download the sheet and start working, but i move to the next month all the leave history in the previous month are shown, and pop macro may not be available in the w.b or all macros may be disabled.

  345. Hi, thanks for this amazing template.
    However, can the same template be used as a year calendar, where I can have a year overview of absences.

  346. Hello sir, this is very useful template for me. i would like to know how did you do the scroll bar. it scrolls the column labels with it. could you please do a tutorial of it?

  347. Hey, I tried using the excel in office online through sharepoint, but seems the scroll bar to change the months is not working at all. Could you kindly assist how the scroll bar can be enabled in the online version so that your excel sheet can be used. Its very urgent, would appreciate if you could respond asap. Thanks

  348. This tracker is great. Except for one thing. I updated the list of holidays, but some of them are not highlighted orange for some reason. Anybody knows why? How can it be fixed?

  349. When I want to add different employees in other month the employee also delete from first moth what is the solution? can anyone help.

  350. This is awesome!

    How can I move the scroll bar down if I insert additional rows?

  351. I really like this Leave Tracker, but have one question about it. Is there a way to add a couple more columns next to the “Name” column?

  352. Hello Sumit, Thanks for your template it is great , can you please help me how to add additional column fjor ID and position in template 2017 I could not use the template you added additional column in version

  353. Hello Sumit, thanks for your template it is great 🙂
    Can you please help me how to add additional column for ID and position in template of 2017 I could not use the template you added for 2016 ?

  354. When I click on the scroll bar; it says; “Run Time Error: 1004 – Application defined or object define error”

  355. Hi Sumit,

    Would it be possible to add extra columns next to name, such as team,…?
    Because every time I add a column, the first day of the month January disapears

  356. HI Sumit
    I can download the 2016 version fine and runs in Excel.
    However when extracted produces several folders with XML content and not an Excel workbook .XLSM.

  357. Hi, I am in need of an excel sheet which can record attendance with exact time of login and log out automatically. When the employee opens this excel sheet he/she shouldn’t be entering anything. Excel should record the time of login and cannot be changed in any case. Should have a button, when pressed excel should record the log out time. This will show how much time has he/she worked. No manual intervention.

  358. Thanks for sharing this. It’s very helpful. I wonder if you would be able to customize some parts of it for me. Some of my staff have weekends as work days. How do I make weekends work days for selected staff. Also, some staff have some public holidays as work days. How can I make some public holidays work days for selected staff? I’m happy to pay for customization to achieve these outcomes.

  359. Thanks for the template!!! If we also want to track WFH (Work from Home) count of employees, how should we do it? It should not add up to holidays, but should have two similar columns to WFH per month and per year. Can you please update it to track WFH also?
    Thanks in advance!!

  360. The Leave Tracker is awesome. I need to track 2017,2018,2019 and so on in one excel sheet.
    When i copy worksheet from one to another the scroll bar doesnot work. Please help

  361. Hi, this is an absolutely amazing tool but I have one question for the 2017 version. When I downloaded it and opened on my desktop everything worked perfectly. But when I wanted to open it in my drive google docs it started to change the dates – now January 2017 is not starting with day 01 (Sunday) but starts on 31st (Sunday) and the month of January does not end with 31st Tuesday as it should, but Tuesday 30. Does anybody has an idea why?

    • You need to set the starting month from the A1 cell for once and use the scroll bar to change the months. If you change the months from A1 cell the dates would be mapped randomly to show some different value but exact.

  362. hi there. This is going to sound a little weird, but what program does this use and what file do I open? I downloaded the above and tried to open it in Excel but nothing…. please help?

  363. @sumitbansal23:disqus ,
    First let me say thank you for this awesome template, I am having a little bit of trouble getting it or any other schedule to accomplish what is needed and would love to talk to you about it and see if you can help.

  364. Hi, in the our office set up, people instead of getting paid double for working on holidays, they earn an extra VL that they can use for some other day. How do I incorporate this? Awesome template btw, thanks!

  365. Hello Sumit,

    Your Leave Tracker is amazing, and I am trying my best to use it. However, I will like to upload it on Google Sheets so that I can share it with my entire team, but I do not want them to be able to make changes, just to view the sheet. Anytime I upload it, it changes the last day of the month to the first day, is there anything you can do to help. This is going to be a life saver for me if this works. Thanks a lot and I do hope to hear from you soon.

  366. Hello Sumit, It is a very nice excel sheet which you have prepared & completely puts me at ease. I just wanted know from you how to add shift details. My team works in 24*5 form hence I am require to collect their login details. How can I create a LIST to record the same & then use other codes highlighting leaves.

  367. Hi Sumit! Please help me remove the 0.5 value and make it to 1. How do I do that? Thank you so much.

  368. Thank you. It is really helpful. I just have 1 questtion.
    If I don’t want to count U, M as 1 in “Leaves this year” column, what should I do?

  369. Tracker is good, but i am facing some issues. For example if i enter two days as vacation for the month on January and i change the month from the dropdown on the left hand corner then the vacation days which i entered in the month of January are reflecting in other months too. Also if i click the scroll bar, it is taking to the end of the month for the year instead of the next month.

  370. This is really is a brilliant template, thank you,
    I have added additional “leave codes”. Some of these additional “leave codes” I would like to have counted as 0.5, however, they are all being counted as 1.0. How do I change that?

  371. Thanks for sharing however the issue with tracker is that if you fill in one of the cell for any leave type, then the cell remains filled even if you change the month. Ideally, the data from the cell should remain restricted to that month only.

  372. This spreadsheet is great however when I tried plotting the leaves for January, the leaves for that month still shows up on the rest of the months. If you delete the code, it will remove everything. How do you fix this?

      • But using the scroll bar, the month is not changing to the immediate next month. Instead it is taking to the last month of the year and some times to the middle month of the year

  373. Is it possible to change one of the other categories, for example the Personal Day to a 0.5 day as well?

  374. Awesome leave tracker, thank you. How can I add in columns to reflect the employee surname, employee code, and job description please?

  375. This tracker is exactly what I’ve been looking for! Question though, I’ve updated the year to 2017, but the days of the week don’t match up properly. How do I update the day of the week row to be accurate?

  376. Hi! The leave tracker is extremely helpful. Just a small question. I want to upload it as Google Spreadsheet and share it with the management. However, when I upload it, the scroll bar doesnt show in Google Spreadsheet. Any suggestions on how to do it? Thanks.

    • Same problem here 🙂 As per Sumit, he’s currently working on another file for Google Spreadsheet

  377. Hi Sumit,
    Would it be possible to amend the tracker so it was date specific, eg if a worker started working on the 20th of the month, the tracker would then show 12 months based on the 20th being the first day of the month?

  378. Great template, one question/problem:
    – if I modify the formula in NJ8 (trying to change the 0.5 to 1 so that all leave types will be counted as a full day) then I end up with a #VALUE error in the cell, not sure how to fix this

    • I am also trying to do this. Did you find out how to make all leave types count as full days?

  379. Hello There,
    The leave tracker one of the best..!Love the Tool..!
    One que i am not able to find same format and formula in next month Exp-if i scroll jan to Feb found the that the micro may not be available in this workbook or all macros may be disable..can you suggest me for the same

  380. Hi there.
    Love the tool – so useful!!!!!

    Are you aware of a way that will stack 3 months at a time and show the month prior and after the selected month? So a manager can see 3 months of planned leave at a time?

    I have thought about linking multiple macros but I did find it difficult and kept getting errors.

    For example, if we have 3 employees and instead of viewing 3 months horizontally, we want to see vertically. Using what we would like to see for feb as an example I have listed it below…

    January 1 2 3 4 5 6 7 8 9 10 11 12 etc

    Employee 1

    Employee 2

    Employee 3

    Feb 1 2 3 4 5 6 7 8 9 10 11 12 etc

    Employee 1

    Employee 2

    Employee 3

    March 1 2 3 4 5 6 7 8 9 10 11 12 etc

    Employee 1

    Employee 2

    Employee 3

  381. This is excellent! Thank you for the tracker but i have one doubt if i enter SL on Jan 5th its taking automatically next month also ???

  382. This is excellent! Thank you for the tracker. I’m customizing it for my team, however I’m having trouble adding more employees. Need help. I tried copy-pasting the formula to add more members but the scroll bar is overlapping.

  383. Sumit, you have made my life easier creating this. I love it. I am having difficulty tweaking it just a little, I’d like to know if you can help. I want to add 3 columns after the name column, to enter the employees hire date/accrual time/ days left. I’ve been trying, but all the formulas get mixed up one way or another. I’m still a beginner so I have no idea what I’m doing (clearly).

    • Hello Leslie.. You can insert details about the employees after the tracker ends (from column NQ onward). That ways it will not mess with the formulas and VBA

  384. This is AMAZING! I can not wait to use this for a portion of our company. Your making me look good Sumit.
    I was wondering, could i get your email or a way to reach out regarding how we do the remaining of our employees to see if you could help. We track our staff (no managers) by hours. IS there anyway to do that with this excel sheet? Really look forward to hearing from you

  385. Once we move to next month, then the leaves tracked for previous month are reflecting. This makes it unusable.
    Please help.

  386. When I save to google drive the scroll bar disappears (all the other functions appear available). How do I create/copy the scroll bar?

    • Hey Jenene.. You can’t save this is Google Sheets as it does not have a scroll bar feature and does not use VBA. I am working on creating a leave tracker in Google Sheets. Will share soon.

  387. This looks great but the month changer scroll bar either moves one month or scrolls all the way to the end of the year?

    • Hey Jonathan.. It happens if you have a slow system or too many applications open. To handle this, click on the tip of the scroll bar and then move away the cursor. Hope this helps!

  388. Hi,

    I tried to mark Feb 7, 2016 as VL for 2016, but when I changed the year to 2017, Feb 7, 2017 was automatically VL. Do we have fix to delete the entries for another year if you moved to the next year?

    Also, what if I want to add a “Half Day SL” in the leave breakup? How to do this one?

    • Hello Paul.. This tracker works for one financial year only. So If you want to have one for multiple years, you need to create a copy of the workbook. There is already a Half Day leave in the tracker (use the code H)

  389. Its a very very useful tracker..kudos… however , it doens’t allow for designations, locations, DOJ etc. to be added

    • Hello Rachna.. You can add these additional details at the end of the tracker in the same row. That ways it wouldn’t break the tracker but still allow you to have the details

  390. Thank you for this wonderful tracker! It is very useful. I would like to track the “Leave this Year” for the period 1 July 2016 to 30 June 2017 (instead of the calendar year.) Can you please advise how I can do this?

  391. how to add coverage: if any one is on leave some one is assigned to cover. How to add name of person covering to cell where type of leave entered


  393. Hi Sumit. I need to add 2 columns to the right of Name and before the first date. but when I do it shifts the days. How can I add a column for Employee ID and DoH?

      • Hi sir thanks alots for this helpful sheet that’s what I’m looking for from long time but really you would help me to add some colume after the name if you can share the sheet with extra colume after the name coz ineed around 7 colume to add all staff details

  394. Hi Sumit. Excellent work. I really like the excel leave planner. However I have to include weekends as well in the leave breakup columns. Right now it gives the leave count for week days only but if we need to consider weekends as well, then the leave breakup column is not considering the weekends days.

  395. Fantastic spreadsheet, thanks. I need to add more rows for employees but the scroll bar stays in the same place. If I move it it doesn’t work properly. How can I do this?

  396. Dear Sir, how to add more types in leave breakup and also to add present column which counts the present number for everymonth or entire year.

  397. Hi, thanks for an awesome spreadsheet.

    1. How do I add more leave options – I want to add a leave option for “unpaid leave” and a few others.

  398. Hi Sumit. The leave planner is fantastic. Quick question though, I need to add in TOIL in half days. I’ve tried copying and pasting the half day formula but it doesn’t work. Please could you tell me how I can add another leave type that counts half days.

  399. Thank you for the great template you created.
    I am looking to create a copy of the “leave tracker” sheet, within the same workbook so that I can track year 1, year 2 etc. all in one document rather than creating a new one for each year.
    The only block I can see to this is the VB logic to show the calendar. As soon as I copy the sheet and start moving the scrolling bar the VB crashes to debug. Any idea on how to fix this. I expect it would be changing the VB logic / macro to apply to a sheet rather than workbook but cannot work it out.

  400. Hi Sumit, this is really helpful however when i am trying to add more rows the scroll bar is reflecting in between, i try to hide but it is not working can u help

  401. Hi Sumit…

    I downloaded your excellent Leave Tracker and I’ve added some other functionality to it. Great spreadsheet – love the slider changing to the relevant month. I’ve since added 4 sheets to it showing an Individual Calendar (showing all annual absences of all types), a group summary and a Management summary dashboard along with a Master data spreadsheet controlling some new functionality. Couldn’t have done it with your starting sheet though.

    As an idea for a future spreadsheet what about and ‘Issues / Risk Log Tracker’. This should ideally include the following:

    The same log should be able to track Issues or Risks.
    Each record must include
    ‘Unique Reference’ e.g. I-001 or R-001,
    ‘Raised By’ (Creator Name)
    ‘Date Logged’,
    ‘Issue Name’ (or ‘Customer Name’),
    ‘Description of Issue / Risk’ field (free text),
    ‘Current Owner’ (Owner name)
    ‘Priority’ (High, Medium, Low),
    ‘Age’ field (Age of issue in days)
    ‘Last Updated on’ (Date / Time field – flagged and highlighted if not updated in X days),
    ‘Status’ (Open, Closed, On Hold (with a triggered ‘Off Hold’ date),
    an associated ‘Audit’ record and, most importantly… Each record must be able to accommodate multiple Actions with each Action having a Time / Date stamp.

    A Log Dashboard would be useful e.g. X Records over Y days, XX Records over YY days, had XX records open, etc., etc.

    There are lots of Templates out there but all a little basic and importantly they don’t accommodate multiple actions (most Issues / rRsks are resolved with a series of actions which need to be recorded and tracked). The ability to produce a formatted history / report for an individual record would be nice – especially if it can be emailed to the person / persons responsible for the next action.

    What do you think? I developed a spreadsheet that does all the above but it is a little clumsy and probably not that efficient – I’m sure it can be improved on.

  402. Hi, I have 2 questions as below:

    1)0.5day can be sick leave, annual leave or unpaid leave. How can I do to count this particular 0.5day at the respective breakup column as 0.5 instead of counting as 1?

    2)Some employee are 5 working day, some is 6 working day and some is 5.5 day within departments. Can I record altogether in this template?

  403. hii.. first thanks for your amazing template.
    when i add a column before the name column. the january shows only 30 days while it should be 31 days. i tried adding an extra column but when i scroll to the february and came back it is again 30 days. what should I do?

  404. Hi Sumit, I am trying to scroll through to the next month but it keeps jumping all the way to the last month.. the scroll bar moves automatically even when I click just once on the forward arrow, all the way to the last month. I am using Microsoft Excel 2016.

  405. Exactly what I was looking for! I want to modify the name of the leave types and have a different color for each one. Is there a simple way to do this?

    • I also would like to modify the names of the leave types?? I am NOT familiar with Excel formulas and would greatly appreciate step by step instructions, especially for changing the colors. Thanks!

  406. Further to my post below, Sumit, One more question, Sometimes a staff may work on a weekend. Normally this is added back on from the leave days (effectively increasing the eligible leave days by one). How would that be done?

  407. Hi Sumit, Just downloaded the template and am trying to learn to work it. So far it seems like it will make life a lot easier for me. Maybe an extra day or two of vacations for me. On question though, our staff all have different working years and not necessarily 1st Jan to 31st Dec. How would that account for in this system?

  408. Hi Sumit – great chart. I’ve been looking for something like this for a while. You say we can add more employees by copying and pasting additional rows – and that works well. However, I need to print the chart so staff know who is on leave when – we have 150 staff and only a certain number can be off at any time 🙂 When I add more rows, the scroll bar for the months is also printed. I tried moving it – but somehow that interferes with the VBA code and I have no idea how to alter that. Can you assist and/or tell me what I need to do to make this alteration. Generally there would only be 40 staff listed on one chart as I have created a different worksheet for each category of staff. Thanks again.

    • Hi Sumit
      I think I found the answer. I had copied the worksheet so I could have different categories on staff on separate worksheets in the one workbook. That created a bug in the VBA code somehow – which was why I couldn’t move the scroll bar. Is there a way to duplicate the worksheet so I can have four categories of staff on different worksheets in the one workbook? I don’t really want to have to save it as four separate files. Will be a little tedious for the staff inputting the data if I do that?
      The question about the highlighting the cells without the “V” showing in the cell is still relevant if you can answer that as well please. Thanks


  409. Hi Sumit, how do we change the color for those holidays that fall on a weekend (Sat/ Sun) to the ORANGE highlight.

  410. Hello! I would like to ask why every time i am adding columns, the 1-31 dates is changing. when i add one column, the date one number will be removed, and so on. How can i add columns without changing the dates? Please help! Thanks ahead 🙂

  411. hi, with the newest version available, how do add more employees? at least 40 for example, but with the version that we can edit the working days and sums up the whole year leaves too.? @sumitbansal23:disqus

  412. Hi Sumit,

    You are god sent! The excel is god’s gift!
    However, i have added 2 more leave codes and how do i color code them using conditional formatting?
    Thanks! Ivy

    • If you look on the second tab on the workbook, the US bank holidays are all listed, just change the date and the description and it automatically updates on the main spreadsheet 🙂

  413. hi sumit i like your template very much but how can i change the calendar i mean i need to use Ethiopian calendar we have 13 months each contains 30 days except the last one which contains 5 days and the year is 2008 can you help me please?

  414. Hi, i have some staff that work different hours and there leave is calculated in hours rather than days – anyone know how i can work this into the formula? Also i need to add a column next to the employee name, but when i do this the 1st day of the month disappears – can any one help?

    Great piece of work by the way – works really well!

  415. Hello Sumit,
    You have the best tracker I have been able to find so far! There are a couple tweaks I have been trying to do specifically for my job and I was wondering if you could assist me? I could email you the changes I have made and what my goals are in each area that I’m having issues with. Thank for your help!

  416. Hello Sumit, first of all great file, im learning a lot with all your posts.

    I have been playing with this file trying to make it show me the weeks per year, and also im trying to make it show me the leaves not only the total of the month but for the week. (first week this many leaves, second week this many…)

    Hope you can help me and once again Thanks for sharing your knowledge.

    Regards from Mexico!! Amigo.

  417. Hi Sumit Am I doing something wrong? When I add any type of leave into a month it copies over into all the subsequent months,

  418. Hi there, I really love this excel. Simple and beauty.
    Then I’m trying to convert this excel to google spreadsheet. So me and my friend could collaborate when using it. By simply do import from excel on G spreadsheet, basic function are working well . And i love it, until I realized, the day the date are not correct. 1st Jan 2016 should be on Friday. In fact it show Sat on google spreadsheet.

    Anyone could help ?
    Many thanks.

    – Alex

  419. I’d love to see this tracker calculate the amount of PTO hours used (and how many hours are remaining) not just the amount of occurrences. Otherwise it’s great!

  420. Hi Sumit, just awesome to track leave. Here by us sick leave must be calculated even over NON working days, the other leave types only on workings days. I can make the complete week a working week, but then my cell shading is gone making it more difficult to enter data. Please tell me where I must change the formula to include NON working days for my SICK leave count. Thanks again, Chris

  421. Hi – thank you so much for this leave tracker.. it helped me a lot. However, I have one slight problem. Whenever I place a leave on a specific date for example on March 2016, what happens is its duplicated on all months. For example I placed an Emergency leave for March 15 2016.. all months every 15th has an Emergency leave on it. Please teach me how I can fix it. Thank You. 🙂

  422. Hi Admin, How can this template be used for over 100 staff of a restaurants business that has many outlets. something . This question is because you have only 10 staff on the template . kindly send a private mail if possible.

  423. Hi Sumit, I need to change the value of the Casual leave to be 0.5 instead of 1. Another user mentioned he used the array formula. It would be great if you could suggest how I can change this. Loving your spreadsheet 🙂

    • Hello Venessa. Use this formula in cell NJ8 (and copy for all cells): =SUMPRODUCT((OFFSET($A8,0,31*($A$3-1)+1,1,31)””)*(IF(OR(OFFSET($A8,0,31*($A$3-1)+1,1,31)=$NS$6,OFFSET($A8,0,31*($A$3-1)+1,1,31)=$NS$5),0.5,1)*(OFFSET($A$4,0,31*($A$3-1)+1,1,31))))

      Use this one for NK8 (and copy for all cells): =SUMPRODUCT((OFFSET($A8,0,1,1,372)””)*(IF(OR(OFFSET($A8,0,1,1,372)=$NS$6,OFFSET($A8,0,1,1,372)=$NS$5),0.5,1)*(OFFSET($A$3,0,1,1,372))))

  424. Hi Sumit. This is amazing. However, there is a slight glitch on the sheet. Data populated on a particular month is also showing on other months. Can you help me with that please?

  425. Hi, This is amazing and just what I’ve been looking for, but when i download, the scroll bar isn’t working, any idea on what may be wrong or how I fix it?

  426. Hi, thanks for this! however I am having trouble when entering annual leave in January it maps to all the other months. how can i stop this? … thank you

  427. Sumit ..this tracker is really neat. Do you have a suggestion on how to enter actual times instead of using codes? I am needing to enter leave time used in increments as small as 15 minutes up to 12 hours depending on what individual employees use on any given day. I was hoping I could modify this wonderful layout if there was a way..thank you so much

  428. Hi, looking to add a additional sheet to the tracker called Comp Offs, I work for an AUS client so we take Australian holidays, if I work o the list of AUS holidays im eligible for a Comp Off, how do I add that to the tracker so that after I update the holiday list and it shows as orange in the tracker now if I mark as Present (P) it should showup as a comp off
    Secondly need to track the comp off’s each person is eligible for and if utilized or not
    Please help
    Not forgetting that the tracker is awesome

  429. Hello, I would like to delete everything but vacation. When I try, I delete all the hard work that you put into this so is there a way I can only track vacation only?

    • Hi Megan. I would suggest, when you type “V” into the cells the person takes their vacation, this will put in “V” for vacation – just ignore all other types of leave and do not use those codes. I need the same and that is what I am doing.

  430. Hi, this template is super! However, I’d appreciate if you could help me out with my scenario – I’ve selected the year Jan’16-Dec’16, and updated the leave plans for my team. However, if I now change the A2 as 2017 (in order to plan for 2017 leaves starting Jan), the 2016 highlighted leave plans reflect in 2017 months, and are all messed up. I was expecting the cells to be reset as soon as I select another fiscal year! Is there a fix?


    • Hello Amrit, You can use this planner only for one financial year. In you want to use it for multiple years, you need to create a copy of this, so one for 2016 and a different one for 2017.

      Also, to change months, use the scroll bar and not cell A1.

      • Hi, to use this for more than one year, I have moved the holiday list on the year sheet and then made copies of that sheet. I then added another sheet to give me totals for all the years. But to do this you need a little bit about Formula and VBA skills

  431. i want to add one more code for leaves but don’t want to count it in total leaves of a year.. how i can make changes?

  432. hi! amazing sheet thanks a lot! how to count as well over time?
    eg: my staff working on weekends or during national days, i need to count so that i replace their over time by holidays

  433. thanks sumit …. its awesome but kindly help me all the month sheet are of same holidays after using scroll bar.also how it is possible to change holidays for a particular month.

  434. Any way to add vacation accrual based on hire date to calculate time remaining for each employee?

  435. How can I copy the calender so that the vbn module works on more then one sheet in that workbook?

  436. Hi Sumit,

    This is Arikrishnan. So pleasure to get in touch with you. I need a favour from you regarding a Tracker you updated for Attendance (….
    In this Tracker you have made the fields “Leave this Month (Cell NJ)”, “Leaves This Year (Cell NK)” till Cell NQ as constant and only cells allocated for every month changes.

    My requirement is that, I need those aforementioned fields needs to be changed as I would like to use those fields for monthly report. Can you assist me in this !!!!

    • Hello.. Cell NL to NP are not constants. The value changes when you mark a leave for any month. However, these would show you the value for the entire year, and not monthly

  437. Can the templete be altered to enter actual hours used for leave time? For instance, employees have 480 hours of FMLA, family medical leave, in a rolling calendar year. They can use in increments of 15 minutes. Would like to be able to see what they have used and how much is available. Any help would be appreciated! Thank you

  438. hi! Sumit,
    I have just downloaded the leave tracker. It looks really good but I just cant change the months.
    Can you please advise.
    Appreciate your help and great template.

  439. Hi – This is awesome, thanks! Could you tell me though how to modify the time off values (i.e. maternity leave – i don’t want this to show as days taken, because it doesn’t count towards their vacation, it’s protected time away). So, if I wanted to make mat leave = 0 days taken, how would I do that?

  440. Its amazing, thanks for the same. Can you please help assist how can we move the Scroll bar down while adding more employees

  441. Hi,

    Whenever I put a leave in a cell it reflects on the other months. Kindly help advise. Thanks. 🙂

  442. Hi Sumit ! Thanks for this wonderful tracker, I have slightly tweaked the formulas to add the days present. these efforts wont be counted in no of leaves for the month and year .leaves this month:=SUMPRODUCT((OFFSET($A8,0,31*($A$3-1)+1,1,31)””)*(IF(OFFSET($A8,0,31*($A$3-1)+1,1,31)=$NS$6,0.5,1)*(IF(OFFSET($A8,0,31*($A$3-1)+1,1,31)=$NS$7,0,1)*(IF(OFFSET($A8,0,31*($A$3-1)+1,1,31)=$NS$8,0,1)*(IF(OFFSET($A8,0,31*($A$3-1)+1,1,31)=$NS$9,0,1)*(IF(OFFSET($A8,0,31*($A$3-1)+1,1,31)=$NS$10,0,1)*(OFFSET($A$4,0,31*($A$3-1)+1,1,31)))))))) leaves this year:=SUMPRODUCT((OFFSET($A8,0,1,1,372)””)*(IF(OFFSET($A8,0,1,1,372)=$NS$6,0.5,1)*(IF(OFFSET($A8,0,1,1,372)=$NS$7,0,1)*(IF(OFFSET($A8,0,1,1,372)=$NS$8,0,1)*(IF(OFFSET($A8,0,1,1,372)=$NS$9,0,1)*(IF(OFFSET($A8,0,1,1,372)=$NS$10,0,1)*(OFFSET($A$3,0,1,1,372)))))))) paste there use control +shift+enter

  443. Any answer on this question pls guide me Some of the additional “leave codes” I would like to have counted as 0.5, however, they are all being counted as 1.0. How do I change that?
    SUMPRODUCT((OFFSET($A10,0,31*($A$3-1)+1,1,31)””)*(IF(OFFSET($A10,0,31*($A$3-1)+1,1,31)=$NS$6,0.5,1)*(OFFSET($A$4,0,31*($A$3-1)+1,1,31)))) here =$NS$6,0.5,1 similarly i want =$NS$5 also counted to be 0.5 thanks!

  444. Thx for this great spreadsheet. Can I ask for a version where You can also add two columns on the right from the employee which would include number of the employee group and current shifht. I need those informations to quickly manage shifts in the company. Those 3 columns wolud have to be visible all the time.
    Pls right me back.

    • Hello Lukas.. You can add new columns to the right of the tracker (column NT onwards). That would keep the tracker intact and also have the columns visible at all time

    • Hello Lukas.. You can add new columns to the right of the tracker (column NT onwards). That would keep the tracker intact and also have the columns visible at all time

  445. Hi Sumit,

    I have figured out what I was doing wrong! I had the right formula but missed that the formula was an Array Formula so consequently I was getting the #VALUE! when I spotted the { } brackets at the start and end of the formula I did some research which suggested after entering the additional information I use the key combination
    CTRL-SHIFT-ENTER rather than just Enter and it worked!

    Now have the spreadsheet working perfectly with 3 options for half-days and an additional column noting the lateness.


  446. Savvy piece , Apropos , others need a HI DoT BB-1 , my colleagues filled out a blank document here

  447. Can we use 1 tab for 2016 and 1 for 2017 in the same file with 1 tab for combined holidays?

  448. Excuse me, how can I remove the Half day cell. I do not want two half day to change to one day leave. How can i Change it , please?

  449. Hi, I’ve recently start using this great spreadsheet as it really fulfills all my needs but unfortunately I’m not as good expert of excel as this sheet. So, would like to know few things:

    – How I can customize/add/remove the leave codes
    – How can I update new code on leave break up section

    • Hello Shujat.. The tracker is a bit complicated so to add new codes, you will need to modify the formulas and make sure new codes are part of it. Alternatively, you can create a copy of the tracker and split the codes

    • Hello Shujat.. The tracker is a bit complicated so to add new codes, you will need to modify the formulas and make sure new codes are part of it. Alternatively, you can create a copy of the tracker and split the codes

  450. Is there anyway to know the VBA Code that runs in the back to keep the month changing?

  451. Hi Sumit,
    I am not sure where my last post went but we actually need 3 1/2 day options and to increase the Leave tracker to 6 categories rather than 5. I have managed to add the sixth category but cannot seem to get the other two 1/2 day formulas to work I keep getting #VALUE! error. Please can you help. For the record, our 1/2 day options are Half day holiday (HH), Half day sick (HS) and Half day other (HO).
    Thank you in advance,


    • Hi Patrick! I am having the same issue as you! I need to change to change one of the leave options to account for a half day holiday! Would be awesome if you could share how you got around this! Thanks!

      • Hi Vanessa, Are you adding an extra row/ column or just amending one of the formulas?

        I had to add to add an extra row so firstly make sure you only move the rows directly below where you insert the formula so that the main spreadsheet is kept in its format. I copied one of the leave codes and then “Inserted Copied Cells” shifting the others down and changed the letters to the Code I wanted i.e. HS
        When you add the extra column, Copy the whole cell range and again insert the copied cells, this will shift the whole lot to the right then again change the code to match the one you want.
        Once you have completed the first two actions, you will then need to amend the formulas in the first cell you want to change, make the relevant changes directly into the cell then before exiting the line use the key combination
        CTRL-SHIFT-ENTER (This is because the formula is an Array formula)
        This will then save the amended formula for you. Once you have done this once, you can copy the cell and paste and this should work fine.
        I hope this helps, let me know if I can help any more, good luck,

        • Hi Patrick, thanks a bunch for your help. However, I need to change value of C to equal a half day instead of 1 full day. I don’t understand how to change the formula let alone find it. Any help or guidance would be greatly appreciated! Thank you again for being so kind!

          • Hi Vanessa,

            It is a bit complex but I think I can put something together to show you how to change C to show a 1/2 day. I am training staff today but will try to get an answer to you tomorrow.
            In the meantime, please can you let me know which cell the letter “C” sits in i.e. NP4 etc



          • Hi Vanessa,

            Just wondering if you could let me know which Cell the letter C sits in on your spreadsheet.



          • Hi Patrick,

            I managed to edit the formula and it worked! Thanks again for your help though!

            Do you happen to know how to create one for 2017? Do we change the dates and holidays manually?


          • Hi Vanessa,

            I am delighted that you have managed to make the changes!!! All you need to do is amend the Year on the spreadsheet to 2017 and it will do the rest for you. What we have done here is to copy the sheet and change the date and save it as 2017.

            I hope this helps best regards,


          • I also need to know how to change the formula to include 6 half day options. I have them changed on the right but they dont figure in as half. I understand the crtl, shift etc but where do I put that in the formula? The formula shows as


            I know I the NS is no longer correct and should be OS but when I add the range it adds them to the total even without any entries being made

          • Hello Kary,

            As you will see I have had to put in a new set of formula for each separate half day that I had to record.


            Before I did this, I had to create the half day holiday reference cells so I added to the existing table pushing the Days of the week table down. (I hope this makes sense where you have “NS$6” I changed mine to “OG$” then the cell number this is because I moved the cells to the right when I created the extra columns to show the half days under the “Annual Leave Breakup” columns (I simply copied the Holiday Column, pasted copied cells, shifting to the right and renamed the column).

            I hope this helps,


          • Yes this does make sense. I’m pretty good at excel but this one was beating me. I could get it to show .5 but then nothing else I entered for that month would add in the monthly total, just the yearly total. Thank you

          • Hi Vanessa,

            So sorry for my slow reply. I just copied the spreadsheet then amended the year to 2017 and it did the rest.


  452. how to edit the formula & add some category of leave… I’ve been trying this for 2 days now, but everytime i change the formula even for the row number it shows error.

  453. how can i add for the category or kinds of leave , we have halfday for sickleave and halfday for sickleave? how will i add it to the formula. thanks

  454. Hi sumit

    Your leave tracker is very nice.thanks.i have modified the leave tracker to include onshore and offshore and hence have created two list in holidays.its working fine except the sum section in column NJ.I am just replacing the holiday list in formula with my one but its not working.its showing 1 only.also i am not able to understand the logic also.can you please help.

  455. HI Sumit,
    Please help me with tab-wise employee sheet instead of month-wise view,
    This will help to see annual details of a single employee on one sheet.
    Can you please upload anything like that?

    • Hello.. The tracker is made to have all employees in a single sheet. Currently I don’t have anything that shows each employee detail in a separate tab

    • Hello.. The tracker is made to have all employees in a single sheet. Currently I don’t have anything that shows each employee detail in a separate tab

  456. Hi this looks superb,
    i would like to have employee wise tabs instead of month wise,
    so one sheet will have annual details for one employee.

    With months on the place of employee names and the heading will be the name of employee.
    Please hep

  457. Hi Sumit, thanking you kindly for making your hard work available for everyone to use!

    I need some assistance regarding protecting the sheet. I need my employees to have access to view the planner but no edit permissions. I have tried many avenues however I’m unable to make the scrollbar the only accessible function. Are you able to assist? Many thanks!!

  458. Hi Sumit,
    how to add in-lieu leave, my team sometimes work during weekend or holiday… Thanks

  459. how to change the colour in leave code there are only showing RED colour
    any know body know please let me know please

  460. Hi,when i update as S”Sick leave” for the month Jan 4th Example of employee 1,when i change the month to febrauary also, the 4th date Sickleave will remain same in the sheet,request you to do the needful.thanks

  461. Hi, i’m planning to do up a firefighter leave tracker. I need to tweak the excel sheet but i’m clueless. Can i get your email to discuss with you?

  462. Hi Sumit, I have been using the awesome leave tracker and I find it very useful. I’m having problem with the scroll bar. When I scroll, it didn’t go to the month I wanted, instead it scroll to the end. Is there a way to adjust the scrolling?

    • Hello Morni.. This happens as the file is a bit heavy. Here is the workaround. Click on the edge of the scroll bar and then remove the cursor from it. It will stop

    • Hello Morni.. This happens as the file is a bit heavy. Here is the workaround. Click on the edge of the scroll bar and then remove the cursor from it. It will stop

  463. I LOVE this leave tracker and have tried to manipulate this form to track the services but can only enter 1 “letter” in a daily box.

    Do you have any type of excel form whereas you can still track the services each day but are able to add 2 or 3 codes in one box and it tallys it at the end based upon each client’s needs??

    For example, each client is given 100 units for Behavorial Health for either a 3, 6, 9 or 12 month period. As I enter the services daily, it will deduct from the available units till the next authorization period.


  464. Hi Submit, it is great to work with your template and my compliments for this tool! Only if I try to change the holiday list, as I am from Europe, I doesn’t succeed to change it. For instance, we don’t have the holiday 4th July. When I update the holiday list, it remains in the tracker. What am I doing wrong?

    • Hey.. Since the holiday are in an excel table, you need to make sure you delete and replace the dates (and not delete the rows as that will delete the table as well)

  465. HI Sumit, i want to use this for 100 employees, but i can not move the scroll bar down to add rows

    • To get the scroll bar down, right click on it and then drag it down. To add more employees, simply add employees and copy the formatting and formula.

    • To get the scroll bar down, right click on it and then drag it down. To add more employees, simply add employees and copy the formatting and formula.

  466. Hello Sumit, great template!!! it is very useful! One question, I try to add a column to have the balance (deducting from the number of vacations they have for the year but the formula didn´t work. Could you help me with it?

    • Hello Liza… Try this formula: =Total Leaves-SUM(NL8:NO8)-0.5*NP8 (in cell NQ8 and copy for all the cells in NQ column).

      In the formula, replace ‘Total Leaves’ with the number of total leaves in your company.

    • Hello Liza… Try this formula: =Total Leaves-SUM(NL8:NO8)-0.5*NP8 (in cell NQ8 and copy for all the cells in NQ column).

      In the formula, replace ‘Total Leaves’ with the number of total leaves in your company.

  467. Hi Sumit. Love this especially as you can start the year anywhere. Great for us whose holiday year doesn’t run from Jan to Dec. One thing..Feb 29th doesn’t seem to show up for 2016

    • Hey Russ.. Glad you liked the template. I just tried and 29 Feb is showing in the template when 2016 is selected.

  468. For some reason the days seem to be out by 1 (Jan 1st is showing as a Saturday when in fact is was a Friday – at least for me in the UK). How would I go about changing this?

    • This only seems to be an issue when opening in Google Sheets (Dates are fine when opening in excel). The whole document however is not setup to work in Google Sheets.

  469. Hi, I also wanted to calculate shrinkage of the month for a team and also set a shrinkage limit. How do i do that?
    Please explain.

    • Hi Aaron. I’ve just done this. Just put June and the year in the boxes on the top left and it calculates 12 months from there.

  470. Hi Sumit.
    The leave tracker is not working for me.
    I have to maintain leave record of about 100 employees
    As I had one column the other column will hide automatically and also the scroll bar will remain in middle of the page

    • Hello Shilpa.. To get the scroll bar down, right click on it and then drag it down. Also, when you insert a column, you need to make sure the formulas are intact. You can however, easily add more employees by adding more rows and copy paste the formulas and formatting.

  471. Hi Sumit! So far you have assisted us with a most impressive and useful excel leave sheet and I have been searching for one for months now! Thank you for this!
    I have managed to figured out how to move the scroll bar in the sheet. Unfortunatley on a few occasions it has completely messed up my leave sheet wtih the error message “run time error message 1004” and I have had to start from scratch…..I have no idea how to fix it.
    Please can you assist me by;
    Inserting colums with NAME/SURNAME/ SITE/ EMPLOYEE #/START DATE
    Vacation leave – after each month have the vacation balance (this will make it easier to see what vacation leave balance they have at that month (however still leave the total vacation days as you have it too)
    Is there a way to have a column with a leave amount that the staffmember has like at end June 2016 Mr x has 8days owing to him and so continue is this manner?



    I also tried to update the calendar with our Holidays and it give me the run time error also.

    Friday 1 January 2016 – New Years Day
    Mon 21 March 2016 – Human Rights Day
    Friday 25 March 2016 – Good Friday
    Monday 28 March 2016 – Family Day
    Wed 27 April 2016 – Freedom Day
    Sunday 1 May 2016 – Workers Day
    Monday 2 May 2016 – “Public Holiday”
    Thursday 16 June 2016 – Youth Day
    Tuesday 9 August 2016 – National Women’s Day
    Sat 24 September 2016 – Heritage Day
    Friday 16 December 2016 – Day of Reconciliation
    Sunday 25 December 2016 – Christmas Day
    Monday 26 December 2016 – Day Of Goodwill
    (if you add all these dates in, then I can just update it next year without any problem)

    Your assistance is much appreciated!!!!!

    • Hello, adding columns to the tracker will mess with the formulas. If you need to add columns, I suggest you do it to the right of the tracker area. You can mark casual leaves by simply coloring the cell and not entering anything in it. Also, to change the holidays, simply delete the data in the holidays tab and add the days you want to show up as holidays. Make sure you don’t delete the rows, only the data.

  472. Is there a way to lounch filtering mode in the “Name” heading, I would like to filter just one employee at a time. Great template by the way.

  473. Hi Mr. Sumit,
    How can I remove half and causal day without damaging the formal in total leave for the month and year?

    • Also, sir, is there a way that if i type “x” into the calendar then it wont count it towards a leave? (all of my employees have a different off days, therefore I am putting an “x” for there off days. The formula thinks it’s a leave).

  474. Hi sir in my company alternate saturday is OFF. How is possible to change that with your template

  475. i want to add more employees in the sheet, but the scroll bar at the bottom to change the month does not move down, hence hindering my view of additional employees. how do i move the scroll bar further down the page?

  476. Hi Sumit, thank you for the awesome template. Just a qns, I would like to add in AM / PM half day leave into the templates so the sheet will reflect either a half day leave taken during the AM session of the day or half day leave taken in the afternoon session of the day.

    Can you guide me thru how to edit the formulas in the excel sheet? Thanks alot in advance for your reply !

  477. Hello, is it possible to get 2 half leaves? Example – if Maternity leave is not needed can we replace by VAC Half day and still keep the other Half day as Personal Half day? I tried to copy the formula but it just links both cells. Thank you!

  478. Hi Sumit, Thank you for this excellent tool. But I have a tricky situation. I have added two more codes W(Work from home) and F(Comp Off). I dont want these to add up to the leaves for the month or the year. However I just need a count of the number of Work from Home and the Comp offs availed separatly for audit purposes. Can you help me here? Thanks in advance- Vasanth

  479. Hi there, I have been looking for something like this for a very long time!! thank you” will the excel sheet still work if the employee holiday year starts at different months? they do not all start on the same month.

  480. Hi,
    Sumit I love the excel leave tracker. But i need some changes in it so would u plese healp me for the same.
    I want to change the colours of the Assigned leave for example consider as vacation(V) should be in dark green insted of red. And i also need the working days to split according from employee to employees as if now if we select the working days it applys to all employees so it should be split working days.Also its should show persent of absent days. Please help.
    Thanks once again.

  481. Hi, This is so close to what I am looking for and tweaked it to add more number of employees. My organization has comp offs that I would like to capture too but they need to be counted as 0 (similar to H being counted as 0.5). How do I tweak to accomodate this.

  482. This was excellent! Thank you. Is there a way to change the colors of the time off. Sick day have own color, vacation own color etc?

  483. Hi, Is there any way to amend the formula so sick days and maternity days do not calculate as leave but can be recorded?

  484. Hi Sumit Bansal… i would like to modify your tracker but i have hard time understanding it.. hehehe

  485. This is a great template! However there is a little glitch such that if i take leave on 1 Jan 2016, the same leave would appear every year for 1 Jan, please help 🙂

  486. Hello 🙂 There is a little problem in the excel sheet. If i take sick leave on 2 Jan 2016, the same leave will appear in 2 Jan 2017 🙁 But anyway thanks for this excel sheet! It’s great!

  487. Dear Sumit,
    its a great work,
    but how can i add another columns, it change the date ?

  488. Dear Sumit,
    I already key in in excel attendance but when i key in january why in february leave symbol still there

  489. the tracker is amazing you did a great job!!!! I was wondereong if how can i change the years , I plan holidays following the uk financial year (from april to april) and need to ad 2017 months!!! will be possible show me how this can be done!!

  490. In addition to days taken, some employees take a couple of hours here and there and I would like to be able to track those hours. How can I change it to show hours taken as well?

  491. Another random bug I just discovered. I added a new column after Column A to allow for First Name and Last Name Columns of employees. This new column removes the first day of the first month in the calendar. Everything else seems to be fine (I did adjust the slider macro format control to leave that column from moving but that seems to be unrelated). All other cells, formulas, and conditional formatting adjust when I insert that new column except for January 1 or June 1 or whatever the first month is set at. Any ideas?

    • (Did I mention how much a truly LOVE this workbook! Amazing job and thank you so much for making it and sharing it! Has REALLY helped my job)

  492. I LOVE this tracker so much. So adaptable to my needs. Thanks for making it and thanks for having it unlocked and open. I have discovered one small bug that I cannot figure out how to fix. I changed the start month to June to coincide with our fiscal year (using the dropdown menu in A1) and the the year to 2015 so it goes June 2015-May 2016. It does not account for the leap year in February of this year. But when I have it set to the calendar year of 2016 (Jan-Dec) the leap year is there. I have been able to simply type “29” in the box where it should be and everything seems to work fine, but was wondering if that is causing any unseen issues that I haven’t noticed. Thanks again for such an amazing workbook template! Amazing!

  493. I LOVE this tracker so much. So adaptable to my needs. Thanks for making it and thanks for having it unlocked and open. I have discovered one small bug that I cannot figure out how to fix. I changed the start month to June to coincide with our fiscal year (using the dropdown menu in A1) and the the year to 2015 so it goes June 2015-May 2016. It does not account for the leap year in February of this year. But when I have it set to the calendar year of 2016 (Jan-Dec) the leap year is there. I have been able to simply type “29” in the box where it should be and everything seems to work fine, but was wondering if that is causing any unseen issues that I haven’t noticed. Thanks again for such an amazing workbook template! Amazing!

  494. Hi Sumit. Awesome work with the tracker. You have a breakup for the year, can you please advise how can I break up the leaves taken per month the same way. Secondly, how can I assign a different colour for different leave types?

    • Changing the colors is super simple (I literally just did that to mine 5 mins ago). Go to Conditional Formatting > Manage Rules. Notice the rule for the Red and Yellow (those are the ones to pay attention to).For yellow (for example) it is only the half day. It looks at B8(don’t put $ in this piece) and compares that (or whatever date cell you are on) to $NT$6 (use the $ here) which is the code ‘H’ for half day listed in the small area on the top right of the worksheet listing codes. You can add more codes like this. Conditional Formatting > Manage Rules >