Free Excel Leave Tracker Template (Updated for 2018)

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.

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)Download File

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

How this Excel Leave Tracker Template Works?

  • This Excel template can be used to track leaves 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 2018 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 it 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 Template - Change start Month
  • 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 2018- Change Months Scroll Bar - attendance vacation tracker
  • You can select 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.Excel Attendance Tracker Template - Holiday List
  • 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 case of sick leave, use S, in case of Vacation, use V, as so on.
    • 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.

FAQ on using this Leave Tracker

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.

Is 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:

  • PD says:

    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??

  • Sachin says:

    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.

    Regards
    Sachin Sharma

  • Teet Parson says:

    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.

  • Chx says:

    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?

  • chix says:

    There’a alway an error “UNABLE TO SET THE HIDDEN PROPERTY OF THE RANGE CLASS”

  • Rachel Mescon says:

    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.

  • Sopheak says:

    So help full, but I cant add more column after the name, for gender or sth els. Please help!

  • Rita says:

    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?

  • Susan Brennan says:

    Hi, do you have a 2019 version?

  • ronell grobler says:

    HI HALLO, IS IT POSSIBLE TO INSERT AN EXTRA COLUMN FOR STARTING DATE OF EMPLOYER MY LEAVE GOES FROM STARTING DATE TO ONE YEAR AFTER THAT.

  • NH says:

    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?

  • Robin Emswiler says:

    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!

  • SUBRATA says:

    nice

  • Alanna says:

    Can I use this with Mac??

  • martyna says:

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

  • brhane says:

    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.

  • Jub says:

    How do I add a column for employee ID left to name

  • Lisa says:

    This is a great tracker. How can you assign your own colors for each type of leave?

    • Sumit Bansal says:

      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

      • Lisa says:

        That worked…thank you!

      • Lisa says:

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

  • Shijas says:

    How to change Saturday and Sunday holidays to Friday

  • Shijas says:

    Hi

  • Joe says:

    hi. is it possible to exclude some leave type in the calculation of the leaves?

  • Maria says:

    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?

  • Prateek Srivastava says:

    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?

  • Binisha says:

    Awesome Work, Sumit. Thanks a lot for your great efforts… It helps!

  • Nellie Ochieng says:

    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.

  • Ken Smith says:

    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

  • Lozier Gutters says:

    Can you track the hours they took on the day they took the designated leave

  • Suede Chavez Bolaños says:

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

  • Lauren Smith says:

    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?

  • Ashley W says:

    Is a 2019 version available?

  • Mharfel Milanes says:

    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

  • Hefty Lumba says:

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

  • Ala'a Al Allawy says:

    I can’t Find the working days and non-working days to change it

  • Amy says:

    Is there a way to show each staff’s remaining holidays as well as holidays taken? Thanks

  • nouf saad says:

    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”

  • Mohammed Imran says:

    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”

  • Rajib Das says:

    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.

    Thanks,
    Rajib Das

  • Ela says:

    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?

  • Michele Apple Taylor says:

    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?

  • Miro says:

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

  • Fadilah Ghabi says:

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

  • Tracey Purdon says:

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

    • Sumit Bansal says:

      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

      • Kriti says:

        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.

  • Michelle Testoni says:

    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?

    • Sumit Bansal says:

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

  • saud says:

    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

  • Faye Chevaughn Davies says:

    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

  • Carl says:

    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.

  • Denise Cain says:

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

  • Jaroslav Kuzma says:

    Hi, there is possible to add multiple Holidays for 2 or more different country??

  • Dale Wicks says:

    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

  • Evgenia Yertsenkin says:

    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?

  • Claire C says:

    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?

  • Bev says:

    can’t upload onto google drive coz of the macros…

  • Marlize Joubert says:

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

  • Sue Thornton says:

    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

  • Robert Heise says:

    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.

    • Helen Abbott says:

      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?

  • noriel says:

    How will I add additional column for the name/

  • Tobias Van Reenen says:

    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.

  • Bethany Lambert says:

    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?

  • Merrily Hansen says:

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

  • Tom says:

    this is a great template

  • Tom says:

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

  • Tom says:

    this is great.

  • Becca says:

    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.

  • Anoop James says:

    @ 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 [email protected]

  • Helen Abbott says:

    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.

  • Karl Lund says:

    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.

  • Angela says:

    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.

  • Liliana says:

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

  • Liliana says:

    hELLO,

    How I add new employee/

  • Kevin Spiteri says:

    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.

  • Melody says:

    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.

  • Steven Lebron says:

    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

    • Melody says:

      Hi Steven:

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

  • Deepak Gurung says:

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

  • Sincerity says:

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

    Whao! May God bless you real good.

  • Aliakbar says:

    Thanks ,very good nice Excel Leave Tracker Template

  • Tuyet Nhi Chau says:

    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?

  • Sue says:

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

  • Lyndsey Graham says:

    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

  • Donna Mayo says:

    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.

  • Donna Mayo says:

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

  • Sunil Patil says:

    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

  • Aisha Sheikh says:

    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.

  • Smruti says:

    Hi Sumit,
    I enjoyed using 2017 tracker. Will you be going to release 2018 tracker soon?
    Thanks,

  • Rita Toscano says:

    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.

  • Abdul Jameel says:

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

  • shashank says:

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

  • RJ says:

    How to add another column without affecting the other column? Is that possible

  • Smruti says:

    When you be releasing 2018 Vacation Tracker?

  • rishie lovesu says:

    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

  • Moe Abraham says:

    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?

  • dishi says:

    Hey , the zip file does not have any excel,pls help

  • Shinelle Grant-Sealey says:

    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?

    • Shinelle Grant-Sealey says:

      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?

  • Esther Kiarish Bella says:

    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.

  • Venkat Jaksani says:

    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

  • Rachel says:

    I downloaded the zip file… how do I open the template?

  • Subhashini Prem says:

    Hi Do you have template for 70 people

  • Azleendah Sam says:

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

  • Jm Palomares says:

    Hello. This is awesome. But how can I make it for atleast 100 people?

  • erzon says:

    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?

  • AG says:

    when can you update the leave tracker template for 2018 🙂

  • Abraham Joseph says:

    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

  • yueakabane says:

    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

  • Todd Humphrey says:

    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?

  • Brenda says:

    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 Bansal says:

      Hey Brenda.. You can download the leave tracker now and it will be saved as an Excel file (not a zip).

      • Kafil AH says:

        hey Sumit need your help in doing AHT and other call center Dashboard .

      • Genesia Pierre says:

        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?

  • Laura Taylor says:

    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?

  • Tallie Theriot says:

    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.

  • Nicole Brown says:

    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!

  • Derek Lim says:

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

  • Karan says:

    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.

  • Aymen says:

    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.

    • Aymen says:

      Also whenever i try to create a copy it always show error of Debug .. s

  • jackie says:

    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.

  • Alwyn Noronha says:

    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

    • Aymen says:

      same worry for me .. how to calculate the leave balances with reference to the joining date

  • Lee Fearnley says:

    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!

  • Emily Horswill says:

    Hiya, This is great, would you be able to release a 2018 version please?

    Thanks!

    Emily

  • Ray says:

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

  • Dannielle says:

    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!

  • Gaby B says:

    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?

  • Ivan de Jager says:

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

  • Amanda Fouche says:

    HI. Please assist.. I am not to sure how to create a leave cycle on the leave tracking template you created
    [email protected]

  • pedro says:

    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

  • Harish says:

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

  • Andrew Munn says:

    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?

  • siti says:

    Hi Sumit, How to change the year..im looking for 2018 version

    • Sumit Bansal says:

      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.

      • siti says:

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

  • Keshav Hebbar says:

    can i mark time also if employee leaves early in the same sheet?

  • Kenny Kong says:

    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.

  • Danny says:

    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.

  • John Webber says:

    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

  • Adnan Raihan says:

    Hi Mr. Sumit, awesome work, I just stuck with one, you didn’t add Time off In Lieu (TOIL) here

  • Muhammad Irfan says:

    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

    • Aymen says:

      First right click and drag the scroll bar down and then keep adding the rows

  • Gilbert Adame says:

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

  • dee says:

    how to add another row of employee.. our employee are more then 10

  • DanaKat18 says:

    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?

  • Cyron Somosot says:

    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!

  • Anna says:

    Hi I’m downloading the file but there’s no excel file? Is this still available?

    • Scott says:

      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.

  • Serena Tan says:

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

    • Sumit Bansal says:

      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.

      • Serena Tan says:

        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
        Sereba

        • Sumit Bansal says:

          Hey Serena.. The easiest way would be to use a separate column (may be column NQ) and subtract the training leaves from total leaves.

          • Serena Tan says:

            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

          • Sumit Bansal says:

            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

          • Serena Tan says:

            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.

  • Serena says:

    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?

  • Paula says:

    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!!

  • Shan says:

    Thanks a lot for this template. I have one question: How can I count friday as half day?

  • manmohan gurjar says:

    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 ?

  • jab says:

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

  • natasha says:

    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?

  • Manab says:

    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.

  • Soul_O_Nice says:

    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

  • Soul_O_Nice says:

    How can you change the scroll bar to advance by one month?

  • Milind says:

    superb!! Thanks 🙂

  • Wendy Leigh says:

    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?

  • Tatyana Sinchuk says:

    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?

  • Ravil Kharya says:

    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

  • grace says:

    hi, can i use this template of year 2017 & 2018 in one sheet?

  • Bill says:

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

  • Deepika Lohani says:

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

  • Deepika Lohani says:

    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.

  • Geetha says:

    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?

  • Bonnie says:

    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?

  • Megan Lee says:

    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?

  • Rukshana Hussain says:

    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

  • James Ali Benoit says:

    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?

  • may says:

    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?

  • welgene says:

    once download it was a zip file.. how to use?

  • mohammed samiulla says:

    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

  • Ka Ro says:

    Hello, I am unable to unzip the file, does it still work? thanks!

  • Kelly says:

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

  • mac42 says:

    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?

  • Abdulwhab Owil says:

    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 .

  • Shawn Moore says:

    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,
    Shawn

  • Yogesh Dash says:

    Is it possible to calculate monthly absent and present in this template?

    • Ken Smith says:

      hey Yogesh, did you find the way to do this?

  • Sameer says:

    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 – [email protected]

  • Anonymus says:

    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.

    • Sameer says:

      Hey, where you able to access the excel file?
      I am not able to. can you please share it with me – [email protected]

  • Visu Vijayakumar says:

    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

    Regards
    Visu.V

  • Rosemarie Alago says:

    Thanks a lot

  • Rosemarie Alago says:

    This is a total useful …. Thanks

  • sachin says:

    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.

  • Aditya says:

    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?

  • Suzette Marino says:

    I need to add additional leave codes, both full day and half day. Can you send instructions? My email is [email protected].

    Thanks for the help!!

  • John Griffin says:

    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

  • Aragon says:

    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 https://goo.gl/76rzxp

  • Paul says:

    Hi Sumit
    I have added an overview sheet that uses a drop down to select the employee and then show the annual leave.
    Not as colourful as your original so far. Thanks for the template. Column AI is the starting point for each month assuming no changes have been made to layout.
    https://uploads.disquscdn.com/images/b1929dd3a6c1ffec104313a6a44e7d23172692086e11dbb6ce0de8a5abaaab8a.png

    • Lauren Smith says:

      Hi Paul, can you share this spreadsheet you made? I would like to use it

  • Stacey says:

    I like this tracker, but how do add time to it. It only goes to March 2018?

  • فاطمه فوزي says:

    could you please do a tutorial of this leave tracker

  • Ishani Dave says:

    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?

  • binoy says:

    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.

  • Guadelupe Antonisia-Con says:

    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.

  • فاطمه فوزي says:

    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?

  • Uday says:

    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

  • Alisa Lycheva says:

    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?

  • mitra says:

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

  • Kristine Mendes says:

    This is awesome!

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

    • mitra says:

      right click on scroll bar and then cut , past where you want.

  • Susan says:

    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?

    • mitra says:

      You can add column after NQ not anywhere else

  • mitra says:

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

  • mitra says:

    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 ?

  • Mansoor says:

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

  • James says:

    Need help.
    I download the zip file, but unsure of how to set up the file in excel.

    • Paul says:

      Hi
      I had the same issue, rename the .ZIP file extension to .XLSM and the file will open in Excel.
      HTH

      • James says:

        Thanks!
        I will make the name change.

  • Tom Moons says:

    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

    • mitra says:

      ı have same ıssue can anyone help?

  • Paul says:

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

  • Pete Chatten-Berry says:

    I’m probably being really dim but I downloaded and unzipped the file but cannot find an excel spreadsheet to open?
    Which file in what directory opens the tracker?
    [email protected]ix.co.uk

  • Chandan Hemnani says:

    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.

  • Michael Jones says:

    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.

  • Uthuman says:

    This is the most important thing i have so far got from the internet this year

  • Samamil says:

    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!!

  • Muzaffar says:

    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

  • Petra Rážová says:

    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?

    • Uday says:

      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.

  • hanim says:

    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?

    • Melissa says:

      I have the same issue. Please advise!

  • Kenneth Parkins says:

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

  • Larvin Esguerra says:

    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!

  • Megan Harrison says:

    This is awesome! Do you plan on doing a Google Sheets version?

  • Somto Ogbonna says:

    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.

  • Divesh says:

    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.

  • Sankar Iyer says:

    HI Sumith- Is there a 2017 version for this?

  • Zyra says:

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

  • Lynn says:

    Our holidays are February to end January .. how can I amend the dates for the year

  • Andy Tran says:

    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?

  • Sridhar A says:

    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.

  • Odette says:

    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?

  • Prabodh Dwivedi says:

    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.

  • Tina Abarquez says:

    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?

    • Paul G says:

      As per sumit, you need to use the scroll bar for changing the months 🙂

      • Sridhar A says:

        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

  • Matt says:

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

  • Paul G says:

    Hi Sumit,

    Hope that you have a template for this for Google Docs 🙂

  • Jacques says:

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

  • Erica Franz says:

    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?

  • Humaira says:

    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.

    • Paul G says:

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

  • james says:

    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?

  • Michael says:

    What a beautiful spreadsheet. Is there perhaps a video on how to start it from scratch?

  • tenvolt says:

    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

    • Lauren Smith says:

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

  • Shital Patel says:

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

  • matthew laker says:

    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

    • Sumit Bansal says:

      Hello Matthew.. That would have to be a completely new tracker.. Stacking these months would mean a complete overhaul of formula and VBA

  • vinoth says:

    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 ???

    • Sumit Bansal says:

      Hello Vinoth, You need to use the scroll bar to change the months, and not cell A1

  • Ashwini says:

    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.

  • Jan Wee says:

    Hi Sumit, where can I input a staff’s leave entitlement for the year?

    • Sumit Bansal says:

      Hello Jan, you can put these at the end of the tracker. Next to the leave count

  • Leslie says:

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

    • Sumit Bansal says:

      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

      • Leslie says:

        Hi Sumit!
        Thanks so much! It worked perfectly. Really appreciate you sharing 🙂

  • Shannon says:

    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

  • Ashish Bhatt says:

    the scroll bar simply skips few months

    • Sumit Bansal says:

      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.

      • Ashish Bhatt says:

        Hey thanks for the reply. Appreciate it.

  • Ashish Bhatt says:

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

  • Jenene Richards says:

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

    • Sumit Bansal says:

      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.

  • Jonathan Inch says:

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

    • Sumit Bansal says:

      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!

  • Jan Wee says:

    awesome! Thank you Sumit!

  • Paul G says:

    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?

    • Sumit Bansal says:

      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)

  • Rachna says:

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

    • Sumit Bansal says:

      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

  • Angie Smart says:

    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?

    • Sumit Bansal says:

      Hello Angie.. You can do that by selecting 7 from the drop down in cell A1.

  • KHUSHBU says:

    CAN U UNDERSTAND ME DEEP TO ATTENDANCE SHEET OF EMPLOYEE

  • SNI says:

    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

    • Sumit Bansal says:

      The easy way would be to add the name in the same row after the tracker ends.

  • justice lover says:

    the formula you entered in NJ AND NK DOES NOT COUNT THE LEAVES PROPERLY. CRATEFUL IF YOU COULS SEE TO THE MATTER. THANKS

  • Erika Mercado says:

    Hi! May I know how would I be able to change the value of casual leave to 0.5? Thanks!!!

  • Fran Pate Hampton says:

    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?

    • Sumit Bansal says:

      Hello Fran.. while the tracker breaks if you try and insert columns, it would work fine if you have these details in column NT or to the right of it.

      • Ibtisam Elkhatib says:

        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

  • Maj says:

    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.

    • Sumit Bansal says:

      In the select working days options, just make all the days as working days (select yes for all)

  • Denise says:

    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?
    Denise

  • SD.K says:

    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.

  • ritu says:

    do you have tracker for 2017

  • ritu says:

    hi how do you add no of employees to the tracker

  • Stephen says:

    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.

  • Mel says:

    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.

  • james says:

    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.

  • Nivedita Singh says:

    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

    • Nivedita Singh says:

      ALSO IF I UPDATE ONE MONTH THEN IT REFLECTS IN OTHER MONTH TOO

    • Sumit Bansal says:

      Hello Nivedita.. Right click on the scroll bar and drag it wherever you want it!

  • pdab09T says:

    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.

  • phil192 says:

    Hey Sumit, love the holiday tracker. I created a similar one, also free to download, check it out here http://www.excel-macros.co.uk/free-excel-tool-for-recording-and-tracking-employee-vacations/

  • Phoebe says:

    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?

  • qasem says:

    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?

  • hawk eye says:

    need to ask how could I make H code for day off full day not half day

  • Onita Fernandes says:

    Hello, Is there a possibility to add the holidays too during vacation?

  • lauren C says:

    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.

  • Dawn says:

    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?

    • Dawn says:

      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!

  • Garrett - Adams Ltd says:

    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?

  • Garrett - Adams Ltd says:

    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?

  • Sue Kelly says:

    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.

    • Sue Kelly says:

      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

      Sue

  • Ivy Lim says:

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

  • SraOshiro says:

    Wow!! It’s exactly what I was looking for. Thank you.

  • Chu Chi says:

    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 🙂

  • Rodolfo Valdes R says:

    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

  • Ivy says:

    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

  • Melinda says:

    Can someone please tell me how you add Bank Holidays into the spreadsheet?

    • emma says:

      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 🙂

  • Bruktawit Alebachew says:

    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?

  • Emma says:

    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!

  • Matthew H. says:

    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!
    Matthew

  • Fernando Mellado says:

    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.

  • Mel says:

    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,

  • Black Coffee says:

    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

    • Sumit Bansal says:

      Hello Alex.. This can be converted into Google Sheets as it uses VBA. I am working on a Google Sheet leave tracker and will share it soon.

  • Laura says:

    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!

    • Laura says:

      Is there any way you can upload a formula for that? I can’t seem to figure it out :o/

  • Chris says:

    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

  • Renee Parra says:

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

    • Sumit Bansal says:

      Hello Renee.. Please use the scroll bar to change months (and not cell A1).

  • Seyi says:

    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. [email protected]

  • Vanessa says:

    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 🙂

    • Sumit Bansal says:

      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))))

      • Erika Mercado says:

        Hi Sumit, I tried to copy and paste this formula but it’s not working.

  • Mirasol Benson says:

    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?

    • Sumit Bansal says:

      Hello Mirasol.. Please use the scroll bar to change the months. Don’t use cell A1 to do this.

  • Teresa says:

    How do you print this template?

  • Helenwebb says:

    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?

    • Sumit Bansal says:

      Hello.. When you open the workbook, it might prompt yo enable macros. You need to click on that prompt button so that macros can work.

  • Helenwebb says:

    HI,

  • Ella says:

    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

    • Sumit Bansal says:

      Hello Ella.. Please use the scroll bar to change the months. Don’t use cell A1 to do this.

  • Celmore says:

    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

  • Claire says:

    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

  • Megan says:

    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?

    • Sue Kelly says:

      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.

  • Amrit says:

    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?

    Thanks

    • Sumit Bansal says:

      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.

      • Christo says:

        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

  • ExcelNewbee says:

    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?

    • Sumit Bansal says:

      In that case, a better way would be to apply a backgroud color to the cell. That way, it will be highlighted but not get counted.

  • Alokparna Acharya says:

    Hello,
    We want to maintain 2016 and 2017 in one excel, can you please help us

    • Sumit Bansal says:

      You need to create separate copies (preferably separate workbooks) for this. This leave tracker only covers one year,

  • simaosavait says:

    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
    thanks

  • Raja dey says:

    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.

  • Alicia Rhodes says:

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

  • Shane Owens says:

    I would like to change the half day value. How do I change it from 0.5 to 1?

  • Celmore says:

    Does anyone know if hours versus codes could be entered? Thanks

  • Ryan says:

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

  • Arikrishnan Ece says:

    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 (http://trumpexcel.com/2015/03/….
    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 !!!!

    • Sumit Bansal says:

      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

  • Celmore says:

    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

  • Nilam says:

    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.
    Thanks,
    Nilam

  • Bri says:

    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?

  • Ravi says:

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

    • Ravi says:

      ahh.. I found the answer in the comments, thanks 🙂

  • Azmaria says:

    Hi,

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

  • Vasanthi R says:

    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 https://uploads.disquscdn.com/images/156a36b1d988100d97549cbca759b81ae5f2a17d700e17e1df6d5b173ec26456.png .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

  • Vasanthi R says:

    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!

  • Lukas says:

    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.

    • Sumit Bansal says:

      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

    • Sumit Bansal says:

      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

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

    Patrick

  • August Ames says:

    Savvy piece , Apropos , others need a HI DoT BB-1 , my colleagues filled out a blank document here http://goo.gl/MFT7jp

  • Vivek Chandra says:

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

  • Vivek Chandra says:

    Can we use 1 tab for 2016 and 1 for 2017?

  • Vian says:

    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?

    • Sumit Bansal says:

      You want to remove the half day leave? You can use other codes except H which is for half leave.

  • Shujat Ali says:

    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

    • Sumit Bansal says:

      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

    • Sumit Bansal says:

      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

  • Lilian says:

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

    • Sumit Bansal says:

      Activate the worksheet and press Alt + F11. The only thing that VBA does in this is hide columns when you use the scrollbar

    • Sumit Bansal says:

      Activate the worksheet and press Alt + F11. The only thing that VBA does in this is hide columns when you use the scrollbar

  • 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,

    Patrick

    • Vanessa says:

      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,
        Patrick

        • Vanessa says:

          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

            Thanks,

            Patrick

          • Hi Vanessa,

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

            Thanks,

            Patrick

          • Vanessa says:

            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?

            Thanks,
            Vanessa

          • 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,

            Patrick

          • kary4567 says:

            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

            =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)*(OFFSET($A$4,0,31*($A$3-1)+1,1,31))))

            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.

            =SUMPRODUCT((OFFSET($A70,0,1,1,372)””)*(IF(OFFSET($A70,0,1,1,372)=$OG$8,0.5,1)*(IF(OFFSET($A70,0,1,1,372)=$OG$5,0.5,1)*(IF(OFFSET($A70,0,1,1,372)=$OG$9,0,1)*(IF(OFFSET($A70,0,1,1,372)=$OG$3,0.5,1)*(OFFSET($A$3,0,1,1,372)))))))

            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,

            Patrick

          • kary4567 says:

            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

          • I hope that it is now working, I must admit it took me a few goes…. 🙂

          • Hi Vanessa,

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

            Patrick

  • Casper says:

    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.

  • Casper says:

    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

  • ARCHANA CHOWHAN says:

    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.

  • anshms says:

    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?

    • Sumit Bansal says:

      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

    • Sumit Bansal says:

      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

  • anshms says:

    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

  • Jamie says:

    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!!

  • Fransisca Emeline says:

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

  • siva vinoth says:

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

  • Sheik says:

    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

    • Sumit Bansal says:

      Hello.. You need to use the scroll bar to change the month and not the value in cell A1

  • Muhd Buk says:

    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?

    • Stephanie Thomas says:

      Yes indeed. My email is [email protected].

      • Stephanie Thomas says:

        Muhud you would need to speak directly with Sumit as I’m not familiar with the chart to that extent. I believe you posted under my question and I thought it was Sumit needing more information for my specific chart needs. Sorry!

  • Morni Sarif says:

    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?

    • Sumit Bansal says:

      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

    • Sumit Bansal says:

      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

  • Stephanie Thomas says:

    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.

    Thanks,

  • Greenhorn says:

    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?

    • Sumit Bansal says:

      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)

  • nina says:

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

    • Sumit Bansal says:

      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.

    • Sumit Bansal says:

      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.

  • Liza.gomez says:

    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?

    • Sumit Bansal says:

      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.

    • Sumit Bansal says:

      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.

  • Russ Ince says:

    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

    • Sumit Bansal says:

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

    • Sumit Bansal says:

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

  • Adam Wishart says:

    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?

    • Adam Wishart says:

      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.

      • Sumit Bansal says:

        Hello Adam.. This won’t work in Google Sheet since this uses VBA as well

      • Sumit Bansal says:

        Hello Adam.. This won’t work in Google Sheet since this uses VBA as well. Also, for me it’s showing Friday on Jan 1.

    • Sumit Bansal says:

      Hello Adam.. For me it’s showing Friday on Jan 1.

  • Karan says:

    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.

  • laaiqah says:

    Hello Summit! Please can you reply to my email….please please…

  • Aaron says:

    Is there a way to set the Year to run from June to May instead of Calendar year

    • Russ Ince says:

      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.

  • Shilpa Patil says:

    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

    • Sumit Bansal says:

      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.

  • laaiqah says:

    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 need for the CASUAL LEAVE to be changed to CAME LATE (THIS SHOULD BE HIGHLIGHTED IN RED) BUT NO DEDUCTIONS SHOULD BE MADE

    ALSO, SOME STAFF WORK ON A SUNDAY AND GET A DAY OFF (CAN YOU INSERT A COLUMN FOR “O” OFF DAY?

    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!!!!!

    • Sumit Bansal says:

      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.

  • Lukas says:

    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.

  • Imran Sheikh says:

    i want to join your class if you offer any classes plz let me know e-mail [email protected]

  • Imran Sheikh says:

    super like bansal

  • Ahvi says:

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

    • Ahvi says:

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

  • Karthick Nathan says:

    hi all how to mark present

  • Manigandan Visvanathan says:

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

  • Nikita Agrawal says:

    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?

  • Nikita Agrawal says:

    hi how do i change the year cycle from april 2016 to march 2017.?

  • paraice89 says:

    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 !

  • Natalie says:

    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!

  • vasanth nair says:

    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

  • Dinarta Abreu says:

    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.

  • Suraj Khairavkar says:

    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.

  • Deepa says:

    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.

  • Dominique Zelaya says:

    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?

  • AA says:

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

  • Jeffrey says:

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

  • Gwen says:

    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 🙂

  • Gwendolyn says:

    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!

  • Ahmed says:

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

  • rose mn says:

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

  • Dinarte Quirino says:

    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!!

  • Canadian Girl! says:

    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?

  • Matt says:

    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?

    • Matt says:

      (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)

  • Matt says:

    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!

  • Matt says:

    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!

  • Rahul Ferns says:

    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?

    • Matt says:

      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 > ‘+’ > Style = ‘Classic’, Next drop down menu = ‘Use formula to determine which cells to format’. formula would look like ‘=B8=$NT$2. Custom Format – whatever color(s)/Fill(s) you want. Click OK. Change ‘Applies to’ to “‘Leave Tracker’!$B$8:$NI$25” which is the entire calendar piece. Make sure you then edit the current “Red” formula to remove the $NT$2 code in that formula (“=OR(B8=$NT$2, B8=$NT$3, etc). If you have one color for each code, you will remove that entire formula and just have one that looks like “=B8=$NT$x” for each code. Hope this isn’t too confusing.

  • Charlotte says:

    Hi Sumit! I’m having an issue with my template, when ever I highlight a cell in March 2016 as a Half Day (H) for example, it duplicates it in the other years (March 2015), and when we erase the duplicate, the original cell highlight disappears as well. Not sure how to fix this, any ideas?

  • Julia says:

    Hi, it seems problem when I edit the file and when I move the scroll bar, it show the table of mistake and say that “mircrosoft visual basic, run-time error”1004″, application defined or object defined error”” I have tried to download the file again to PC and again the same problem happened? How can I fix it? please help.

  • Barbara says:

    can the half days be converted to hours?

  • Chavi Asdhir says:

    I locked A1 and A3 Cells and made it invisible so you cannot change. Now I use tracker to move between the months and it works so much better. Vacations are not copied to the other months. I could not post the new tweaked spreadsheet here unfortunately. Please help us add the department besides the employee name and then it would work like a charm. If anybody need my tweaked spreadsheet please send me an email.

  • Chavi Asdhir says:

    Please add another column for department besides employee name (Sales, Marketing etc) and allow us to Filter by department or show by all department. It will be very useful. appreciate in advance.

  • Siraj Ali Khan says:

    Hello Sir, could you please assist me with how to modify the Half Day calculation from 0.5 Day to 1 Day

  • Siraj Ali Khan says:

    Hello Sir, could you please assist me with how to modify the Half Day calculation from 0.5 Day to 1 Day

  • Liz says:

    Dear Sumit, It’s been a while, How are you? Please i would like to know if i can i use this leave tracker template to track other hr functions? If yes, please how do i go about it?

  • Vishnu says:

    Hey Sumit! Thank for this amazing template. Is it possible to tweak this template to count weekends when it comes to vacation?

  • Jess says:

    Hi – I have 2 additional codes like half day which need to be 0.5 and then once it had added them up in total leaves this month and total leaves this year I need it to add 0.5 into these columns and not add 1 like it currently is. Can you help please.

  • Tonnie Gathogo says:

    the leave tracker is great but i have an issue with this sheet.When you use S code or M Code or any other code for instant, on 15 of April was a sick leave the sheet records in all other months same date as sick leave instead on each month to be blank for you to input your records

  • Irina Rajbhandari says:

    Hi Sumit,

    Its a great template. Thanks. I need 2 different section with Half Casual and Half Sick leave. I could not formulate the template. Would appreciate it if you could help me. I need four different options : Annual Leave, Sick Leave, Half Sick, Casual Leave,Half Casual. I was not able to add formula to account another half day leave.

    Would be great if you could help.

    Thanks

  • Wonuola Scott says:

    The leave tracker is fantastic – many thanks. However, the horizontal scroll doesn’t appear to work very well. When I try to toggle from month to month, it starts off well, then slides to the end by itself, makin git hard to see specific months. Anybody else having this challenge?

  • Nicole Stagg says:

    How can I add a quarter day. Any help would be appreciated

  • francis mormor says:

    Hi Summit, that’s one of the best tracker i have seen so far, CONGRATS. but plss if i want to copy the template from one sheet to another in the same workbook, how do i do that please? i tried with some VBA code and it works but the scroll bar was unable to select the months. can you help please. it gives some runtime error

  • Mohamed Youssef Sarhan says:

    Hi Sumit, Wonderful progress !
    I rebuild the leave tracker to learn but when it comes to the macro code it’s not working.

    B12: where the month number appears using a scroll bar

    H:NO is the year days columns range

    showcalendar()
    SCHEDULE.Range(Columns(Range(“B12”).Value * 37 – 35), Columns(Range(“B12”).Value * 37 + 1)).Hidden = False
    End Sub

    I’m trying to change the equation numbers but it’s not working .. Don’t hide columns or the correct ones .. can you give a hand !?

    Appreciate your help !

    • Dinarte Quirino says:

      I can you help and let me know I change the years I need to add january 2017 and foward

  • Natalya says:

    Hello Sumit!

    I have downloaded the template, thank you very much for allowing free access to it, very helpful.

    However, when I try to move the scroll bar to change the month, it shows the Runtime error 1004 : application defined or object defined error. Why could it be so and can I do something to fix it? Thank you in advance!

  • Debbie Rowe says:

    Hi Sumit, I think your template is excellent. I have some questions about some of the formulas, though, as I am far from an expert. I have been able to add extra holiday types, etc, but am struggling with the part where a sick day is added to the number of leave days per month and year. I have added a holiday entitlement column, also, which then works out how much leave is still available. However, I am struggling to workout for leave which taken in hours rather than a full day. Do you have any advice, please? Thanks, Deb

  • Win Jiang says:

    Thank you for your sharing! But i experienced one issue regarding entry leave code. The leave code wont change when i want to key another month. For example, employee one took annual leave on first April so i typed A on first April. However, the A will exist on first May when i change to another month. Do you have any advice about this? Appreciate if you can give me the solution. Thank.

  • Leah Brenton says:

    Hello, This excel template has the potential to be absolutely fantastic – thank you very much!

    I am just stuck on one thing though. I need to enter retroactive leave for the years of 2014 and 2015 to accurately check leave balances for staff in the present. I added in all of the relevant Holidays for 2014/2015/2016 into the [Holidays] worksheet.
    I then changed the value in A2 to read ‘2014’. I then scrolled through the months and entered in the leave. However, when I change the A2 value to read ‘2015’ and then I scroll through the months to add leave for 2015, the leave I added for 2014 is still associated to those days.
    Is there a way I can add in leave days for multiple years without it affecting each year’s data?

  • Sangeeta Rawat says:

    Thanks a lot Sumit.. A great help.. 🙂 easy and helpful..

  • Edith Asamani says:

    Thanks for sharing this! It’s sooooooooooooooooooooo helpful. God bless you.

  • Fam says:

    How do you create additional leave codes for full and half days?

  • GIM says:

    Thank you for the perfect leave tracker! I do have a question however. If i wanted to include a column next to “leaves this year” titled “vacation days taken”. How would I code that? Essential i want that total to include only vacation, half flex day and full flex day(i included the last one). Also, what formula would I use if I wanted to log only a half sick day taken?

  • Darren says:

    Hi Submit,

    • Darren says:

      Is it possible to have have multiple leave types that are half day-0.5. For instance,i would like to include Sick-S as half day leave as well.

  • Jade says:

    Hi Sumit, the leave tracker is so awesome!. Could you please help me on counting the leave breakup every month beside the yearly summary?

    • Sumit Bansal says:

      Hello Jade.. Glad you liked the tracker. If you want monthly breakup (instead of yearly), use the following formula in cell NL8 and copy paste in all the cell (NL8:NP17)

      =SUMPRODUCT((OFFSET($A8,0,31*($A$3-1)+1,1,31)””)*((OFFSET($A$4,0,31*($A$3-1)+1,1,31)))*((OFFSET($A8,0,31*($A$3-1)+1,1,31)””)))

  • Hims KL says:

    Hi Sumit..can you help me to do this excel spreadsheet
    in google excel? my company is using google excel.. Please assist thanks.

    • Sumit Bansal says:

      Hello.. I am afraid I won’t be able to replicate it in Google Spreadsheets. While the formulas are almost the same in Excel and Google Sheets, VBA is exclusive to Excel.

  • Tony Heir says:

    Hi I am still having problem with this excel work sheet when I put in
    the V under dates and then switch to next month why am I seeing the V
    for previous month, please help.

    • Dinesh says:

      Hi Tony, You have to use the left and right arrows on the scroll bar to move to the next month or the previous month.

      The month number is cell A1 is to give the user the flexibility to
      change the lave year. For example, to have the year as Jan-Dec, make A1
      value 1, for Apr-Mar, make it 4. . Once you have set leave year, you
      need to use the scroll bar only to go to next/previous month.

  • Andre says:

    There is a bug in the month selection. A1 and A3 are both being used to determine the month, but they change independently. For example, if I scroll forward to March, A1 will stay static. If I then select the third month using A1, I am taken to June instead of March.

    • Sumit Bansal says:

      Hello Andre.. The month number is cell A1 is to give the user the flexibility to change the lave year. For example, to have the year as Jan-Dec, make A1 value 1, for Apr-Mar, make it 4. . Once you have set leave year, you need to use the scroll bar only to go to next/previous month.

  • Austin says:

    Thank you Sumit, loved this tracker… it is very much user friendly. Since am very new to excel formulas, can we edit this sheet?
    What we are looking for is like need to add something in the leave description section, Half Day (forenoon and afternoon) and also add some shift codes to use this leave tracker and as a shift tracker as well like (Morning(M), After noon(A) and Night (N). Really appreciate your help in this

    • Sumit Bansal says:

      Hello Austin.. The leave tracker only tracks the Half leave using the code H, but you can add comments if you like. However, you’ll need to stick to H as the leave code for half day

      • Austin says:

        Thank you for the quick reply Sumit, as asked in earlier question can I add some codes for the shift as Morning(M), After noon(A) and Night (N) and these should not count as leave. Currently the formula is set like if hit any character, it counts as one day leave.

        • Sumit Bansal says:

          Hey Austin.. You can highlight the cells by filling it with a background color. That would not make it count as a leave and you can still make out the shift based on a color code.

          • Austin says:

            Thanks a lot Sumit, that was a quick fix!!!

  • Dinesh says:

    Hi Suimt, Thanks for the great tool, Is if possible for you to add Work From Home option and highlight with different color and give the total counts separately for the whole year.

    • Sumit Bansal says:

      Hello Dinesh, as of now there is a provision for five types of leaves (sick, vacation, maternity, casual, and half leaves). You can change any of these with the leave code you want. For example, if you want W for work from home, replace the S with W in NL5 and NS2

      • Dinesh says:

        Hello Sumit..I added additional column for Work from home this year(W) however i dont want work from home to be counted on Leaves this month and Leaves this year.could you please give me the formula for that.

        • Sumit Bansal says:

          If you want to highlight Work from home but don’t want to count it, a quick fix is to highlight the cell by filling it with a background color. That way’s it will be marked but not counted

  • Krupa Adatia says:

    Hi this is such a good and user friendly template. Thank you for creating it. Only problem I am having is when I book a holiday off for an employee, it comes up in the future years too. I only want it for that one year. How do we do that?

    • Sumit Bansal says:

      Hello Krupa.. This tracker is made for one year only. If you want to have multiple years, you can create copies of it

  • Vivian Lobo says:

    Do need to add additional columns (need 3 before Name) without breaking the dates please.

    • Vivian Lobo says:

      Managed to add the additional columns, using the dropbox link below in the comments.

      • Sumit Bansal says:

        Hey Vivian.. Glad it worked.. I just saw your comments, but I guess you managed it before I could jump in 🙂

      • Siraj Ali Khan says:

        Hi Vivian !
        Could you please help me with how to add more columns before Name using dropbox as you have already succeeded in the same.

        thanks.

      • Brian Chege says:

        Hi Vivian…..please help on the addition of more columns.

  • Janine Starey says:

    Hello, When I add a vacation day let’s say January 10th 2016 and then I want to change the month on the upper A1 case the vacation day (january 10th) appears on february 10th, march 10th and so on. How can I have the vacation day ony highlighted on january and not o the rest of the months? becasue it work when I change the month on the scrolling bar but not on the A1 (select month) case. thank you

  • Janine Starey says:

    Hi Sumit, One question when I add for example a vacation day for one employee let’s say January 2nd 2016 and I go on the top to select february or any month of the following year 2016 you can see the V from vacation on the 2nd of each months 2016. This happens only when I choose the month in cell A1 but when I scroll the moths it doesn’t happend how can I fix this?

    • Sumit Bansal says:

      Hello Janine.. To change the months in a specified interval, only use the scroll bar. Cell A1 is to be used only when you want to change the year (let’s say from Jan- Dec to Apr-Mar). Once you have the desired year range, scroll bar should be used to go to next/previous months

  • Vivian Lobo says:

    Thanks for the amazing template Sumit. I saw that you have added an additional column and helped with the template, but it is very difficult to add extra columns I think. If there was some instructions to add columns to the template, it would make it so much easier. As, I wanted to add 3 columns before the Name column and adding them spoils the date formula. However, I appreciate your efforts in this and thank you for sharing the template. Please do consider making the adding of columns a little easier.

  • Gordana Nikolic says:

    Question: when I add employees, the months scroll bar remains in the same place. How to move the scroll bar down, or how to properly insert additional employees?

    • Sumit Bansal says:

      Hello Gordana, Hold the Control key and left click on the scorll bar. Now you’ll be able to move it.

  • Gordana Nikolic says:

    awesome template!

  • Mahe says:

    Still no response from you,,.Sumit……When protecting the sheet …scrollbar doesn’t work and also I have not locked or protected the cell link to scrollbar….pls help on this..

  • Tony Heir says:

    Please advise how I can add further UK holidays to the table.

  • Tony Heir says:

    When I make an entry in one month why does those entries show up when I change months.

    • Sumit Bansal says:

      Hello Tony.. You need to use the scroll bar to change the month. I believe you are using cell A1 as of now.

  • Jaro says:

    Hello Sumit, really nice work! one comment. If I want add different holiday (for two countries) with different color to be displayed it is possible?

    • Sumit Bansal says:

      Hello Jaro, you can do that. First you need to have the list of holidays somewhere in the workbook. A similar table as the one in the holidays tab. Then you need to add a conditional formatting formula to all the cells in the leave tracker.

      • Jaro says:

        Thanks for reply Sumit. In conditional formatting I can see “HolidayListNamedRange” and worksheet with bank holiday are called Holiday
        What exactly in code mean “HolidayListNamedRange” ?

        I want add couple holiday workseets (holiday_US, holiday_UK..) For all of new add new formatting with different colors.

        but where define different worksheets name?

  • pratheesh rajan says:

    Hello Sumit, First, I would like to say that the leave tracker which you have made is superb and very easy to use!

    I’m trying to customized base on requirement, and trying to use the same sheet as a shift tracker.
    As part of this I need your support:
    1) I’ve to put the log-in time of each employees when they are present. So, in column NJ (Leaves This Months) & in NK (Leaves This Year) should pull the total no. of days and ignore the cell were the log-in time is mentioned
    .currently if we put the login time then it is getting added as leaves.
    2) I’ve added three more column, which in NU, NV & NW this column will count the no. days he/she had login in particular region so that in end of the month it will be easy to calculate the shift allowance. for eg. if a person log-in at 12:15 3 days, then column NU (UK Login i.e 11:15 am to 8:30 PM) will show as 3 days.
    It will be really helpful if you can help me in putting the formula to get the desire results,

  • Mahe says:

    When protecting the sheet …scrollbar doesn’t work and also I have not locked or protected the cell link to scrollbar….pls help on this..

    • mahe says:

      Hi Sumit,
      Awaiting your response…pls help on query.

      • Sumit Bansal says:

        You need to make sure the cells that are dependent on the scroll bar are not protected. Simply protecting the entire sheet wont work

  • Administrator UMCS office says:

    This is such a awesome tool! I tried to follow the thread below as I am trying enter additional leave codes for half days for vacation, sick and personal. Any idea on how I can accmplish this?

    • Sumit Bansal says:

      Hello.. As of now I have made this for five leave codes. If you want to use more leave codes for full day leaves, you can simply enter the code in the leave tracker and it will count it as a leave. It would however not highlight it. As of now, you can only use h/H for half day leave.

      • Administrator UMCS office says:

        I understand but I was trying to get the half days to apply to the appropriate leave breakup, hence SH, VH, etc. Where is the tutorial I can download an updated version from?

        • Sumit Bansal says:

          You can download the updated version from the tutorial above. There is no video tutorial on this, just the template

  • Henry Ngure says:

    why is it that when i select a day in one month it gets selected in all other months?

    • Sumit Bansal says:

      Hello Henry.. I believe you are using the value in cell A1 to change the month. Instead use the scroll bar at the bottom.

  • Neha Chetal says:

    wonderful template.Please assist me with one issue.suppose i sel;ected Jan Month and entered the leave in the columns of the respective employees.and when i scroll for the next month i.e feb and try to add the leaves for the month,the leave balance for the Jan month is affected.whatever changes i try to make it happens for previous month too

    • Sumit Bansal says:

      Hello Neha.. When you use the scroll bar and change the month, column NJ shows leaves only for the selected month. So if you have 2 leaves in January, and you use the scroll bar to come to february, column NJ would show leaves of Feb only. However, Colunm NK tracks all the leaves in that year.

  • Nilaksha Silva says:

    Thanks Sumith, it realy works

  • Abeer says:

    Thank you for this goog job. Seems there is an error in the sheet as when I enter the vacations for example in January then I move to Feb to enter another vacations, The vaction which I entered in Jan coming to Fenruary automatically! Can you please find whats the problem?

    • Sumit Bansal says:

      Hello Abeer,, I believe you are changing the month value in cell A1. Instead, use the scroll bar to change the month.

  • kewiliko shikonda says:

    does the tracker only track fr one year i want to save the leaves for one year nd continue wth the nxt year is it possibble

    • Sumit Bansal says:

      Hello.. The tracker is made for 1 year for now. If you want to make it for multiple years, you can have create multiple copied for different years.

      • kewiliko shikonda says:

        thank you

      • Sharon Brooks says:

        Is it possible to do this within the same workbook?

  • mahe says:

    Thanks for good tracker!

    When leaves are updated for Feb month ie 25 Feb, same leave codes are copied for other months as well which duplicates the work and same case for previous months ie Feb 2016 data is reflecting in Jan 2016.

    • Sumit Bansal says:

      Hello Mahe.. Just want to make sure you are using the scroll bar to change the month (and not the month value in cell A1).

      • mahe says:

        Yes, I was changing the month value….thanks will scroll to change the month…whether I can display the scroll bar vertically?

        Also in holiday list whether I can name it as public holiday and optional holiday for each month with color code to view in main.sheet?

        • Sumit Bansal says:

          You can make the scroll bar vertical. Hold the Control key and left click on the scroll bar. You will see an outline on the scrollbar. Then you can resize and make it vertical.

          As of now, there is no way to classify holidays in the tracker

  • Kathryn Kitchener-Pope says:

    After finishing the tracker for a month, when I change the month in column A1 the holidays do not wipe out. If I delete them, then the entire spreadsheet becomes zero. Is there a fix?

    • Sumit Bansal says:

      Hello Kathryn.. To change the month, use the scroll bar at the bottom. As you click on the scroll bar, the months would change and the leave record would remain intact.

      • Kathryn Kitchener-Pope says:

        The holiday cells are staying highlighted no matter what the month is. I’ve saved the doc as a xlms – but it’s still not working properly 🙁

        • Sumit Bansal says:

          If you don’t want the holidays to get highlighted, you can remove these by deleting the holidays data from the Holidays Tab.

  • Kathryn Kitchener-Pope says:

    When I change the month ont he column A1 after finishing the tracker for a month. The holidays do not wipe out, if I delete them then the entire thing becomes zero. Is there a fix?

  • Sharon Hoo says:

    I am so happy that I found this template and downloaded it immediately. But, when I marked “C” casual leave for one of the employees on a date, let say 10th of February. All other months are marked with C on the 10th! I am very confused here, please help!

    • Heather says:

      That’s what happened with mine! I am hoping I will get a response soon as it is a really helpful template other than that small glitch!

      • Sumit Bansal says:

        Hello Heather.. To change the month in the leave tracker, you need to use the scroll bar. Don’t use the value in cell A1 to change months (it’s just to specify the time period for the leave tracker)

    • Sumit Bansal says:

      Hello Sharon.. To change the month in the leave tracker, you need to use the scroll bar. Don’t use the value in cell A1 to change months (it’s just to specify the time period for the leave tracker)

      • Sharon Hoo says:

        The control bar is not going to the next month, hard to control. Is there a way to make it more responsive? And, sometimes the entire spreadsheet was highlighted and hang there. Hoping for your help soon.

        • Sumit Bansal says:

          Hey Sharon.. A lot also depends on the computers configuration. Scroll Bar tend to just keep scrolling if you are using a machine is less memory. It seems to work fine a multiple machines I tried

  • Morni Sarif says:

    Is there a way to create more than one half days, say for sick leave or casual leave

  • Sahar Ashraf says:

    For some reason, every time I enter a day in april it reflects on all the other days. How can I change that? For example I put a V day in april, and when I went to the next month it showed up on that as well.

    • Sumit Bansal says:

      Hello Sahar.. To change the month in the leave tracker, you need to use the scroll bar. Don’t use the value in cell A1 to change months (it’s just to specify the time period for the leave tracker)

  • Wondimagegn Leta says:

    This is very wonderful. My question is about the maximum number of leaves allowed.

  • Heather says:

    Hi, great tracker although I am a little confused – when i put in a day off / vacation / sick leave etc. and I change the month it then has it on every single month not just the one I placed it in!? Help would be great!
    Thanks

    • Sumit Bansal says:

      Hello Heather.. Kindly use the scroll bar to change the month value. I believe you are changing the value in A1.

  • Ramya says:

    Hi Sumit This is Great Thank you. 🙂

    • Ramya says:

      Having started to use it, I have a suggestion for you.
      It would be nice if the working days changes as per employee as I manage a company with staff working on different days. Wondering if that is a possible tweak?

      • Sumit Bansal says:

        Hello Ramya.. If you have different employees working on different days, You can create different versions of this tracker. To have it all in the same tracker will complicate it a lot.

      • Sumit Bansal says:

        Thanks for commenting Ramya.. Glad you liked it.
        If you have different employees working on different days, You can create different versions of this tracker. To have it all in the same tracker might complicate it a lot.

  • Kris Wiszowaty says:

    This tracker is impressive, I can see a lot of use in the future. I do have a little request for you.
    Is there some way to easily change the totals at the end of the row to equal hours instead of days?
    Example:
    Sick Leave = 8 hours
    Vacation = 8 hours
    Half Day = 4 hours
    (or if someone happens to leave for an appointment)
    Appointment = 2 hours
    and in the final column have a calculation that would take those hours and give a total amount of days?

    • Sumit Bansal says:

      Hello Kris.. You can achieve that by multiplying the leave count with the number of working hours in a day. For example, if the total leaves for a month is 2, then hours could be 2*8 (considering an eight hour work day)

      • Karina says:

        Hello Sumit, First of all, your tracker is amazing. I have been trying to modify the formula by multiplying the leave count by 8 and I keep getting an error message. Any suggestions

  • Jewels says:

    Is there a way to account for Lieu days? I am trying to insert a formula but it is counting as a day away.

    • Sumit Bansal says:

      Hello Jewels.. Currently there is no way to account for lieu days. You can have the Lieu days in a separate cell for each employee and add it to the total leave count if you want

      • Jewels says:

        Thank-you very much Sumit, I love the tracker. It is extremely user friendly.

  • rgran says:

    Hi, Would it be possible to edit this so standard year is 01/04 to 31/03?

    • Sumit Bansal says:

      I have updated the tracker and now you can select the starting month (for example Apr 2016-Mar 2017). You can download the new tracker from the link in the tutorial.

  • faye says:

    hi, could you help me with this the same format yet the month will be Jul to dec only and it be starting on column 2

    this is a great help.

    • Sumit Bansal says:

      Hello faye.. I have updated the tracker and now you can select the starting month (for example July 2016-June 2017). You can download the new tracker from the link in the tutorial.

  • Elizabeth Gomes says:

    hi.. loved it…….. want to know if there is a way to get total of the codes at month end.. like total number of sick days…. or V days…

    • Sumit Bansal says:

      Hello Elizabeth.. I have updated the tracker and now you can get the leave break-up by leave type. You can download the updated tracker from the link in the tutorial.

  • Liz says:

    Hello Sumit, how have you been? Have a thing or two l would like you to help me out with. In my company, we do not take out sick leave from a staff leave schedule. would like to know how l can use the (S) sick leave with out it being deducted from the staff leave

    • Sumit Bansal says:

      Hello Liz.. I’ve been good.. Nice to see you again. For marking sick leave but not counting it, a better way would be to simply highlight the cell with a background color, but not enter anything in it. That way it will be marked and wouldn’t be counted in the total leave count.

    • Sumit Bansal says:

      Hello Liz.. An easy way would be highlight the sick days (using fill cell), but not enter any leave code. That way, it will not count it in total leave count

  • Malsha Jayamaha says:

    Hi Sumit, this template really helped me. I need a small change would you be able to show the leaves separately.

    • Sumit Bansal says:

      Hello Malsha.. I have updated the tracker and now you can get the leave break-up by leave type. You can download the updated tracker from the link in the tutorial.

      • Malsha Jayamaha says:

        Thanks a bunch 🙂

      • Malsha Jayamaha says:

        Thanks a bunch 🙂

      • Malsha Jayamaha says:

        Thanks a bunch 🙂

  • Sabastian says:

    Wow! fantastic.Thank you for this template .Can you create an Excel sheet whereby sheet one is able to update other sheets in the same work book .If so please let us get in touch through skype

    • Sumit Bansal says:

      Hello Sebastian.. It can be done but by interlinking cells across sheets. Kindly let me know what you are looking for, and I’ll try my best to guide you

  • Lori says:

    The leave tracker is the closest I’ve seen to what I am looking for. I am trying to create an employee time tracker spreadsheet that will record working hours, calculate overtime hours based on a 40 hour work week, sick time, and vacation time. I want this spreadsheet by pay period with the ability to record a portion of day vacation/sick and portion of day working. This is where I am running into difficulty. Ideally this spreadsheet would provide a pay period summary for each employee for the following; hours worked, vacation hours taken, sick hours, and overtime hours, as well as a yearly summary for managers showing total sick time taken/remaining, vacation time taken/remaining, etc.

  • Mar says:

    Is it possible to have the total separately? like annual paid leave is separated from sick leave on the calculation?

    • Sumit Bansal says:

      Hello Mar, if you don’t want the total count to include sick leaves, it would be best to highlight sick leaves with a background color, but not insert any alphabet code in it. That way the total leave count wouldn’t have the sick leave count in it.

    • Sumit Bansal says:

      Hello Mar.. One easy way to do this would be to highlight sick leaves with a background color, but not enter any code in the cell. That ways, it wouldn’t be counted in the total

  • Krutika says:

    Hi Sumit, I have a few specific changes that I am trying to make to the sheet. Would it be possible for me to send this to you over email so that i can explain it well. I can then post a summary on the comments section for everyone else.

  • Maggie Wilson says:

    I really like this tracker, any ideas on how I would go about setting up an automated system for personal and sick time accrual so i dont have to go in each month and do it manually?

  • Carolyn says:

    How could you change this to be a jul 1 – jun 30 fiscal year?

    • Sumit Bansal says:

      Hello Carolyn.. As of now I have created it for Jan-Dec.. Will soon create one for custom periods and share here with you

  • Dawn says:

    I would like to amend the sumproduct formula in NJ to exclude a all leave codes except annual leave and half days so they do not get counted. All other leave is recorded but does not need to be counted. I have tried to get to grips with this formula, but am finding it difficult. Also if I click on any cell in NJ, click in the formula bar and hit enter, it comes up with #value! even though I have made no changes. Can you please break your formula down so I can try to amend it. I have never used sumproduct before.

    • Dawn says:

      I had a Eureka moment. I changed the “” to =$nn$3 and it has worked. However, if I needed to include other leave types, I would not know how to accomplish that.

      • Sumit Bansal says:

        Hello Dawn.. there are five types of leave codes already.. if you need to have more, simply use any alphabet code. As soon as you enter any code, it will be counted as one.

  • Liz says:

    Hello Sumit, can’t tell you how grateful l am, l have downloaded it through the link and l have gotten what l wanted. Thank you so much 🙂

  • Liz says:

    For instance, an employee takes two days in January and five days in February, l don’t get to see the total seven days when am on February, l only see the 5day in february and when l go to January l see the 2day.

  • Liz says:

    Hi Sumit, l have downloaded the template again and it still calculates how many days taken per month, it still does not summarize the whole days being taken as the months go by.

  • Chido Okafor says:

    Hello. Does this allow one input the total number of leave days allowable and also track the number of leave days outstanding for an employee?

    • Sumit Bansal says:

      Hello Chido.. I have updated the template so now it will also show the leaves availed in the entire year. This would be helpful in knowing how many leaves can still be availed by each employee

      • Chido Okafor says:

        Thanks so much. This is so nice.

        If you could include 2 more columns to show “Allowable Leave Days” and “Leave days balance”, it would be great.

      • Chido Okafor says:

        Thanks Sumit.
        Kindly make provisions for total leave days allowable as well and leave days balance if its possible.

  • Liz says:

    Hello Sumit, thanks for leave tracker, l have figured out how to change the holidays to the ones we observe but still wish l can get the leaves taken calculate totally as the months go by rather than the monthly calculations we already have

    • Sumit Bansal says:

      Hello Liz.. I have updated the template and now you can get total leaves as well (availed so far in all the months). Kindly download the template again

  • Lynnae says:

    This is awesome, thanks! Is there a way to ‘select working days’ for each employee? Also, is there a way to change the “leave code” highlight color from red to a different color for each? I’d like to color code the leave days. Thanks again!!

    • Sumit Bansal says:

      Hello Lynnae.. It wouldn’t be possible to have different working days for different employees.. You can however, change the color codes by changing the conditional formatting setting

      • Nix says:

        Sumit could you please explain how? I’ve added a leave code and want to assign a colour to it but can’t and every leave code is coming up red. Your spreadsheet is amazing btw.

  • Jet PinoyGooner says:

    and by the way this is just awesome!!!!! You save me heaps of time

  • Jet PinoyGooner says:

    Hi Sumit,

    How can I have the total Leaves for the entire year?

    Thanks

    Jet

    • Sumit Bansal says:

      Hello Jet.. Thanks for dropping by and commenting.. I have updated the template so now you can get total leaves for the entire year as well. Kindly download the template again from the link in the article.

  • Yaz says:

    Thanks for the Employee Leave Tracker Template – Can it be customised to include more leave variation like Working from Home but not calculate those days in the # of leaves column

  • Liz says:

    Hello Sumit, l like the planner but here in Nigeria our holidays are different from yours so how do l cancel the holidays that we in Nigeria don’t have and also change the orange colour on those days so it can reflect as normal days?

    • Sumit Bansal says:

      Hello Liz, you can change the holidays in the Holidays tab.. There is a table with dates. Just change it the ones that you follow. The tracker would automatically show the orange color for only the ones that you have specified as holidays

  • Meredith says:

    oh, I figured it out myself, its how you save it. clever me!

  • Meredith says:

    Hi, when I use the slidebar to change the months, the dates are only showing for the first month January, not any other months- what am I doing wrong please- there is a pop up message about macros disabled, how to I enable them, I am a novice sorry.

  • Astroud says:

    Hi, thanks for an awesome tool. Would it be at all possible to add a table where each employee’s types of leave are shown as a sub-total then also show a total? It will give an overview of all the leave taken.

    • Sumit Bansal says:

      Hello Astroud.. I have updated the template so now you can get total leaves for the entire year as well. Kindly download the template again from the link in the article.

      • Astroud says:

        Hi Sumit, thank you for that, much appreciated.

  • Danny says:

    Hi There, Is there any chance I could change this from April 2016 – March 2017 as that is when our next leave year runs! Thanks!

  • S Farha Anwar says:

    Hi Sumit. I would like to add the employee ID and department before the name but when i do so the formula eliminates the first 2 days of the month. Please help 🙂

  • Naveen N says:

    hi when the same sheet is uploaded for spreadsheet the scrol bar and months are not visible any help on this matter

    • Sumit Bansal says:

      Hello Naveen.. What do you mean when you say “uploaded for spreadsheet”?

  • June says:

    the issue I have with the half day is that it doesn’t seem to allow the user to input they type of leave that is being utilized…..just records that a half day of “leave” was taken. also, I would like to see a summary of each employees’ type of leave that has been used as opposed to the sum of all leave for example, 3 days sick, 1.5 days vacation, 2 days compensatory.

  • Kelli says:

    Hello All,

    Our employees work one week in the whole, so I don’t record the PTO used until the week after it has been used. When I opened up the sheet today, the only dates in January showing were from the 11th (today’s date) through the end of the month). Is there a way for the entire sheet to show for the entire month and thereafter??

    • Sumit Bansal says:

      Hello Kelli.. The template shows the dates for the entire month. You can use the scrollbar to change the months.

  • reenu sharma says:

    hi sumit. leave tracker updated is really great and helpful . please let me know how to add column before emp name .. i want to add emp id column however while doing so the whole sheet gets messed .. Please help

  • Jamie says:

    Great spreadsheet Sumit. Is there an easy way to use this sheet to track days my employees are late, be great to have that total in a separate total Column?

    • Sumit Bansal says:

      Hello Jamie. You can use this formula in cell NK (and drag for all the remaining cells). =COUNTIF(B8:NI8,”L”)

      Now when you enter ‘L’ (for Late) in any of the cells, it will be counted.

  • Dale Wicks says:

    How could I put actual hours in here and have it add them up also I tried to add a column B for the employee id and it skews the January date to start with 2. I found where to change it to hide from C in the Macro.

  • Siew TPV says:

    In the formula mention to Sheet 3 but there is no Sheet 3 or i was missing while i downloading, pls explain, it is very helpful for me

    Thx

    • Sumit Bansal says:

      Glad you are find this helpful.. Sheet 3 is hidden. You can make it visible by right clicking on any of the tabs and selecting unhide. It will show a box with Sheet 3 in it. Select it and click on OK

      • Siew TPV says:

        Hi Mr. Sumit
        Thanks for the sheet 3 purpose but i have 3 more Questions
        – What if i’d like to adjust date of the month start from 21.Dec.2015 to 20.Jan.2016 (For 1 month)
        – Change Leave code
        – Add Holiday

        is it able to adjust?

        Thank you 🙂

        • Sumit Bansal says:

          – Changing month start and end date would be difficult.
          – You can change the leave codes in cells NN2 to NN6. The tracker, however can track any code you enter in it.
          – You can easily add holidays in the table in the holidays tab.

  • Chelle says:

    Hi, wondering if there is a way to change the position of the scroll bar, after adding more employees?

  • Al Ballard says:

    Love this leave tracker but having some issues. When I scroll to Feb, March, etc… all the calendar dates disappear. January is beautifully labeled 01, 02, 03, etc… However, when I choose a different month there is no data in row 5. There is a formula but the cells in row 5 are blank.

    • Sumit Bansal says:

      Thanks for commenting Al.. Since this workbook contain a macro, you need to enable the macros in it. When you open the workbook, you would see a yellow button that says -‘enable content’. Once you click on it, the tracker should work. I just checked it on my system and seems to be working fine.

  • rachel levak says:

    hi! how do i add a column to the right of “employee” to say something like “title” or “time zone” without messing up the entire formula?

  • suma says:

    Hi, can you please help me creating a dashboard sheet where we can get total leaves of each employee

    • Suma says:

      Hi Sumit,
      Can you please help me in creating a dashboard where I can get total leaves of each employee. Please do need full help.

      • Sumit Bansal says:

        Hello Suma.. I have updated the template and now you can get the total number of leaves as well. Kindly download the template again using the link in the tutorial.

  • Justin says:

    Hi Sumit, when I tried making amendments to formula such as changing “=SUMPRODUCT((OFFSET($A8,0,31*($A$3-1)+1,1,31)””)*(IF(OFFSET($A8,0,31*($A$3-1)+1,1,31)=$NN$6,0.5,1)*(OFFSET($A$4,0,31*($A$3-1)+1,1,31))))” to “=SUMPRODUCT((OFFSET($A8,0,31*($A$3-1)+1,1,31)””)*(IF(OFFSET($A8,0,31*($A$3-1)+1,1,31)=$NN$5,0.5,1)*(OFFSET($A$4,0,31*($A$3-1)+1,1,31))))”
    The $NN$5 causes the formual to display #Value . Urgently need help

  • Maria Lira Enriquez says:

    Hi! Will it be possible to track the no. of SL and VL separately minus from SL and VL credits for a year?

  • Jennifer Warkentin says:

    Couple of questions. Will the sheet keep a running total the time taken off for every employee (For example, so you can see in May how much time was taken?) If I revert the sheet to a google doc with the formulas transfer?

    • Maria Lira Enriquez says:

      Hi Please! How can I include Half day Vacation and Half Day Sick leave?

      • Sumit Bansal says:

        Hello Maria.. You can record half leaves by using the code H. Half day leaves are highlighted in yellow, while full day leaves are highlighted in red.

    • Sumit Bansal says:

      Hello Jennifer.. It keeps a track of all the leaves taken in the entire year for all the employees. So if you mark the leaves for May, it will show the total for May. If you then mark the leaves for June, it will show the total for June, however, if you go back to May, it will have the leaves for May as well.

      I haven’t checked this on Google Doc, but I feel this wont work in Google Docs.

  • Kacey Bowman says:

    Sumit, I really like the lay out and ease of use of the spreadsheet, the only component that I am missing is the ability to see the yearly total of vacation days used and be able to subtract it from each employees yearly vacation accrual. This would then allow me to see what vacation total for the year has been used and what is remaining. Just curious if there is a way I can add these functions. If you have any tips on how it would be greatly appreciated.
    Thanks

    • Sumit Bansal says:

      Hello Kacey.. I have updated the template and now you can get the total number of leaves as well. Kindly download the template again using the link in the tutorial.

  • Kiran Hamid says:

    Amazing! So helpful

  • Kelli says:

    This is great! I am trying to modify it to calculate the amount of PTO a given employee has remaining, as well as to count the number of days off that the employee takes without pay. Any suggestions??

    • Sumit Bansal says:

      Hello Kelli.. I have updated the template and now you can get the total number of leaves as well. Kindly download the template again using the link in the tutorial.

      • Kelli says:

        Great! Thank you very much! this template is excellent!

  • Robert Fernandez says:

    Hi Sumit.
    How can I make some of the leave types not countable? Or is there a way to get totals of each type of leave in a separate column? rather then counting all the leave together?

    • Darren says:

      It will be good and useful if Sumit can do this

      • Sumit Bansal says:

        hello Darren.. I have updated the template and now you can get the total number of leaves taken so far (along with the monthly leaves). Hope this helps

  • P Dasgupta says:

    Hi Sumit,

    Thanks a ton for sharing the excel sheet. It has helped me in sorting out the employee leave details to a great extent.
    Just one request: Can you please share the formula to include weekends (Saturday and Sunday) under “# of Leaves” column. In my company, we don’t have a weekend system in place and rather have weekly offs for employees.
    Looking forward to a favorable response from your end.
    Regards

    • Sumit Bansal says:

      Hello.. I have updated the template and now you can select the what days are working and what are not. Kindly download the template from the link in the tutorial.

  • P Dasgupta says:

    Hi Sumit

  • Lerum Walders says:

    Is there a way to lock the worksheet but keep the scroll bar functionality? When I protect the sheet the scroll bar no longer works! I have Unlocked the scroll bar, as well as, the Cell A3. After I protect the sheet and try to use the scroll bar the debug note comes up.

  • Rachel Johnson says:

    Hi, love this, is there a way to track the sick days annually? The way our company works employees get 3 per fiscal year. Also, this may be a stretch, is there a way once the employee reaches their three days that their row can be filled in a certain color? Thank you!

    • Lerum Walders says:

      Is there a way to lock the worksheet? When I lock the Worksheet the Scroll Bar stops working!

  • Brian Wamatu says:

    Great Leave Tracker… Quick question — how can I add 2016 in the same calendar?

    • Sumit Bansal says:

      Hello Brian.. I have updated the tracker for 2016. You can download it from the tutorial above

  • Balvin S says:

    Hi Sumit. How can I capture if an employee takes half day leave?

  • Riccardo Urbanet says:

    Your Excel file is gorgeous. Only one thing is missing, could you give me a hint on how to sum all the leave DAY of the year ? we have 20 days each years, would be nice to undestand how many days are left month by month in a column near the day for each month

    • Sumit Bansal says:

      Hello Riccardo.. I have updated the template and now you can get the total number of leaves as well. Kindly download the template again using the link in the tutorial.

  • Amrita Damania says:

    hi sumit,
    the leave tracker is great, however i would like to gray out fri and sat as the weekends as these are the weekends in UAE.
    how can you help me with this ?

    • Sumit Bansal says:

      Hello Amrita.. I have updated the Template. Now you can select weekends from the list. You can download the updated template from the tutorial above

  • Luke says:

    Within my office, many of the employees have different start and end dates for their contracts (which roll-over 6 months at a time). Using this template, is there a way to calculate the number of days of annual leave they have taken only within their current contracted 6 months.
    For example: For Person 1 – I would need to know how many days they had taken between January 28th and June 28th. But for someone else this might be March 28th – September 28th.
    I understand I could calculate this by editing the formula =COUNTA($B8:$NI8), but I would have to allocate these for each employee. Is there a way to change the formula so that I could input Start & End dates for each persons contract somewhere else, and the COUNT formula would then use the information in these cells to count within the appropriate range of dates?

    From here I have no problem calculating remaining days. It’s just calculating individual contracts that is causing issues.

    • Luke says:

      Sorry! Also is it possible to add an option for a ‘half day’ of annual leave, rather than a full one.

      • Sumit Bansal says:

        Hello Luke.. I have updated the template and now you can add half day leaves

  • Desmond says:

    Hi, do you know how to clear contents whenever you change the year? For instance January 2015 -December 2015 cell values will be remove when you plan your schedule for January 2016 -December 2016

    • Sumit Bansal says:

      Hello Desmond.. The easiest way would be to download this again and change the year. You can also do this manually by going to each month and deleting all the records,

  • Rod says:

    Your Excel worksheet is great, but I need help on also including the weekends as part of the # of leaves total. How can I add the weekend as part of the total # of leaves?

    • Sumit Bansal says:

      Hello Rod.. I have updated the template so you can select what days are working days and which ones are not. Kindly download using the link in the tutorial above.

  • Sandy says:

    This is amazing – do you have a 2016 version too?

    • Sumit Bansal says:

      Thanks for commenting Sandy. If you change the value in cell A2 to 2016, it will become the leave tracker for 2016. You will need to update the holidays for 2016 though.

      • Sandy says:

        You legend – thank you!

      • sunny sandhu says:

        how about if I need to add half day and sick leave and don’t want that to be counted in the last tab , what should I do to solve that issue . there is one more cant add any other column or row coz it shift everything , any help .

  • Alan says:

    2016 Version Release soon?

    • Sumit Bansal says:

      Hey Alan.. Just Enter 2016 in cell A2 and you will have it. You would have to add the holidays for 2016 though.

  • Sharmila Srinivasan says:

    I just discovered the leave tracker and it is great. planning ot use it for 2016. i need to do a bit of edit though. Appreciate your help. I need the value in NK8 to be updated every month. that is reduce the leaves taken

  • Deepak says:

    I am not in the winner list 🙁

  • Rakesh Kv says:

    Hi,

    How to move the scroll bar to next cell.. has employee are more than count 10

    • Sumit Bansal says:

      Thanks for commenting Rakesh.. 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. I did it for you here (can have up to 40 employees) – https://www.dropbox.com/s/qsfyo70kyf1ugvh/Excel-Leave-Tracker-40Rows-TrumpExcel.xlsm?dl=0

      • Zyra says:

        Hi Sumit! i would like to use this template for 100 employees where all days are working days… I also want to insert more columns where I can put the employees position, hire date, etc. Hope you could help me. Thank you so much.

  • K.D.Nelson says:

    How can we have half day leave recorded in the templet?

    • Sumit Bansal says:

      I have updated the Template. Now you can enter Half Day leave by typing the code H. It will be counted as 0.5

      • Zyra says:

        hi sumit! where is the formula for 1 day leave? i would like to remove 0.5

  • Sarvanmani S says:

    u made an excel template for time and matrix one, the entry in one excel automatically refelcted in time matrix page. i do have special requirements regarding that sort or type can make it

  • Sarvanmani S says:

    hi sumit
    i watched ur videos found interesting
    the stuff like this im being waiting let me check out
    all tutorials are really short and effective

  • Stephanie Rodriguez says:

    This is amazing – I am trying to edit it so that I can use it for 2016 – 2017 – I already have the holiday for both years applicable to the company. Any pointers to do this?

  • Siddharth Mehta says:

    Thanks a lot Sumit for this wonderful tool. !! Need your help with one customisation though. My team has members from different countries and their holiday calendars vary accordingly. Can I insert additional holiday sheets and with different colour codes? Your guidance will be much appreciated.

  • Nicole Lavery says:

    I love this tracker and would like to use it however, although I can get by in excel I’m by no means an expert. I need this to run from April to March but although I can change the months easily enough, the days don’t correspond though. Does anyone know how to adjust this?

  • PacoDiaz says:

    Eres un Maestro. Agradezco tu tiempo y el que compartas tus trabajos.
    Por favor,¿cómo pudiera generar desde Septiembre de 2015 a Agosto de 2016?
    Lo intente de varias maneras pero me sale error, Gracias!!!

  • Vi Reak Sweetpain says:

    can i change the weekend? cuz i still work on saturday

    • Sumit Bansal says:

      I have updated the Template. Now you can select weekends from the list. You can download the updated template from the tutorial above

  • Marvin says:

    how do you edit the weekends days? here in the middle east, we usually have fridays & saturdays as weekends.. thanks.

    • Sumit Bansal says:

      Hello Marvin. I have updated the Template. Now you can select weekends from the list. You can download the updated template from the tutorial above

  • Its a fabulous software. Friends #Leave Monitor is also providing the same kind of software. Experience it really you will like it also. Click here: https://www.leavemonitor.com/

  • Anjali Jha says:

    Could you please change the weekend days to be Sunday?

    • Sumit Bansal says:

      Hello Anjali.. I have updated the leave tracker template. Now you can select the weekends

  • Digital Marine says:

    This was really helpful!! Thankyou!:)

  • Asha Niña says:

    Hi, great share. Is there a way where I could have an additional code to sum the total number of lets say “Sick Leave” for Employee 1 and etc?

    • Asha Niña says:

      in addition, how do we adjust the weekdays? Since we have working days from Monday till Saturday. So the only day off is on a Sunday

      • Sumit Bansal says:

        Hello Asha.. I have updated the template. Now you can select what days are working and which ones are not

  • Dilshad Hussain says:

    Nice one, Sumit.

    Thanks for this.

    I have one request, how can I add # of Leaves for a complete year

    • Sumit Bansal says:

      Hello Dilshad.. I have updated the tracker and now you can have the total number of leaves for the complete year. Kindly download the template again from the link in the tutorial

  • Sirikorn Phanthabut says:

    Hi Sumit, this is a really great tool! It’s a thousand times better than my super basic Excel. Thank you so much for sharing it. However I would like to add more columns “Role” and “Team” next to “[Employee] Name” but can’t work out how to do that. Please help.. 🙁

  • Stan Paul says:

    Great product. I would like to add more employees say 20 total and move the slider for the months down. How can i do this?

  • pradeep P says:

    Amazing. I have asked my team implement with immediate effect. Summary for whole year (employee wise & Type of leave in the columns) would be great

    • Sumit Bansal says:

      Thanks for commenting Pradeep. I am glad you find this template useful. I will soon update it with a summary

      • Aymen says:

        Hi did you get any chance to update it with the summary, i am so relief looking at this template and what to implement it in my company

  • KJ Sridhar says:

    Ur work is awesome. Additionally, I would like to do more with your template…

  • sayem ahmed says:

    How will i get balance of Annual Leave casual leave and sick leave separately

  • Jason Farrell says:

    Hi, Would it be possible to edit this so standard year is 01/04 to 31/03?

  • Theresa Mascarenhas says:

    this is the Bestest excel i have used till date. its so helpful. thank you!!!

  • Mohammad says:

    thank you for the leave template. 1 question, is there a way to change the weekends?

    • Sumit Bansal says:

      Thanks for commenting..You can change the weekend by tweaking the Weekday formula in this template. More about weekday formula here – http://trumpexcel.com/learn-excel/excel-formulas/WEEKDAY/

      • Mohammad says:

        Thank you, I tried to figure out how but honestly I’m total noob in excel formulas. Your template is really really awesome! but can you please upload a version where the weekend is Friday and Saturday? it would be very much appreciated.

        • Sumit Bansal says:

          Hello Mohammad.. I have updated the template and now you can select the weekends.

          • Andrew Berigan says:

            Hi, Would there be a version which would allow me to upload to google sheets as I need something like this on the cloud

  • dd says:

    but there is no validation, suppose if five people apply on a same day then how we tackle. There should be validation where more than 3 or 4 people can not mark their leaves. a message box will prompt to select another day..

  • Andrew Luanda says:

    Amazing piece of work! I want to extend the no of workers but I I fail how to remove the horizontal scroll bar in row 18, please some idea!

    • Binaya Karmacharya says:

      Right click on the HSB and drag down in this way you can add more Rows in between. Hope Sumit don’t mind, very good stuff well done Sumit 🙂

  • Ammar Kh says:

    Hi Sumit, Thanks very much for sharing your knowledge. One comment here, i tried to put a non-valid vacation Letter (like R for example). and it counted it as a day off in # of leaves column.

    • Sumit Bansal says:

      Hi Ammar.. Thanks for commenting.. I left it that way as I was not sure if people would want to add their own codes (or randomly key in anything just to mark a leave). But you have a valid point, and we can improve it by highlighting any cell that has something in it. Thanks for bringing this to my notice 🙂

  • April says:

    The leave tracker came in time that i require. And its a great program. Thank you. Are you able to add in the balance after deducting the nos. of leaves taken. I am more concern to track the balance of vacation leaves.

    • Sumit Bansal says:

      Thanks for commenting April.. To get the total number of leaves, use the formula in cell NK8 (and drag for all employees) =COUNTA($B8:$NI8)

      To get the leave balance, subtract this leave count from the total number of leaves. Hope this helps

      • Charlie Kurzawski says:

        Sumit, this is an amazing spreadsheet. Similar to April, I need to track the balance of vacation leaves throughout the course of the calendar year. I cannot find a formula in cell NK8 that you reference above. I just downloaded the leave tracker this morning, so maybe it was an earlier version that contained a formula in NK8?

        Further, in order to track total vacation days taken for the entire year, how would I adjust the # of leaves formula?

      • Ashraf Halai says:

        Hi Sumit,Its wonderful & excellent tool. After searching many templates on website I found this useful. A question how can i amend count formula in cell NK8 to exclude holidays & weekends.(I know it can be manually done but not entering any value in this column, just curious to know it there is any formula)

      • Ra says:

        This is really helpful. However, when I added that formula, it treated “h” as full days, giving me a total count of 4 instead of 3.5. Any recommendations? Thanks in advance.

  • yogirajoo says:

    Congrats to all winners !
    Leave tracker is awesome and very useful !! – Yogirajoo

  • Raja says:

    Thanks Sumit, nice.

  • Andrew says:

    I changed the condition for highlighting the days for the leave code – I first defined the list of codes as ‘LeaveCodes’ and then changed the condition to – =IF(ISERROR(VLOOKUP(B8,LeaveCodes,1,FALSE)),FALSE,TRUE), this makes it easier to extend the table of leave codes as you only then need to ensure the name matches the longer list. It could also be used to apply ‘Data Validation’ to all the dates. It is also something that can be used to track other type of calendar events – a lot of work must have gone into this, well done.

    • Sumit Bansal says:

      Thanks for commenting Andrew.. Great idea to create a named range for leave codes. It would make customizing it much easier 🙂

    • Chelsea says:

      Hi, Andrew. Killing myself here trying to follow your comment so I can
      also extend the leave codes. Any chance you could email me a sample
      file? I’d really appreciate it.

    • Chelsea says:

      Sorry. I am at [email protected]

    • nisch says:

      Hi Andrew, I’m trying to follow your comment as I would like to extend the leave codes but I seem to be lost. Can you please email me the worksheet that you have? My email is [email protected] Thanks!

    • hazel says:

      am trying to add the additional leave type for 0.5 value , can you help me please

      • Suzette Marino says:

        I’m trying to do the same, did you get a response for this?

  • Vabs says:

    Congrats to all winners!

  • Mark says:

    Super Stuff! Love the leave tracker.. This is so much better than what I use.. Thank You!

  • Mehar says:

    Yayy!!! I won $25 Amazon Gift Card 🙂 Thank you Sumit….Look forward to learning more new interesting stuff on TrumpExcel

  • gehad hamed says:

    Could you please change the weekend days to be Friday and Saturday ?

    • Sumit Bansal says:

      Thanks for commenting.. It can be done by tweaking the weekday() formula, where the second argument is 2. You can change it to 1 and it will work.

      • gehad hamed says:

        i try t but the dates became amended for 1 day !!

  • Rose says:

    The leave tracker is one of the awesome-st thing i have seen! congratulations to the winners!

    • Sumit Bansal says:

      Thanks Rose.. Glad you liked it 🙂

      • Rodolfo Valdes R says:

        Hi Submit, Will you be releasing a 2016 version soon?

        • Sumit Bansal says:

          Hello Rodolfo.. I have updated the template for 2016. You can download it now

          • George says:

            Thanks for this template, I have a question. How would list the counts for vacation days and sick days separate instead of having just the total of all days?

          • Ashesh Jain says:

            Hi Sumit, i need to change the standard year to Jan-December year. Can you guide on how to do it?

          • Kathryn Kitchener-Pope says:

            I am also experiencing this issue – is there a fix?

          • Fam says:

            Did you receive a recommendation for correction?

          • Megan Estes says:

            Same problem here. Everything drags over into the other months. If I mark April 3rd as a V for Vacation day then the 3rd of every month becomes a vacation day for that employee. If I delete one, they all delete.

          • Sumit Bansal says:

            Hello Megan.. I believe you are using the value in cell A1 to change the month. Cell A1 need to be set only once, and then use the scroll bar to change the month.

          • Sumit Bansal says:

            Hello Fam..To change the month, use the scroll bar and not the value in cell A1.

          • Fam says:

            Thank you so much for the reply! I was unclear with my question. I actually have two. 1). 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?
            2). How can I view multiple months at a time? I need to see 6 months in advance all at once for schedule comparison. I can unhide the columns, however, I then lose the dates at the top.
            This is masterful! Thank you for the creation, as once I have it completely tweaked, it will make tracking so easy!!!

          • Ashish Bhatt says:

            You will need to add or amend the formula to do so

          • erzon says:

            is it also possible to link the A1 value to scrollbar? for easy access

          • Sumit Bansal says:

            A1 is not to be used to change the months. For that you need to use scroll bar. A1 is to be used to specify the calendar year. SO if you want the year from April-march, then you would have 4 in cell A1.

          • erzon says:

            Hi Sumit I enjoy your Leave Tracker.. but I have a question. Is it possible to include a summary Sheet where in you can see all the Employee and the Date they tag as Leave in that summary for whole year?

          • ABC says:

            The scroll bar is not user friendly as it changes at random. Also is there an easier way to view a single person’s vacation details?

          • Sumit Bansal says:

            Hello Ashesh.. To change the month, use the scroll bar and not the value in cell A1.

          • Tom says:

            hello. after adjusting the holiday list, the tracker color has not changed

          • Ashish Bhatt says:

            I am also facing this problem. Did u get a solution to this?

          • edith says:

            same here, is there any solution for this case?

          • Onita Fernandes says:

            Is there a possibility to add the holidays too during vacation?

      • Prabhath MP says:

        Thanks so much for this wonderful template! 🙂

      • mitra says:

        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 2017 I
        could not use the template you added additional column in version
        2016

        • Deepika Lohani says:

          Hi… did you by any chance find the way to add employee ID. Please share if you’ve got the solution

      • Je says:

        Hi, I’m trying to download the leave tracker, it just shows up downloaded like a zip file and when I open zip file, no spreadsheet. Not sure what I am doin wrong. Is it possible for the spreadsheet tracker to emailed to me

      • JJ Ung says:

        how to set working day for specific employee,because they non working day is not the same..hope u will help me

      • Yonas Misganaw says:

        hi i am wondering how i can do this template all by myself and it is
        been very helpful without too much effort still i want to understand it
        fully in spite of plenty try i could not understand it fully is there
        are guide i have to be using to help me make one of my own.

  • >