Thanks to everyone for the positive feedback and support. An improved version (with leave balance calculations) of the template is available at indzara.com/product/employee-leave-manager/ Based on popular demand, I have created a video course that details every step in building this template. courses.indzara.com/p/vacation-attendance-tracker-dashboard-in-microsoft-excel Please let me know if there are any questions. Best wishes.
Thanks so much for taking the time to make this template! It is extremely helpful as i learn more about excel functionality. For my agency, leave taken through a weekend is still counted as leave days. Is there a way that i can have Sat/Sun still highlight as weekends for quick visual reference when looking at the Dashboard, but still have them code black to indicate leave and count toward leave total if those dates are in the leave period taken? Thanks so much!
Thank you for using our template and sharing your valuable feedback. It involves some changes in the calendar formulas, where the weekend need to be checked after checking for the leaves in the IF condition hierarchy. We take customization projects for a fee. Please write to us at the below link to for estimation: support.indzara.com/support/tickets/new Best wishes.
Thank you for showing interest in our template. You can enter more leaves one after another for same employee or different employee with different leave types for the same month. If you want to add two leave types for same date and for the same employee, like half day sick and half day approved leave, then the feature is available in our premium version of the template. Following is the link to the same. indzara.com/product/employee-leave-manager/ Best wishes.
You are welcome and thank you for sharing your valuable feedback. Calculating leave balance involves some complicated logics and the same feature is available in our Premium version of Leave Manager template. Following is the link to the same for quick reference. indzara.com/product/employee-leave-manager/ Best wishes.
You are welcome. Thanks for feedback. Best wishes.
3 года назад+1
thank you. i didnt look your website yet but did you cover another issue of OVERTIME calculations ? another problem is (for me) my employees have different weekend holidays , some have saturday some monday , some thursday ....
You are welcome. Thank you for watching our video and please do check our website www.indzara.com. This template cannot be used for overtime calculation. Requesting to check our timesheet manager, which has the overtime calculation and its steps. Following is the link to the product: indzara.com/product/timesheets-manager-excel-template/ However, currently we do not have a template to manage rotational week off but you can set the template as no week off's and enter week off separately as a leave in employee leave tracker. Best wishes.
3 года назад
@@indzara thank your reply. i m already watch google sheet version of your template. for different week offs maybe i group my employees and assign weekoff days to them. thank you, i will follow your great works.
You are welcome and thank you for your appreciation. We have a leave manager template, where you can have 12 leave types and more. The following is the link of our Leave Manager template: indzara.com/product/employee-leave-manager/ Best wishes.
Thank you. An improved version (with leave balance calculations) and 12 leave types of the template is available at indzara.com/product/employee-leave-manager/ I have also created a video course that details every step in building this template. courses.indzara.com/p/vacation-attendance-tracker-dashboard-in-microsoft-excel Please let me know if there are any questions Best wishes.
Hi, Thank you for the complement and thank you for showing interest in our template. If you want more leave types and leave type entitlement, requesting to check our premium template at following link: indzara.com/product/employee-leave-manager/ If you want more option to a leave type like rollover, accrual interval etc., requesting to check our PTO manager template at following link: indzara.com/product/small-business-paid-time-off-manager/ Best wishes.
This is a wonderful template! I have utilized it to create a template to track occurrence points for a training program I run. Its been great so far, but have run into 1 issue... if an individual earns multiple occurrences on a given day, the points do not total. Instead they cancel out. Is there a way to have the tracker show a cumulative of the occurrences earned in a day?
Thank you. In this template, if there are multiple leave types on same day for an employee, it is not supported. In the premium version, indzara.com/product/employee-leave-manager/, that is supported. Only one leave color will appear on the dashboards, but the calculations will accommodate multiple leave types and calculate balance correctly. Please let us know if any questions. Best wishes.
This is an amazing & very complete tracker I have ever seen. It has fulfilled all my requirements except the leave balance summary of all employees in one sheet. Sir can u please help me to do so?
Thank you for sharing your valuable feedback. Our premium version of the template has the requested feature. Following is the link to the template for quick reference: indzara.com/product/hr-excel-templates/employee-leave-tracker-excel-template/ Best wishes.
Thank you for showing interest in our template and you are welcome. You can enter leaves one after another for the same employee. For example, employee 1 takes leave from 1st of Jan to 5th of Jan and another leave on 15th of Jan to 20th of Jan, you can enter 1st Jan to 5th Jan in one line and 15th Jan to 20th Jan in next line. However, please note the sheet will not accept two leave types on same day. For example, 5th of Jan cannot be half day Vacation and half day Unpaid leave. The premium version of the template with the above functionality and more is available in the below link: indzara.com/product/employee-leave-manager/ Best wishes.
Thank you. This really helped me. But there are few things that were not discussed or skipped. Conditional Formatting is not working. I have changed the fill colour but this didn't change the colour of the leave type. Also if I want to add a leave type-"Optional off". I have added formula and it's there in dropdown but is not reflecting in Sheet "Team Dashboard" and "Employee Report". Conditional formatting is also not helping. Please help
Thank you for showing interest in our template. Adding another leave type involves some complex steps. You can take up our course on how to create the template to learn on creating the conditional formatting to a newly added leave type. Following is the link to the course: courses.indzara.com/p/vacation-attendance-tracker-dashboard-in-microsoft-excel Best wishes.
Thanks a lot. Plz clear how u r adding employee 31 and 32 on a different sheets and the same is reflecting on another sheet. Is there several sheets. Like sheet 1 ,sheet 2?
You are welcome. We have formulas in a sheet that can pull information from another sheet. If you download the workbook, you can see all the sheets. Best wishes.
This is super!!! Can you please let me know if there is a way to add more than 5 leave types in the settings tab? For maternity leave for example. I suppose that I will have to update additional sheets so if you can mention what else needs to be updated to enable this it would be great.
Please do the following to add more leave types. 1. Just type sixth leave type in SETTINGS sheet’s Leave type table. 2. In the TEAM DASHBOARD sheet, a. Edit conditional formatting rules. You will see rules for the 5 colors.We need to create a similar rule. The formula for the sixth color is =C8=INDEX(L_LEAVE,6). Apply this to all the cells in the calendar. b. Insert a column between columns AM and AN. This new column is to calculate the sum for sixth leave type for each employee. c. In that new column, we need to enter formula. You can copy formula from cell AM8 and paste in AN8(newly created column). Then drag down the cell to row 37 to copy the formula for all employees. d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types). 3. In the EMPLOYEE REPORT sheet, a. Edit conditional formatting rules. You will see rules for the 5 colors. We need to create a similar rule for the sixth color. Apply this to all the cells in the calendar. b. Insert a column between columns AN and AO. This new column is to calculate the sum for sixth leave type for each month. c. In that new column, we need to enter formula. You can copy formula from cell AN12 and paste in AO12 (newly created column). Then drag down the cell to row 23 to copy the formula for all months. d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types). These steps above should implement one additional leave type. You can repeat the same for more additional leave types as needed. Best wishes. Reply
Hi Indra. it's a great app from you, thanks a lot for making this. By the way, I have a question, how do I make a balance leave of each employee? Like I want to calculate their leave remaining. Thanks
You are welcome. Please see Employee Leave Manager template that calculates balance for each employee. Video: ruclips.net/video/-HmBCtIL4qM/видео.html Product page: indzara.com/product/employee-leave-manager/ Please let me know if any questions. best wishes.
How can I add a field which will show me how many off days left for an employee? I need this field to add 2 days every month so that if someone takes a day-off it subtract these days from employees day-off balance. The tool of amazing, I Love it
I love this template. I just have a little suggestion. My organization usually have its staff having to roll-over their leave till the next year example: Start date: 12-12-2019 End Date 11-01-2020. As observed, this template only runs for a single year. Can you please guide me on what to do or better create something i can work with, i will greatly appreciate a feedback. Thanks
Thanks for your feedback. Please try indzara.com/product/employee-leave-manager/ which supports custom reporting period. It is still limited to 1 year of tracking at a time (can change easily for next year), however the year can start on any date. Example Dec 2019 to Nov 2020. Best wishes.
Thanks for feedback. Glad you find it useful. Please unprotect sheet (password indzara) and then try extending the formulas down to show more employees. I cover step by step instructions for building this template here; courses.indzara.com/p/vacation-attendance-tracker-dashboard-in-microsoft-excel Best wishes.
Hi this was very help for my organization. I just want to small changes in working days od the week. In my organization saturday is a half working day...so how i used it to formulas.pls help with me thid updates
You are welcome. Regarding customization, currently the requested feature is not available in our templates. Hence requesting to contact support@indzara.com for an estimation. Best wishes.
This is a wonderful spreadsheet!! THANK YOU! Can you please tell me how to add holidays for different years? If I continue to update the current holiday rows to the present year (2019) wouldn't that mess up the date for previous years like 2018 and 2017?
You are welcome. Yes, you can continue to add new holiday dates to the same table. Since the dates are different (because the year is different), they will not break anything. the template is designed to use for multiple years. Best wishes.
Had great use of this, but hope you can help me with an issue I have: If I have employees on different public holidays because of multiple countries, but how do I add that in the formula?
Hi, this is really helpful. Thank you for sharing. Is there a way to change the last tab report, what if the company have a fiscal year? Can it be changed?
Thanks for feedback. I am working on a new template that will allow user to change the period start and end dates. It should be published this month (Aug 2019). Best wishes.
You can use it for any year and in fact you can continue to use for many years. We just keep adding data and in the reports, we can select the year for reporting. Please do the following to add more leave types. 1. Just type sixth leave type in SETTINGS sheet’s Leave type table. 2. In the TEAM DASHBOARD sheet, a. Edit conditional formatting rules. You will see rules for the 5 colors.We need to create a similar rule. The formula for the sixth color is =C8=INDEX(L_LEAVE,6). Apply this to all the cells in the calendar. b. Insert a column between columns AM and AN. This new column is to calculate the sum for sixth leave type for each employee. c. In that new column, we need to enter formula. You can copy formula from cell AM8 and paste in AN8(newly created column). Then drag down the cell to row 37 to copy the formula for all employees. d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types).3. In the EMPLOYEE REPORT sheet, a. Edit conditional formatting rules. You will see rules for the 5 colors.We need to create a similar rule for the sixth color. Apply this to all the cells in the calendar. b. Insert a column between columns AN and AO. This new column is to calculate the sum for sixth leave type for each month. c. In that new column, we need to enter formula. You can copy formula from cell AN12 and paste in AO12 (newly created column). Then drag down the cell to row 23 to copy the formula for all months. d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types).These steps above should implement one additional leave type. You can repeat the same for more additional leave types as needed. Please try and let me know if there are any questions. Best wishes.
You are welcome. We have to write some formula to calculate that. You can also try the free vacation planner template indzara.com/2015/09/employee-vacation-planner-excel-template/ which has that information. Best wishes.
Thank you. You can add employee details where employee names are entered. You can add more columns to store such information in that sheet. Best wishes.
Thank you. We have retail inventory management templates. indzara.com/2017/02/free-retail-inventory-management-template/ Are you referring to something else? Best wishes.
Thank you. There are no macros/vba. To learn how to build this template, please sign up for the video course courses.indzara.com/p/vacation-attendance-tracker-dashboard-in-microsoft-excel Best wishes.
Hello sir.First of all many thanks for the generosity of sharing the above.Question: If I want to show on the dashboard-along with the above-the names of the clients my employees are constantly booked to (in Employees tab I ve added a client/task name column of the bookings) what nested function should I add in dashboard and at which part?
You are welcome. Glad to hear it is useful. You can see the formula in cell B8 which brings the employee name. You can use that formula and replace the 'Employee Name' reference with the name of the new column you added Best wishes.
Indzara Thank you for the reply. If I want to keep the B8 cell employee name formula as it is but show my new added one in the calendar along with the colours? (This is what I need to be more clear)
Sorry for the delayed response. What I meant was to add a new column, and then use a formula similar to what we used for employee name (except employee name reference will be replaced with the reference to the new column). Best wishes.
Hi sir, one of my employee joined on 1-9-2021 And his vacation was from 18-4-2022 to 5-5-2022 But in team dashboard it is shoei g not employed And one more thing I have total of 55 employee In leave sheet also it's showing but in team dashboard after 29 names are not shown
Thank you for showing interest in our template. Regarding your concern on leave displayed as not employed, We are unable to replicate the issue in our end, hence please share your sheet to support@indzara.com with sample data of the highlighted issue to check further. Regarding limitation on team dashboard, Following are the steps to increase the limit: 1. Unprotect the sheet using password indzara. support.indzara.com/support/solutions/articles/62000137281-how-to-unprotect-sheets- 2. Select all the formulas in last row. 3. Click the bottom right dot on your selected cells and drag down to expand the formulas for more rows and increase the limit. Best wishes.
Such a beautiful tutorial however i cannot see the data changed in "Team Dashboard - Monthly summary" and "Employee Report-leave details". Kindly help me
Hello Indzara, thank you so much for this wonderful resource. Is there a way to divide the employees into different departments as well as setting up individual accessibility for each department manager? Not sure if that makes sense, but I need to be able to provide management only a report pertaining to their department. Thank you
You are welcome. The template is not designed for that type of use. If it is acceptable, you can maintain a separate file for each department. To make it work for multiple departments with customized permissions will require significant development. Best wishes.
Thanks for the feedback. Please see PTO manager where you can assign leave accrual policy and calculate leave balance. indzara.com/product/small-business-paid-time-off-manager/ Best wishes.
Hello Sir, It is a very good tracker. I want do an small modification. In "TEAM DASHBOARD" tab, can we segregate employee on basis of respective team? Please suggest your view on that, Thanks in advance !
Thank you. We need to make modification to the employee list that gets populated in the dashboard. Currently all employees are listed. We need to edit that formula to bring only employees based on a selection. Best wishes.
Hi Presenter, Appreciate and thanks for sharing this template. It is very helpful as I'm using it already to keep track employee leave status. I just have a question, is that possible to group the employee by department? Mean, I can easily filter the department employee leave status instead of individual employee. It would be great if can do this.
this is great, thank you for sharing! Is there any way to incorporate/link a google form or something for employees to request leave? then, once the approver approves it, the data for that particular employee gets automatically added into this sheet?
I am a project manager and this great for managing my company's employees vacation time, is there a way to have the excel sheet accrue the vacation time? For instance how many days they have to take within the hours they have worked?
Thanks. The PTO accrual calculator indzara.com/2015/09/employee-pto-tracker-free-excel-template/ can track accrual and balance for an employee but accrual is based on Tenure (not Hours worked). There is a place to add adjustments where you can add to or subtract from balance for each employee. Best wishes.
Vacation 1 Vacation - Half Day 0.5 Sick 1 Sick - Half Day 0.5 Off Day 1 Vacation - Q-Day 0.25 Sick - Q-Day 0.25 These are the leave types and values I would like to use
Hi. Can this file be copied and pasted via shared drive? Tried this awhile ago but data didn’t reflect once transferred on a shared file. Also, how to add more leave types? Tried the step by step process based on ur comments below however, the color for leave type 6 was not reflecting even after adding the formulas in the confitional formatting so is on the employee calendar..
Yes, the file can be stored on a shared drive. Please see the entire course for creating this template courses.indzara.com/p/vacation-attendance-tracker-dashboard-in-microsoft-excel Best wishes.
Hello, is there a way to add more than 5 leave types in the settings tab? This is a great tool. My only issue is attendance points fall off after one year. Example: I have an person that was absent one whole day on 03/01/2017, the value is 1. Well on 3/01/2018, the value 1 comes off. Is there a way i can track this? I tried adding 4 more leave leave types and added negative values to them but it does not work... I am not sure about adjusting formulas.
To add more than 5 leave types, we have to modify formulas in the other sheets as well. Can you please explain what 'value 1 comes off' means? You should be able to enter each leave taken by all employees in the table. We can keep adding any number of rows. Best wishes.
I recently bought the "Vacation and attendance tracker dashboard" course, and I've been following the course during the past few days, however, today I wanted to continue the course but now the webpage shows the following message: This page does not exist. Sorry Error 404. What should I do now?
We regret the inconvenience caused. You can go to the below link to view the purchased course: indzara.teachable.com/courses/vacation-attendance-tracker-dashboard-in-microsoft-excel Best wishes.
To learn how to build this template, please sign up for the video course courses.indzara.com/p/vacation-attendance-tracker-dashboard-in-microsoft-excel Best wishes.
You are welcome. Please specify your questions in detail. I will be happy to address. The detailed instructions and the free template are available here: indzara.com/2016/11/employee-leave-vacation-tracker/ Best wishes,
hi sir how are you ? hope u doin well actually i am lookin for the same thing i tried to make changes but i am not into excel alot i have a small supply company i need this same think but i need to add hours my company like i have different hours daily and i have different rates if i want to do this can u help me out ?
Thanks for this fantastic spreadsheet. How can I add a second type of holiday-like field, with a different colour, that updates automatically for every employee?
@@vladuadrian40 Please follow steps used in creating holiday logic. Adding a table to store new holidays, updating formulas in both reports to account for this new holiday, and adding conditional formatting to display the new holiday in a different color. Best wishes.
@@indzara Could you please explain how to update the formulas correctly? What is the correct date function i need to add for this new type of holiday? Let's say I have a new new table called T_HOLS2 and the list L_HOLS2. How do I update the formula? I have the following code: =IFERROR(IF(C$8="";"NA";IF(C$80;C$8>INDEX(T_EMP[DATĂ ÎNCHEIERE];ROW($B9)-ROW($B$8)));"NE";IF(NOT(ISERROR(MATCH(C$8;L_HOLS;0)));"H";IF(INDEX(L_WKNDVAL;WEEKDAY(C$8;1))=1;"WKND";INDEX(T_LEAVE[TIP CONCEDIU];SUMPRODUCT(--(T_LEAVE[NUME ANGAJAT]=$B9);--(T_LEAVE[DATĂ ÎNCEPUT]=C$8);ROW(T_LEAVE[TIP CONCEDIU]))-ROW(T_LEAVE[#Anteturi])))))));"") Where do i need to add the new values and what is the correct syntax for HOLS2?
First of all this is amazing tracker without any failure. I need one help , can I update "half day" leave type to "early going" in hours. And Can I add one more leave type "late coming" in hours. Please advise me how can I do this???
You are welcome. This template is designed to track in days and not hours. You can enter 8 (assuming 8 hours of work every day) as value in Settings for a leave type, and then 4 for another leave type (for example). You have to add a column in Leave sheet as leave tracking is currently set up for days. Then, you have to modify dependent formulas to count in hours and not days. Sorry, it is not a simple single step change. Best wishes.
And, The leave type only up to 5 types. Is that possible to add other leave type. As my company is having more than 5 leave type. Sick Leave Vacation Maternity Leave Paternity Leave Casual Leave Compassionate Work from Home Comp OFF Half Day Leave 1 Half Day Leave 2
Please do the following to add more leave types. 1. Just type sixth leave type in SETTINGS sheet’s Leave type table. 2. In the TEAM DASHBOARD sheet, a. Edit conditional formatting rules. You will see rules for the 5 colors.We need to create a similar rule. The formula for the sixth color is =C8=INDEX(L_LEAVE,6). Apply this to all the cells in the calendar. b. Insert a column between columns AM and AN. This new column is to calculate the sum for sixth leave type for each employee. c. In that new column, we need to enter formula. You can copy formula from cell AM8 and paste in AN8(newly created column). Then drag down the cell to row 37 to copy the formula for all employees. d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types). 3. In the EMPLOYEE REPORT sheet, a. Edit conditional formatting rules. You will see rules for the 5 colors. We need to create a similar rule for the sixth color. Apply this to all the cells in the calendar. b. Insert a column between columns AN and AO. This new column is to calculate the sum for sixth leave type for each month. c. In that new column, we need to enter formula. You can copy formula from cell AN12 and paste in AO12 (newly created column). Then drag down the cell to row 23 to copy the formula for all months. d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types). These steps above should implement one additional leave type. You can repeat the same for more additional leave types as needed. Please try and email contact@indzara.com if there are any questions. Best wishes.
Indzara. Hello. I have watched all your videos. They are great. How you explain etc. so much info and detail. Plus u are actually active on ur chat. I was gonna send an email from you website. But. I’ll ask you here as there might be others in my situation. I am working with a small company and I’m in CA. I am I charge of workers’ compensation. Everything is done old school for tracking. I saw your attendance tracker. That would work but it would need to be edited to fit rules and regs. If you are able to help please let me know thanks
Thank you for the kind words. The Student Attendance Register template indzara.com/product/student-attendance-register-excel-template/ has more controls for the user. Please specify what rules and regulations need to be fit. Thanks & Best wishes.
I personally thank you so much for this great work and your tutorial video have really helped me a lot. can you please provide Protect Sheet Password, thanks once again. i am waiting your valuable reply
Hi! Thanks a lot for your outstanding video! Question: if our company's payroll reference year is not calendar based but rather from May 1st to April 30th, is there a way I can modify the reference year in order to obtain employee reports that would match our year of reference? Thank you in advance!
You are welcome. Changing the cycle requires a few steps. 1. Unprotect the sheet 3. In column B, where month names are, we have to edit the formulas. You can just type May 1, 2016 in cell B12, June 1, 2016 in cell B13 and so on. 3. In column A, you will see numbers 1 to 12. Replaces them with 5 to 4. 4. Protect the sheet again. Please try and let me know if there are any questions Best wishes.
Hi ... thank you for this sheet... I want to add column in employee sheet the column called ( Branch)... I have different employee working in different branch ... I need to know the employees that are taking vacation in that month in the one branch
Question: What if an employee has multiple leave in a month? but the dates area not consecutive... say for example, January 1, 2016 and January 8, 2016 and January 16, 2016, and January 20, 2016... How can I enter it?
hi indzara.Can you enter multiple leave dates for one employee at the LEAVE SHEET. if that is correct i don't understand why i get negative for worked days in the team dashboard as well as the employee report.i need feedback please.thanks
Can you please check if you have entered same leave for an employee multiple times in the Leave Sheet? for example, Employee A from 1-Nov to 10-Nov, then in next row Employee A from 1-Nov to 5-Nov, ..etc. Best wishes.
Hi ind zara, I would like to get help on how I can input existing number of leaves and off-in-lieus for each individual employee in the employee sheet so that it can calculate within the team dashboard and employee report on how many days they have balance for their leaves and off-in-lieus. I would like to calculate it only using 2 different leave types. Will greatly appreciate your help as I have little knowledge in Excel.
To calculate balance against accrued PTO, please review the PTO Manager template indzara.com/product/small-business-paid-time-off-manager/ Best wishes.
Please do the following to add more leave types. 1. Just type sixth leave type in SETTINGS sheet’s Leave type table. 2. In the TEAM DASHBOARD sheet, a. Edit conditional formatting rules. You will see rules for the 5 colors.We need to create a similar rule. The formula for the sixth color is =C8=INDEX(L_LEAVE,6). Apply this to all the cells in the calendar. b. Insert a column between columns AM and AN. This new column is to calculate the sum for sixth leave type for each employee. c. In that new column, we need to enter formula. You can copy formula from cell AM8 and paste in AN8(newly created column). Then drag down the cell to row 37 to copy the formula for all employees. d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types). 3. In the EMPLOYEE REPORT sheet, a. Edit conditional formatting rules. You will see rules for the 5 colors. We need to create a similar rule for the sixth color. Apply this to all the cells in the calendar. b. Insert a column between columns AN and AO. This new column is to calculate the sum for sixth leave type for each month. c. In that new column, we need to enter formula. You can copy formula from cell AN12 and paste in AO12 (newly created column). Then drag down the cell to row 23 to copy the formula for all months. d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types). These steps above should implement one additional leave type. You can repeat the same for more additional leave types as needed. Best wishes.
We regret the inconvenience caused. There only two possible reason for this issue. 1. Data must be entered outside the table. 2. Accidently the Holiday table is deleted. Requesting to share your sheet to support@indzara.com to check further. Best wishes.
Lovely Video! Excuse me for butting in, I would love your initial thoughts. Have you heard about - Weydaniel Organized Dominator (probably on Google)? It is an awesome one off product for getting 7000 plus project management and business templates without the headache. Ive heard some extraordinary things about it and my close friend Aubrey at last got cool success with it.
Please do the following to add more leave types. 1. Just type sixth leave type in SETTINGS sheet’s Leave type table. 2. In the TEAM DASHBOARD sheet, a. Edit conditional formatting rules. You will see rules for the 5 colors.We need to create a similar rule. Apply this to all the cells in the calendar. b. Insert a column between columns AM and AN. This new column is to calculate the sum for sixth leave type for each employee. c. In that new column, we need to enter formula. You can copy formula from cell AM8 and paste in AN8(newly created column). Then drag down the cell to row 37 to copy the formula for all employees. d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types). 3. In the EMPLOYEE REPORT sheet, a. Edit conditional formatting rules. You will see rules for the 5 colors.We need to create a similar rule for the sixth color. Apply this to all the cells in the calendar. b. Insert a column between columns AN and AO. This new column is to calculate the sum for sixth leave type for each month. c. In that new column, we need to enter formula. You can copy formula from cell AN12 and paste in AO12 (newly created column). Then drag down the cell to row 23 to copy the formula for all months. d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types). These steps above should implement one additional leave type. You can repeat the same for more additional leave types as needed. Please try and let me know if there are any questions. Best wishes.
The links to files are available in the blog post. indzara.com/2016/11/employee-leave-vacation-tracker/ Please search for the word 'Download' to find the location. I have also emailed the file. Thanks. Best wishes.
how do i add the formula for team dashboard if the employee is taking halfday leave and i want it to be shown on the calendar table that says "AM" or "PM"?
How can i maintain multiple one day leave in one month? let take exp:- if some is taking leave 2nd feb 2017 and 15 feb 2017 how ll i maintain in this leave tracker? ple help me ASAP.
Please enter each vacation in separate rows. Please add more rows to the table as more vacations are taken, whether it is one person taking multiple vacations or multiple people taking vacation. Best wishes.
How do i make it so, Leave (Vacation) stays as 0 days over the weekends, but if i work (Duties) over a weekend, it shows up as 2 days. Example - i'm on a course for 2 weeks, and have to work weekends. Therefore i want this 'course' to be shown as 14 days and not 10 days.
Thanks. There is no simple way to modify this. We can create a leave type 'Weekend' and assign 0 as value. We would not select any weekdays as weekends in the Settings. But we would enter weekends as leave in the Leave sheet. We would have to do this for each employee separately (separate rows in Leave sheet). Hope this helps. In the future, I will have to incorporate this feature to override weekends as an option. Best wishes.
Thanks, it was more to highlight the weekends (in grey) so they are clear to see. When i tried this manually, and go back/forward a month, it started to highlight different days i.e friday/saturday, thursday/friday etc. I'll try your method above, see if i can work something out.
What is the password to unprotect the sheet in employee Leave Tracker Excel Template. I want to change the sequence of months but it requires password to unprotect the sheet.
You can enter more than 30 employees in the Employees sheet. There is no limit imposed here. However, the Team Dashboard shows only 30 employees. To extend that, follow these steps. For example, to support 35 employees, Unprotect the Team Dashboard sheet with the password indzara Select cells B36:AO36 (this is the row of Employee 29) Drag down for 6 rows as to fill them with the formulas. Now you should see 35 (29+6) employees accounted for. If you need to extend for more employees, follow the same steps but increase the number of rows you are copying. If you print or export to PDF, adjust print settings accordingly to include these new additional employees. Best wishes.
your holidays does not contains 2017 and further and please make a column that show total working hours as per 9 hour/day wise excluding leaves(sick leave, training) in case half day include 4.5 hours for all employee and for each month.
In the Settings sheet, we can enter holidays in the Holidays table. We can enter any year's holidays as per our needs. If I have misunderstood your question, please provide more details. I am not following the specifics of the column you are referring to. Sorry. The template is based on day level leave tracking and not hourly level. It would require a different design. Please let me know if there are any questions. Best wishes.
Give the template like I can we maintain every month without removing. i want for yearly . exp. if i maintain February month i don't want to remove for previous month. Please help
We can create a formula with an IF function to flag if the vacation taken has reached a specific number. We can also use conditional formatting to visually highlight. Best wishes.
Is it possible to modify to include: a. different number of leave available days per employee (for example, in Japan, we start each employee with 15 days of annual leave - starting only after they complete the probation period - then prorated for months left in the year - and then, on their anniversary date with the company one day is added each year until they reach the maximum of 20 days (this is the labor law rule in Japan). b. Example: Employee starts June 1, 2019. Probation ends August 31. Balance of the year Employee has 5 days vacation. Starting Jan. 1, 2020, they have 15 days, but on June 1, 2020 that increases by 1 day. c. Carry forward unused leave. Example: Employee 1 above used all vacation days in 2019 (5 days), but in 2020 only uses 10 days. The remaining 6 days can be carried forward to the next year, so in 2021 the start the years with 17 days (1 more day added at anniversary date) + 6 days = 23 days.
Thanks for your interest. Please try the PTO Calculator template for one employee. indzara.com/2015/09/employee-pto-tracker-free-excel-template/ This allows setting probationary period, accrual rates based on employee tenures and rollover policy. If it works, the premium template PTO Manager indzara.com/product/small-business-paid-time-off-manager/ supports multiple employees. Please let me know if I am missing anything. Best wishes.
There is a video course available on how to build this entire template: courses.indzara.com/p/vacation-attendance-tracker-dashboard-in-microsoft-excel I will also be publishing a new template that will support 12 types of leave along with many other enhancements. It should be available later this month (Aug 2019). Best wishes.
The published template shows only one month for all employees together and the whole year for one employee at a time. We have to develop if we want to show the annual total for all employees in one sheet. Please let me know if there are any questions. Best wishes.
In the current version, looking in Team Dashboard for a specific date column would inform us those who are on leave. There is no separate report available for that. Please let me know if any questions. Best wishes.
Sorry, we don't have that feature yet. You can add a column for department and write formulas to bring them to the dashboard. But filtering by department would need more editing of formulas. We will consider adding it to next version. Best wishes.
Thanks to everyone for the positive feedback and support.
An improved version (with leave balance calculations) of the template is available at indzara.com/product/employee-leave-manager/
Based on popular demand, I have created a video course that details every step in building this template. courses.indzara.com/p/vacation-attendance-tracker-dashboard-in-microsoft-excel
Please let me know if there are any questions.
Best wishes.
Can you please add one more option to find out the names of employees on leave as on date. ( Leave Report )
One of the most simplest easy to manage leave schedules. Thanks a mil.
Absolutely brilliant and exactly what I have been looking for. Thanks.
Thank you for sharing your valuable feedback and you are welcome.
Best wishes.
I personally thank you(Indzara) so much for this great work, and your tutorial video have really helped me a lot. Once again i appreciate.
You are very welcome! Glad to hear.
best wishes.
That's incredible work, and I really appreciate it!
Thank you for sharing your valuable feedback.
Best wishes.
Thanks so much for taking the time to make this template! It is extremely helpful as i learn more about excel functionality. For my agency, leave taken through a weekend is still counted as leave days. Is there a way that i can have Sat/Sun still highlight as weekends for quick visual reference when looking at the Dashboard, but still have them code black to indicate leave and count toward leave total if those dates are in the leave period taken? Thanks so much!
Thank you for using our template and sharing your valuable feedback.
It involves some changes in the calendar formulas, where the weekend need to be checked after checking for the leaves in the IF condition hierarchy.
We take customization projects for a fee. Please write to us at the below link to for estimation:
support.indzara.com/support/tickets/new
Best wishes.
This is amazing. Thank you so much Indzara. You have no idea what a blessing you. Again, thank you.
You are welcome. Thanks for feedback.
Best wishes.
Incredible video.
One question, How can I put multiple types of leave in a month?
Thank you for showing interest in our template.
You can enter more leaves one after another for same employee or different employee with different leave types for the same month.
If you want to add two leave types for same date and for the same employee, like half day sick and half day approved leave, then the feature is available in our premium version of the template. Following is the link to the same.
indzara.com/product/employee-leave-manager/
Best wishes.
Waooo
excellent
thanks very much
your share Knowledge
thanks you very much
good ebout complate learn
Thanks for your feedback. Happy New Year!
Best wishes.
Hi, thank you for this amazing sheet! but how is it possible to add accumulated leave balances for example 60 days from 2020 and 2021
You are welcome and thank you for sharing your valuable feedback.
Calculating leave balance involves some complicated logics and the same feature is available in our Premium version of Leave Manager template. Following is the link to the same for quick reference.
indzara.com/product/employee-leave-manager/
Best wishes.
Awesome spreadsheet. Thanks for leaving it open.
You are welcome. Thanks for feedback. Best wishes.
thank you. i didnt look your website yet but did you cover another issue of OVERTIME calculations ? another problem is (for me) my employees have different weekend holidays , some have saturday some monday , some thursday ....
You are welcome. Thank you for watching our video and please do check our website www.indzara.com.
This template cannot be used for overtime calculation. Requesting to check our timesheet manager, which has the overtime calculation and its steps. Following is the link to the product:
indzara.com/product/timesheets-manager-excel-template/
However, currently we do not have a template to manage rotational week off but you can set the template as no week off's and enter week off separately as a leave in employee leave tracker.
Best wishes.
@@indzara thank your reply. i m already watch google sheet version of your template. for different week offs maybe i group my employees and assign weekoff days to them. thank you, i will follow your great works.
Thank you so much Indzara. I found this very very useful. I really appreciate. How can add more leave types? kindly suggest. Thank you
You are welcome and thank you for your appreciation.
We have a leave manager template, where you can have 12 leave types and more. The following is the link of our Leave Manager template:
indzara.com/product/employee-leave-manager/
Best wishes.
Very helpful , please also show us How to add more leave types?
Thank you.
An improved version (with leave balance calculations) and 12 leave types of the template is available at indzara.com/product/employee-leave-manager/
I have also created a video course that details every step in building this template. courses.indzara.com/p/vacation-attendance-tracker-dashboard-in-microsoft-excel
Please let me know if there are any questions
Best wishes.
This is great! But is there anyway to add more catergories to the employee leave?
Hi,
Thank you for the complement and thank you for showing interest in our template.
If you want more leave types and leave type entitlement, requesting to check our premium template at following link:
indzara.com/product/employee-leave-manager/
If you want more option to a leave type like rollover, accrual interval etc., requesting to check our PTO manager template at following link:
indzara.com/product/small-business-paid-time-off-manager/
Best wishes.
This is a wonderful template! I have utilized it to create a template to track occurrence points for a training program I run. Its been great so far, but have run into 1 issue... if an individual earns multiple occurrences on a given day, the points do not total. Instead they cancel out. Is there a way to have the tracker show a cumulative of the occurrences earned in a day?
Thank you.
In this template, if there are multiple leave types on same day for an employee, it is not supported. In the premium version, indzara.com/product/employee-leave-manager/, that is supported. Only one leave color will appear on the dashboards, but the calculations will accommodate multiple leave types and calculate balance correctly.
Please let us know if any questions.
Best wishes.
This is an amazing & very complete tracker I have ever seen. It has fulfilled all my requirements except the leave balance summary of all employees in one sheet. Sir can u please help me to do so?
Thank you for sharing your valuable feedback.
Our premium version of the template has the requested feature. Following is the link to the template for quick reference:
indzara.com/product/hr-excel-templates/employee-leave-tracker-excel-template/
Best wishes.
This is very helpful! thank you and more power to you career and channel. God Bless!
Thanks for feedback. You are welcome.
Best wishes.
thank you so much for this.
can you please tell me if a employee took 2 holidays in two separate weeks of same month how to track them in same excel!
Thank you for showing interest in our template and you are welcome.
You can enter leaves one after another for the same employee. For example, employee 1 takes leave from 1st of Jan to 5th of Jan and another leave on 15th of Jan to 20th of Jan, you can enter 1st Jan to 5th Jan in one line and 15th Jan to 20th Jan in next line.
However, please note the sheet will not accept two leave types on same day.
For example, 5th of Jan cannot be half day Vacation and half day Unpaid leave.
The premium version of the template with the above functionality and more is available in the below link:
indzara.com/product/employee-leave-manager/
Best wishes.
Excellent...
Thank you.
Best wishes.
Thank you for this!
You are welcome and thank you for using our template.
Best wishes.
Thank you. This really helped me. But there are few things that were not discussed or skipped. Conditional Formatting is not working. I have changed the fill colour but this didn't change the colour of the leave type. Also if I want to add a leave type-"Optional off". I have added formula and it's there in dropdown but is not reflecting in Sheet "Team Dashboard" and "Employee Report". Conditional formatting is also not helping. Please help
Thank you for showing interest in our template.
Adding another leave type involves some complex steps. You can take up our course on how to create the template to learn on creating the conditional formatting to a newly added leave type.
Following is the link to the course:
courses.indzara.com/p/vacation-attendance-tracker-dashboard-in-microsoft-excel
Best wishes.
Many thanks for the valuable product....really appreciated. Hope this template will help me a lot.
You are very welcome. Thanks for the feedback. Best wishes.
Thanks a lot. Plz clear how u r adding employee 31 and 32 on a different sheets and the same is reflecting on another sheet. Is there several sheets. Like sheet 1 ,sheet 2?
You are welcome. We have formulas in a sheet that can pull information from another sheet. If you download the workbook, you can see all the sheets.
Best wishes.
This is super!!! Can you please let me know if there is a way to add more than 5 leave types in the settings tab? For maternity leave for example. I suppose that I will have to update additional sheets so if you can mention what else needs to be updated to enable this it would be great.
Please do the following to add more leave types.
1. Just type sixth leave type in SETTINGS sheet’s Leave type table.
2. In the TEAM DASHBOARD sheet,
a. Edit conditional formatting rules. You will see rules for the 5 colors.We need to create a similar rule. The formula for the sixth color is =C8=INDEX(L_LEAVE,6).
Apply this to all the cells in the calendar.
b. Insert a column between columns AM and AN. This new column is to calculate the sum for sixth leave type for each employee.
c. In that new column, we need to enter formula. You can copy formula from cell AM8 and paste in AN8(newly created column). Then drag down the cell to row 37 to copy the formula for all employees.
d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types).
3. In the EMPLOYEE REPORT sheet,
a. Edit conditional formatting rules. You will see rules for the 5 colors. We need to create a similar rule for the sixth color. Apply this to all the cells in the calendar.
b. Insert a column between columns AN and AO. This new column is to calculate the sum for sixth leave type for each month.
c. In that new column, we need to enter formula. You can copy formula from cell AN12 and paste in AO12 (newly created column). Then drag down the cell to row 23 to copy the formula for all months.
d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types).
These steps above should implement one additional leave type. You can repeat the same for more additional leave types as needed.
Best wishes.
Reply
Hi Indra. it's a great app from you, thanks a lot for making this. By the way, I have a question, how do I make a balance leave of each employee? Like I want to calculate their leave remaining. Thanks
You are welcome.
Please see Employee Leave Manager template that calculates balance for each employee. Video: ruclips.net/video/-HmBCtIL4qM/видео.html
Product page: indzara.com/product/employee-leave-manager/
Please let me know if any questions.
best wishes.
How can I add a field which will show me how many off days left for an employee? I need this field to add 2 days every month so that if someone takes a day-off it subtract these days from employees day-off balance.
The tool of amazing, I Love it
Please take a look at ruclips.net/video/npvoWK5s5O0/видео.html PTO Calculator template that does accrual automatically and shows balance.
Best wishes.
I love this template. I just have a little suggestion. My organization usually have its staff having to roll-over their leave till the next year example: Start date: 12-12-2019 End Date 11-01-2020. As observed, this template only runs for a single year. Can you please guide me on what to do or better create something i can work with, i will greatly appreciate a feedback. Thanks
Thanks for your feedback.
Please try indzara.com/product/employee-leave-manager/ which supports custom reporting period. It is still limited to 1 year of tracking at a time (can change easily for next year), however the year can start on any date. Example Dec 2019 to Nov 2020.
Best wishes.
@@indzara Dear sir my organization fiscal year July to June so how can i used this templat!. This is very good temples for us .
How can I get the Team Dashboard to show more employees? - BTW Great leave tracker!
Thanks for feedback. Glad you find it useful.
Please unprotect sheet (password indzara) and then try extending the formulas down to show more employees.
I cover step by step instructions for building this template here; courses.indzara.com/p/vacation-attendance-tracker-dashboard-in-microsoft-excel
Best wishes.
Hi this was very help for my organization. I just want to small changes in working days od the week.
In my organization saturday is a half working day...so how i used it to formulas.pls help with me thid updates
You are welcome.
Regarding customization, currently the requested feature is not available in our templates. Hence requesting to contact support@indzara.com for an estimation.
Best wishes.
This is a wonderful spreadsheet!! THANK YOU! Can you please tell me how to add holidays for different years? If I continue to update the current holiday rows to the present year (2019) wouldn't that mess up the date for previous years like 2018 and 2017?
You are welcome.
Yes, you can continue to add new holiday dates to the same table. Since the dates are different (because the year is different), they will not break anything. the template is designed to use for multiple years.
Best wishes.
Had great use of this, but hope you can help me with an issue I have: If I have employees on different public holidays because of multiple countries, but how do I add that in the formula?
Hi, this is really helpful. Thank you for sharing. Is there a way to change the last tab report, what if the company have a fiscal year? Can it be changed?
Thanks for feedback.
I am working on a new template that will allow user to change the period start and end dates. It should be published this month (Aug 2019).
Best wishes.
@@indzara Thank you so much! Looking forward to it.
@@SirChiefUK Please try the new template at indzara.com/product/employee-leave-manager/
Best wishes,
This is great ty. I see this covers 2017 how do you you change it to 2018 - can you add leave types fro example maternity
You can use it for any year and in fact you can continue to use for many years. We just keep adding data and in the reports, we can select the year for reporting.
Please do the following to add more leave types.
1. Just type sixth leave type in SETTINGS sheet’s Leave type table.
2. In the TEAM DASHBOARD sheet,
a. Edit conditional formatting rules. You will see rules for the 5 colors.We need to create a similar rule. The formula for the sixth color is =C8=INDEX(L_LEAVE,6).
Apply this to all the cells in the calendar.
b. Insert a column between columns AM and AN. This new column is to calculate the sum for sixth leave type for each employee.
c. In that new column, we need to enter formula. You can copy formula from cell AM8 and paste in AN8(newly created column). Then drag down the cell to row 37 to copy the formula for all employees.
d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types).3. In the EMPLOYEE REPORT sheet,
a. Edit conditional formatting rules. You will see rules for the 5 colors.We need to create a similar rule for the sixth color. Apply this to all the cells in the calendar.
b. Insert a column between columns AN and AO. This new column is to calculate the sum for sixth leave type for each month.
c. In that new column, we need to enter formula. You can copy formula from cell AN12 and paste in AO12 (newly created column). Then drag down the cell to row 23 to copy the formula for all months.
d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types).These steps above should implement one additional leave type. You can repeat the same for more additional leave types as needed.
Please try and let me know if there are any questions.
Best wishes.
Such amazing excel tutorial, thanks! Please do you know why an employee's leave is showing 351 days?
Thank you. Please email file to contact@indzara.com. We will be glad to review and address your question.
Thanks & Best wishes.
Many thanks for this great planner.. I have a question. How i count how many staff on leave each day?
You are welcome. We have to write some formula to calculate that.
You can also try the free vacation planner template indzara.com/2015/09/employee-vacation-planner-excel-template/ which has that information.
Best wishes.
Very Good Sheet, where to put the employees Data for eg. Emp ID, DOB, Designation, Contact No. Etc...
Thank you. You can add employee details where employee names are entered. You can add more columns to store such information in that sheet.
Best wishes.
Excellent.. Do you have some formates regarding Office inventory management..?
Thank you. We have retail inventory management templates. indzara.com/2017/02/free-retail-inventory-management-template/
Are you referring to something else?
Best wishes.
How do I add more leave types and get it to show on the team dashboard?
Hi, this video is very helpful. May I know if there is any way that I can understand the codes that you had made?
Thank you. There are no macros/vba.
To learn how to build this template, please sign up for the video course courses.indzara.com/p/vacation-attendance-tracker-dashboard-in-microsoft-excel
Best wishes.
How do you add additional leave types and get them to show up on the other pages?
Hello sir.First of all many thanks for the generosity of sharing the above.Question: If I want to show on the dashboard-along with the above-the names of the clients my employees are constantly booked to (in Employees tab I ve added a client/task name column of the bookings) what nested function should I add in dashboard and at which part?
You are welcome. Glad to hear it is useful.
You can see the formula in cell B8 which brings the employee name. You can use that formula and replace the 'Employee Name' reference with the name of the new column you added
Best wishes.
Indzara Thank you for the reply. If I want to keep the B8 cell employee name formula as it is but show my new added one in the calendar along with the colours? (This is what I need to be more clear)
Sorry for the delayed response.
What I meant was to add a new column, and then use a formula similar to what we used for employee name (except employee name reference will be replaced with the reference to the new column).
Best wishes.
hello sir , ur videos are very helpful to me
Thank you very much. Glad to hear.
Hi sir, one of my employee joined on 1-9-2021
And his vacation was from 18-4-2022 to 5-5-2022
But in team dashboard it is shoei g not employed
And one more thing I have total of 55 employee
In leave sheet also it's showing but in team dashboard after 29 names are not shown
Thank you for showing interest in our template.
Regarding your concern on leave displayed as not employed,
We are unable to replicate the issue in our end, hence please share your sheet to support@indzara.com with sample data of the highlighted issue to check further.
Regarding limitation on team dashboard,
Following are the steps to increase the limit:
1. Unprotect the sheet using password indzara.
support.indzara.com/support/solutions/articles/62000137281-how-to-unprotect-sheets-
2. Select all the formulas in last row.
3. Click the bottom right dot on your selected cells and drag down to expand the formulas for more rows and increase the limit.
Best wishes.
Such a beautiful tutorial however i cannot see the data changed in "Team Dashboard - Monthly summary" and "Employee Report-leave details". Kindly help me
Thanks. Please email file to contact@indzara.com and specify the details.
Best wishes.
Really an amazing work , but what if we need week ends to be determined as agent level Separately as it a rotation shifts ?
Thanks. Sorry, currently it requires all the employees to have same weekend dates.
Best wishes.
Hello Indzara, thank you so much for this wonderful resource. Is there a way to divide the employees into different departments as well as setting up individual accessibility for each department manager? Not sure if that makes sense, but I need to be able to provide management only a report pertaining to their department. Thank you
You are welcome. The template is not designed for that type of use. If it is acceptable, you can maintain a separate file for each department. To make it work for multiple departments with customized permissions will require significant development.
Best wishes.
hi, this is a great leave management using excel. i have a question how can i put leave credit control?
Thanks for the feedback. Please see PTO manager where you can assign leave accrual policy and calculate leave balance. indzara.com/product/small-business-paid-time-off-manager/ Best wishes.
Hello Sir, It is a very good tracker. I want do an small modification. In "TEAM DASHBOARD" tab, can we segregate employee on basis of respective team? Please suggest your view on that, Thanks in advance !
Thank you.
We need to make modification to the employee list that gets populated in the dashboard. Currently all employees are listed. We need to edit that formula to bring only employees based on a selection.
Best wishes.
@@indzara Can you help to do modification in forumala?
I am currently tied up with other projects. I will try to do it when I find time.
Thanks & Best wishes.
Hi Presenter,
Appreciate and thanks for sharing this template. It is very helpful as I'm using it already to keep track employee leave status.
I just have a question, is that possible to group the employee by department? Mean, I can easily filter the department employee leave status instead of individual employee. It would be great if can do this.
Thanks for feedback. We will plan to incorporate it in next version. Best wishes.
this is great, thank you for sharing! Is there any way to incorporate/link a google form or something for employees to request leave? then, once the approver approves it, the data for that particular employee gets automatically added into this sheet?
You are welcome. Thanks for the suggestions. We don't have such a feature yet though.
Best wishes.
I am a project manager and this great for managing my company's employees vacation time, is there a way to have the excel sheet accrue the vacation time? For instance how many days they have to take within the hours they have worked?
Thanks. The PTO accrual calculator indzara.com/2015/09/employee-pto-tracker-free-excel-template/ can track accrual and balance for an employee but accrual is based on Tenure (not Hours worked). There is a place to add adjustments where you can add to or subtract from balance for each employee.
Best wishes.
Vacation 1
Vacation - Half Day 0.5
Sick 1
Sick - Half Day 0.5
Off Day 1
Vacation - Q-Day 0.25
Sick - Q-Day 0.25
These are the leave types and values I would like to use
Thanks so much,appreciate.
You are very welcome
Hi. Can this file be copied and pasted via shared drive? Tried this awhile ago but data didn’t reflect once transferred on a shared file. Also, how to add more leave types? Tried the step by step process based on ur comments below however, the color for leave type 6 was not reflecting even after adding the formulas in the confitional formatting so is on the employee calendar..
Yes, the file can be stored on a shared drive.
Please see the entire course for creating this template courses.indzara.com/p/vacation-attendance-tracker-dashboard-in-microsoft-excel
Best wishes.
Hello, is there a way to add more than 5 leave types in the settings tab? This is a great tool. My only issue is attendance points fall off after one year. Example: I have an person that was absent one whole day on 03/01/2017, the value is 1. Well on 3/01/2018, the value 1 comes off. Is there a way i can track this? I tried adding 4 more leave leave types and added negative values to them but it does not work... I am not sure about adjusting formulas.
To add more than 5 leave types, we have to modify formulas in the other sheets as well.
Can you please explain what 'value 1 comes off' means? You should be able to enter each leave taken by all employees in the table. We can keep adding any number of rows.
Best wishes.
I recently bought the "Vacation and attendance tracker dashboard" course, and I've been following the course during the past few days, however, today I wanted to continue the course but now the webpage shows the following message: This page does not exist. Sorry Error 404. What should I do now?
We regret the inconvenience caused.
You can go to the below link to view the purchased course:
indzara.teachable.com/courses/vacation-attendance-tracker-dashboard-in-microsoft-excel
Best wishes.
Hi can you please share the detail video of how this template was made. That will really help us.
Thanks. I will try to post tutorials on the template.
Best wishes.
To learn how to build this template, please sign up for the video course courses.indzara.com/p/vacation-attendance-tracker-dashboard-in-microsoft-excel
Best wishes.
If I am new to a team would I enter the employees start date with the company or start date from when I come to the team and begin tracking behavior?
It depends. If employee's term before joining the team does not have to be tracked, then start date will be start date in this team.
Best wishes.
Great job! I just downloaded, and I hope to manage using it truly. May I ask some questions via mail?
Thank you. Sure. please post your questions here or via email. Thanks.
Hi Thank you for making this, actually I used this in my office work but I'm little confused on how the function is working, can anyone help here
You are welcome. Please specify your questions in detail. I will be happy to address.
The detailed instructions and the free template are available here: indzara.com/2016/11/employee-leave-vacation-tracker/
Best wishes,
hi sir
how are you ? hope u doin well
actually i am lookin for the same thing i tried to make changes but i am not into excel alot i have a small supply company i need this same think but i need to add hours my company like i have different hours daily and i have different rates if i want to do this can u help me out ?
Thank you.
Can you convert the hour to fractional days?
Best wishes.
Thanks for this fantastic spreadsheet. How can I add a second type of holiday-like field, with a different colour, that updates automatically for every employee?
You are welcome.
Only holiday and weekends apply to every one. Others should be entered individually for each employee.
Best wishes.
@@indzara I know, that's what I am asking, if I can add another field like holiday and weekends that apply automatically.
@@vladuadrian40 Please follow steps used in creating holiday logic. Adding a table to store new holidays, updating formulas in both reports to account for this new holiday, and adding conditional formatting to display the new holiday in a different color.
Best wishes.
@@indzara Could you please explain how to update the formulas correctly? What is the correct date function i need to add for this new type of holiday? Let's say I have a new new table called T_HOLS2 and the list L_HOLS2. How do I update the formula? I have the following code:
=IFERROR(IF(C$8="";"NA";IF(C$80;C$8>INDEX(T_EMP[DATĂ ÎNCHEIERE];ROW($B9)-ROW($B$8)));"NE";IF(NOT(ISERROR(MATCH(C$8;L_HOLS;0)));"H";IF(INDEX(L_WKNDVAL;WEEKDAY(C$8;1))=1;"WKND";INDEX(T_LEAVE[TIP CONCEDIU];SUMPRODUCT(--(T_LEAVE[NUME ANGAJAT]=$B9);--(T_LEAVE[DATĂ ÎNCEPUT]=C$8);ROW(T_LEAVE[TIP CONCEDIU]))-ROW(T_LEAVE[#Anteturi])))))));"")
Where do i need to add the new values and what is the correct syntax for HOLS2?
Any suggestions Indzara?
First of all this is amazing tracker without any failure.
I need one help , can I update "half day" leave type to "early going" in hours.
And Can I add one more leave type "late coming" in hours.
Please advise me how can I do this???
You are welcome.
This template is designed to track in days and not hours.
You can enter 8 (assuming 8 hours of work every day) as value in Settings for a leave type, and then 4 for another leave type (for example).
You have to add a column in Leave sheet as leave tracking is currently set up for days. Then, you have to modify dependent formulas to count in hours and not days. Sorry, it is not a simple single step change.
Best wishes.
And, The leave type only up to 5 types. Is that possible to add other leave type. As my company is having more than 5 leave type.
Sick Leave
Vacation
Maternity Leave
Paternity Leave
Casual Leave
Compassionate
Work from Home
Comp OFF
Half Day Leave 1
Half Day Leave 2
Please do the following to add more leave types.
1. Just type sixth leave type in SETTINGS sheet’s Leave type table.
2. In the TEAM DASHBOARD sheet,
a. Edit conditional formatting rules. You will see rules for the 5 colors.We need to create a similar rule. The formula for the sixth color is =C8=INDEX(L_LEAVE,6).
Apply this to all the cells in the calendar.
b. Insert a column between columns AM and AN. This new column is to calculate the sum for sixth leave type for each employee.
c. In that new column, we need to enter formula. You can copy formula from cell AM8 and paste in AN8(newly created column). Then drag down the cell to row 37 to copy the formula for all employees.
d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types).
3. In the EMPLOYEE REPORT sheet,
a. Edit conditional formatting rules. You will see rules for the 5 colors. We need to create a similar rule for the sixth color. Apply this to all the cells in the calendar.
b. Insert a column between columns AN and AO. This new column is to calculate the sum for sixth leave type for each month.
c. In that new column, we need to enter formula. You can copy formula from cell AN12 and paste in AO12 (newly created column). Then drag down the cell to row 23 to copy the formula for all months.
d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types).
These steps above should implement one additional leave type. You can repeat the same for more additional leave types as needed.
Please try and email contact@indzara.com if there are any questions.
Best wishes.
Thank you for sharing! :)
You are welcome.
Question for you. Is it possible for some employees weekends be Saturday-Sunday and some employees weekend be Sunday-Monday?
Sorry, in the template, all the employees in one file should have same weekends and holidays.
Best wishes.
Wow..... Amazing!
Thank you. Best wishes.
Indzara. Hello. I have watched all your videos. They are great. How you explain etc. so much info and detail. Plus u are actually active on ur chat.
I was gonna send an email from you website. But. I’ll ask you here as there might be others in my situation. I am working with a small company and I’m in CA. I am I charge of workers’ compensation. Everything is done old school for tracking. I saw your attendance tracker. That would work but it would need to be edited to fit rules and regs. If you are able to help please let me know thanks
Thank you for the kind words.
The Student Attendance Register template indzara.com/product/student-attendance-register-excel-template/ has more controls for the user. Please specify what rules and regulations need to be fit.
Thanks & Best wishes.
I personally thank you so much for this great work and your tutorial video have really helped me a lot.
can you please provide Protect Sheet Password, thanks once again. i am waiting your valuable reply
You are welcome. Thanks for your feedback. Please use 'indzara' as password.
Best wishes.
Hi! Thanks a lot for your outstanding video! Question: if our company's payroll reference year is not calendar based but rather from May 1st to April 30th, is there a way I can modify the reference year in order to obtain employee reports that would match our year of reference? Thank you in advance!
You are welcome.
Changing the cycle requires a few steps.
1. Unprotect the sheet
3. In column B, where month names are, we have to edit the formulas. You can just type May 1, 2016 in cell B12, June 1, 2016 in cell B13 and so on.
3. In column A, you will see numbers 1 to 12. Replaces them with 5 to 4.
4. Protect the sheet again.
Please try and let me know if there are any questions
Best wishes.
Hi ... thank you for this sheet... I want to add column in employee sheet the column called ( Branch)... I have different employee working in different branch ... I need to know the employees that are taking vacation in that month in the one branch
You are welcome.
Sorry, that feature is currently not in the template. I am working on a new version that will address this soon.
Best wishes.
@@indzara Thank you so much. I am waiting 😍 , How much it will take ?
You are welcome.
Planning to have within the next 6 weeks.
Best wishes.
@@indzara Thank you so much . I am waiting💃 . I need it ASAP
Question: What if an employee has multiple leave in a month? but the dates area not consecutive... say for example, January 1, 2016 and January 8, 2016 and January 16, 2016, and January 20, 2016... How can I enter it?
Jerson Guadalquiver bro san ka work
Please enter them in 2 separate rows. Best wishes.
hi indzara.Can you enter multiple leave dates for one employee at the LEAVE SHEET. if that is correct i don't understand why i get negative for worked days in the team dashboard as well as the employee report.i need feedback please.thanks
Can you please check if you have entered same leave for an employee multiple times in the Leave Sheet? for example, Employee A from 1-Nov to 10-Nov, then in next row Employee A from 1-Nov to 5-Nov, ..etc.
Best wishes.
I want to learn, how to create daily efficiency tracker for employees, can you help me to do it ?
Thanks for your interest. I am sorry I don't have such a template. Best wishes.
Hi ind zara, I would like to get help on how I can input existing number of leaves and off-in-lieus for each individual employee in the employee sheet so that it can calculate within the team dashboard and employee report on how many days they have balance for their leaves and off-in-lieus. I would like to calculate it only using 2 different leave types. Will greatly appreciate your help as I have little knowledge in Excel.
To calculate balance against accrued PTO, please review the PTO Manager template indzara.com/product/small-business-paid-time-off-manager/
Best wishes.
May I know if there is any template for shift scheduling?
Sorry, I don't have one for shift scheduling yet.
Best wishes.
Hello I need to add more leave type, however it only allows 5, can you help me add additional 5 more. Thank you.
Please do the following to add more leave types.
1. Just type sixth leave type in SETTINGS sheet’s Leave type table.
2. In the TEAM DASHBOARD sheet,
a. Edit conditional formatting rules. You will see rules for the 5 colors.We need to create a similar rule. The formula for the sixth color is =C8=INDEX(L_LEAVE,6).
Apply this to all the cells in the calendar.
b. Insert a column between columns AM and AN. This new column is to calculate the sum for sixth leave type for each employee.
c. In that new column, we need to enter formula. You can copy formula from cell AM8 and paste in AN8(newly created column). Then drag down the cell to row 37 to copy the formula for all employees.
d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types).
3. In the EMPLOYEE REPORT sheet,
a. Edit conditional formatting rules. You will see rules for the 5 colors. We need to create a similar rule for the sixth color. Apply this to all the cells in the calendar.
b. Insert a column between columns AN and AO. This new column is to calculate the sum for sixth leave type for each month.
c. In that new column, we need to enter formula. You can copy formula from cell AN12 and paste in AO12 (newly created column). Then drag down the cell to row 23 to copy the formula for all months.
d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types).
These steps above should implement one additional leave type. You can repeat the same for more additional leave types as needed.
Best wishes.
Like I want to add more holidays in the list by adding more it' not reflecting in team dashboard only first is showing in purple colour
We regret the inconvenience caused.
There only two possible reason for this issue.
1. Data must be entered outside the table.
2. Accidently the Holiday table is deleted.
Requesting to share your sheet to support@indzara.com to check further.
Best wishes.
Amazing!
Thank you. Best wishes.
Lovely Video! Excuse me for butting in, I would love your initial thoughts. Have you heard about - Weydaniel Organized Dominator (probably on Google)? It is an awesome one off product for getting 7000 plus project management and business templates without the headache. Ive heard some extraordinary things about it and my close friend Aubrey at last got cool success with it.
Hi
if i wanted to add employee excused from work can i use other instead
Can you try entering a leave type and assign 0 as value so that it doesn't count as leave taken? Thanks.
Can you add more leave columns?
Please do the following to add more leave types.
1. Just type sixth leave type in SETTINGS sheet’s Leave type table.
2. In the TEAM DASHBOARD sheet,
a. Edit conditional formatting rules. You will see rules for the 5 colors.We need to create a similar rule. Apply this to all the cells in the calendar.
b. Insert a column between columns AM and AN. This new column is to calculate the sum for sixth leave type for each employee.
c. In that new column, we need to enter formula. You can copy formula from cell AM8 and paste in AN8(newly created column). Then drag down the cell to row 37 to copy the formula for all employees.
d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types).
3. In the EMPLOYEE REPORT sheet,
a. Edit conditional formatting rules. You will see rules for the 5 colors.We need to create a similar rule for the sixth color. Apply this to all the cells in the calendar.
b. Insert a column between columns AN and AO. This new column is to calculate the sum for sixth leave type for each month.
c. In that new column, we need to enter formula. You can copy formula from cell AN12 and paste in AO12 (newly created column). Then drag down the cell to row 23 to copy the formula for all months.
d. In the Total Leave column, edit the formula to sum all the 6 leave types (by default it was for 5 leave types).
These steps above should implement one additional leave type. You can repeat the same for more additional leave types as needed.
Please try and let me know if there are any questions.
Best wishes.
Indzara is it possible to make video for this step?
Hi, I can't seem to download the template. Is it still available?
The links to files are available in the blog post. indzara.com/2016/11/employee-leave-vacation-tracker/ Please search for the word 'Download' to find the location. I have also emailed the file. Thanks. Best wishes.
how do i add the formula for team dashboard if the employee is taking halfday leave and i want it to be shown on the calendar table that says "AM" or "PM"?
Is it possible to create AM and PM as two different leave types in your scenario? if so, they would appear so in the calendar.
Best wishes.
@@indzara i did create a new leave type as half day (am) and half day (pm). I wonder if i should use the IF function for this.
Can you please send the file to support@indzara.com and clarify what is the expected result? We will review and get back to you.
Best wishes.
How can i maintain multiple one day leave in one month? let take exp:- if some is taking leave 2nd feb 2017 and 15 feb 2017 how ll i maintain in this leave tracker? ple help me ASAP.
Please enter each vacation in separate rows. Please add more rows to the table as more vacations are taken, whether it is one person taking multiple vacations or multiple people taking vacation. Best wishes.
How do i make it so, Leave (Vacation) stays as 0 days over the weekends, but if i work (Duties) over a weekend, it shows up as 2 days. Example - i'm on a course for 2 weeks, and have to work weekends. Therefore i want this 'course' to be shown as 14 days and not 10 days.
Thanks. There is no simple way to modify this. We can create a leave type 'Weekend' and assign 0 as value. We would not select any weekdays as weekends in the Settings. But we would enter weekends as leave in the Leave sheet. We would have to do this for each employee separately (separate rows in Leave sheet).
Hope this helps.
In the future, I will have to incorporate this feature to override weekends as an option.
Best wishes.
Thanks, it was more to highlight the weekends (in grey) so they are clear to see. When i tried this manually, and go back/forward a month, it started to highlight different days i.e friday/saturday, thursday/friday etc. I'll try your method above, see if i can work something out.
What is the password to unprotect the sheet in employee Leave Tracker Excel Template. I want to change the sequence of months but it requires password to unprotect the sheet.
Thank you for using our template.
The password to unprotect the sheet is indzara.
Best wishes.
Sir i have suffered problem to add more than 30 employee plz help me how can i enter more employee :)
You can enter more than 30 employees in the Employees sheet. There is no limit imposed here. However, the Team Dashboard shows only 30 employees. To extend that, follow these steps. For example, to support 35 employees,
Unprotect the Team Dashboard sheet with the password indzara
Select cells B36:AO36 (this is the row of Employee 29)
Drag down for 6 rows as to fill them with the formulas.
Now you should see 35 (29+6) employees accounted for. If you need to extend for more employees, follow the same steps but increase the number of rows you are copying.
If you print or export to PDF, adjust print settings accordingly to include these new additional employees.
Best wishes.
hi this is great sheet, could you please modify it for year 2017 and onwards
Thank you. The template works for any year for any number of years. Please let me know if there are any questions.
Best wishes.
Thank you. The template works for any year for any number of years. Please let me know if there are any questions.
Best wishes.
your holidays does not contains 2017 and further and please make a column that show total working hours as per 9 hour/day wise excluding leaves(sick leave, training) in case half day include 4.5 hours for all employee and for each month.
In the Settings sheet, we can enter holidays in the Holidays table. We can enter any year's holidays as per our needs. If I have misunderstood your question, please provide more details.
I am not following the specifics of the column you are referring to. Sorry. The template is based on day level leave tracking and not hourly level. It would require a different design.
Please let me know if there are any questions.
Best wishes.
Give the template like I can we maintain every month without removing. i want for yearly . exp. if i maintain February month i don't want to remove for previous month. Please help
+Sarita Patra yes, you don't have to remove any data monthly. Thanks.
Can i have contact no so i can clear in phone call.
Is there a way to set the cell wherein you would know if a employee has reached their limit on the amount of time they have to take off
We can create a formula with an IF function to flag if the vacation taken has reached a specific number. We can also use conditional formatting to visually highlight.
Best wishes.
That would be great for me. I can really use that
How to save leave details monthly basic once i have changed start date and end date its not sat save last month plz help me out.
All the data in the Leave table will be used for reporting. Please do not remove vacations. Please keep adding more rows to the table. Best wishes.
If i have add one more row and i enter date in team dashboard its not showing.Please help.
Can i have the soluation?
Is it possible to modify to include:
a. different number of leave available days per employee (for example, in Japan, we start each employee with 15 days of annual leave - starting only after they complete the probation period - then prorated for months left in the year - and then, on their anniversary date with the company one day is added each year until they reach the maximum of 20 days (this is the labor law rule in Japan).
b. Example: Employee starts June 1, 2019. Probation ends August 31. Balance of the year Employee has 5 days vacation. Starting Jan. 1, 2020, they have 15 days, but on June 1, 2020 that increases by 1 day.
c. Carry forward unused leave. Example: Employee 1 above used all vacation days in 2019 (5 days), but in 2020 only uses 10 days. The remaining 6 days can be carried forward to the next year, so in 2021 the start the years with 17 days (1 more day added at anniversary date) + 6 days = 23 days.
Thanks for your interest.
Please try the PTO Calculator template for one employee. indzara.com/2015/09/employee-pto-tracker-free-excel-template/
This allows setting probationary period, accrual rates based on employee tenures and rollover policy. If it works, the premium template PTO Manager indzara.com/product/small-business-paid-time-off-manager/ supports multiple employees.
Please let me know if I am missing anything.
Best wishes.
there is any possibility to translate months names in Dashboard page?
The hidden sheet H has the month names. Please translate them.
Best wishes.
How do you add more than 5 types of leave?
There is a video course available on how to build this entire template: courses.indzara.com/p/vacation-attendance-tracker-dashboard-in-microsoft-excel
I will also be publishing a new template that will support 12 types of leave along with many other enhancements. It should be available later this month (Aug 2019).
Best wishes.
can it show the whole year?
for all staff? one year in one veiw
The published template shows only one month for all employees together and the whole year for one employee at a time. We have to develop if we want to show the annual total for all employees in one sheet. Please let me know if there are any questions. Best wishes.
What if an employee leaves for vacation more than once?
Each vacation is entered in separate rows. Please enter in a new row the next vacation for the employee.
Best wishes.
How to get the names of employees who are currently on leave???
In the current version, looking in Team Dashboard for a specific date column would inform us those who are on leave. There is no separate report available for that.
Please let me know if any questions.
Best wishes.
How to add department? Suppose a group of employees belong to a particular dept
Sorry, we don't have that feature yet. You can add a column for department and write formulas to bring them to the dashboard. But filtering by department would need more editing of formulas.
We will consider adding it to next version.
Best wishes.
This is amazing but the sheet is protected. Can you mail me the password?
Thanks. Password is indzara
Best wishes.
hi can you help me I need yearly report
Yearly template for an employee is available in the template.
indzara.com/2016/11/employee-leave-vacation-tracker/
Best wishes.