Last week I got my 2000th email subscriber. To celebrate this milestone, I promised to give away 3 Amazon gift cards ($25 each) and 3 excel training course subscriptions to people who comment. I got a total of 36 comments (from 34 people) and now it’s time to walk the talk.
Here are the winners:
- Sergei gets a $25 Amazon Giftcard.
- Mehar gets a $25 Amazon Giftcard.
- Sachin gets a $25 Amazon Giftcard.
- Jam gets 1-year access to my Excel Course.
- Lookupkim gets 1-year access to my Excel Course.
- My Office’s Excel Geek gets 1-year access to my Excel Course.
Congratulations to all the winners! I love you all 🙂 I will soon send you an email on how you can claim your giveaway.
WAIT! – That’s not it!
It was lovely hearing from you all and I wish I could give away more. So I have decided to top up the giveaway with 2 additional Excel course subscriptions. I, along with my wife, went through all the comments many times, and we decided to delight 2 additional commenters:
- Jim gets 1-year access to my Excel Course.
- gets 1-year access to my Excel Course.
Congratulations!!! Check your inbox for awesomeness!
Now coming back to business as usual, today I am here to share an Excel Leave Tracker template.
One of my team-mates has the onus of creating a leave tracker for the entire team at the end of each month. Till now, she used a simple Gantt chart but wanted something better.
And Poof! Here it is.
Excel Leave Tracker Template
This Excel Leave Tracker template can be used to record and monitor employee leaves for a year.
How this Excel Leave Tracker Works?
- This tracker 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 2016 as the starting month.
- Note: The value in cell A1 is to change the time period of the leave tracker only. To change months while recording leaves, use the scroll bar at the bottom (as explained below).
- Use the scroll bar to change months (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 only the selected month and hides all the other months.
- You can select the working days and non-working days (Weekends). There is a drop down at the right of the leave tracker where you can specify the working days by selecting Yes and non-working days (or Weekends) by selecting No.
- As soon as you specify the non-working days, those weekdays get highlighted in gray color in the leave tracker.
- You can update the holiday list in the worksheet named “Holiday List”. It will automatically be reflected in the tracker by highlighting those days in Orange color.
- To enter the leave record for employees, use the relevant codes based on the leave type (you can customize these leave codes).
- If you want to mark a day as half day, enter the leave code H. For all other leave codes, a full day leave is counted.
- 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 leaves for the entire year. 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.
- Columns NL tp NP 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.
I have created this template for 10 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.
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.
Is this 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 🙂