Hello @FeliciaGates-w5s, You are most welcome. Thanks for your appreciation. Glad to hear that our video tutorial is great for you. Keep learning Excel with ExcelDemy! Regards ExcelDemy
Hello @RonaldRamirez-i7m, Sorry to hear your issue. To solve the problem please verify the steps again: First, insert the following formula : =DATE(Summary!$C$6,MONTH(Jan!C4),1) Then go to Format Cells dialog box >> Custom >> type d Hopefully, it will work. You also can check our article: www.exceldemy.com/create-leave-tracker-in-excel/ Here, you will get another way to add days to date. Regards ExcelDemy
Dear, Thanks for subscribing! We appreciate you for joining the ExcelDemy community. We try our best to respond to comments and help whenever possible.
If I change the year, it saves the previous data. I want to change the year; it will show the previous, current, or next year data automatically. How is it possible?
Hello @asishkarmakar974, It will be complex to show data from the previous, current, or next year. Retrieving data from 36 months will make the formulas complex and will cause errors and performance issues. You can create separate sheets for each year (e.g., 2023, 2024, 2025). Instead of retrieving data from all 36 months, it will retrieve data from 12 months of each year then you can create a summary from each year in a new sheet. Regards ExcelDemy
Hello @sadeel82, There are two links in the description box one for the article and one for to download the Excel template. Please check it again. If you are finding it difficult to download the Excel template please download it from here: www.exceldemy.com/wp-content/uploads/2022/05/Create-Leave-Tracker-1.xlsx 📚 ⯆ DOWNLOAD the workbook here: www.exceldemy.com/create-leave-tracker-in-excel/#download 🌍 ⯆ Checkout the article here: www.exceldemy.com/create-leave-tracker-in-excel/ Regards ExcelDemy
Would be great if you explained how you hid your grid and have a blank template. I'm lost from the very beginning because when I select b4-l4 and center margain, that part doesnt work and it only inserts it into b4.
Hello @matthewhymer5252, No worries. We will explain the steps to hide/remove the gridlines from sheet in Excel. Go to the View tab >> from Show group >> uncheck Gridlines. It will remove the gridlines from the sheet. To remove gridlines you need not to use merge and center option. For details please follow this article: www.exceldemy.com/learn-excel/gridlines/edit/ Regards ExcelDemy
Hi thank you for making this video. It is really helpful but I have a problem generating the leave tracker. I inserted the formula correctly but I noticed the B10 in the formula and I do not know where it comes from. Can you give explanation on that or make a video specifically explaining the formula. That would be so helpful. Thank you. Plus my leave tracker is for 6 employees
Dear, Thanks for your nice words and feedback! Your appreciation means a lot to us. Assuming your mentioned formula is the following: =IFERROR(INDEX(Jan!AJ$9:AJ$14,MATCH($A10,Jan!$C$9:$C$14,0)),0)+IFERROR(INDEX(Feb!AJ$9:AJ$14,MATCH($A10,Feb!$C$9:$C$14,0)),0)+IFERROR(INDEX(Mar!AJ$9:AJ$14,MATCH($A10,Mar!$C$9:$C$14,0)),0)+IFERROR(INDEX(Apr!AJ$9:AJ$14,MATCH($A10,Apr!$C$9:$C$14,0)),0)+IFERROR(INDEX(May!AJ$9:AJ$14,MATCH($A10,May!$C$9:$C$14,0)),0)+IFERROR(INDEX(Jun!AJ$9:AJ$14,MATCH($A10,Jun!$C$9:$C$14,0)),0)+IFERROR(INDEX(Jul!AJ$9:AJ$14,MATCH($A10,Jul!$C$9:$C$14,0)),0)+IFERROR(INDEX(Aug!AJ$9:AJ$14,MATCH($A10,Aug!$C$9:$C$14,0)),0)+IFERROR(INDEX(Sep!AJ$9:AJ$14,MATCH($A10,Sep!$C$9:$C$14,0)),0)+IFERROR(INDEX(Oct!AJ$9:AJ$14,MATCH($A10,Oct!$C$9:$C$14,0)),0)+IFERROR(INDEX(Nov!AJ$9:AJ$14,MATCH($A10,Nov!$C$9:$C$14,0)),0)+IFERROR(INDEX(Dec!AJ$9:AJ$14,MATCH($A10,Dec!$C$9:$C$14,0)),0) How the formula works: As the video explains, it repeats the first part for 12 new sheets. If you understand the first section correctly, you will quickly get ideas for the rest of the formula. Let's discuss the IFERROR(INDEX(Jan!AJ$9:AJ$14,MATCH($A10,Jan!$C$9:$C$14,0)),0) section of the formula: The formula looks up the value in cell $A10 within the range Jan!$C$9:$C$14, finds the corresponding value in Jan!AJ$9:AJ$14 using the INDEX function, and returns 0 if there is any error in the process (e.g., if the value is not found). You mentioned that your leave tracker consists of six employees. Don't worry! We have adjusted the Excel file based on your requirements. You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/05/Neo-Letsoalo-SOLVED.xlsx
@@exceldemy2006 Thank you again, The only option I get on the sheet is for the year 2023, Maybe I have not got the latest leave tracker with a drop down. I am also trying to add conditional formatting as color which would be helpful to quickly identify the type of leaves taken by employees. I am learning so much while I am trying all these. It is just amazing.
Hello @ShakeelAhmed-xm5yc, You are most welcome. We are glad to hear that this was helpful to you. Sorry for the previous reply. You will need to manually change the year from the Overview sheet and Summary sheet then the date will be updated automatically in the Months sheet. Here, I have added a drop-down list for the Year in the Overview and Summary sheet. Download the Excel File: www.exceldemy.com/wp-content/uploads/2024/08/Leave-Tracker-with-Drop-Down-list.xlsx Keep learning Excel with ExcelDemy! Regards ExcelDemy
@exceldemy2006 Thank you very much for the file you sent. The file I am using includes over 100 employees, and I've made several customizations, including conditional formatting and colour changes. The File you provided has an amazing drop-down feature for the year, but I can't afford to make all the changes to the new sheet. Since I am using the first file that I downloaded from Exceldemy. My only request is that you share the steps to add the drop-down year options to my existing sheet. Tha way I can learn and add years accordingly. Thank you again in advance.
Hello @ShakeelAhmed-xm5yc, You are most welcome. No worries I am explaining step by step procedures to add drop down list in the Year cell. In overview sheet select the Year cell. Here, I selected C6 cell. Go to the Data tab >> from Data Tools >> select Data Validation. You will get a dialog box of Data Validation. In Allow: field >> select List >> in Source: insert the years of your choice. 2023,2024,2025,2026,2027,2028,2029,2030 You can change the values of source whenever you want. Now, click on Ok. You can follow the similar steps for the summary column or just copy the C6 cell of overview sheet then paste in C6 cell of Summary sheet. Regards ExcelDemy
@@exceldemy2006 Wow, this is just amazing.... the steps are so crystal clear easy to understand details... I would love to get some courses from you team. i don't mind paying for it... do you have any of these courses.. please advise. • Data Analysis and Interpretation • Statistical Process Control (SPC) • Data Visualization (e.g., Tableau, Power BI) • Data Modelling
Hello @suki6542, You are most welcome. Thanks for your appreciation. I updated the sheets with hours using the "h:mm" time format. You can use your own format. Here, I updated the summary sheet and Jan month, Please create rest of the months based on the January format. Please download the Excel file and then modify it based on your requirements. www.exceldemy.com/wp-content/uploads/2024/07/Create-Leave-Tracker-in-Hours.xlsx Regards ExcelDemy
Dear, Thanks for sharing your problem! Adding more rows to work with more employees and dragging the formula are not enough for the leave tracker to work properly; you must also make all the necessary adjustments. Don't worry! You have improved an Excel file where you can work with more than 100 employees. You can download the Excel file: www.exceldemy.com/wp-content/uploads/2024/06/Shahidatul-Amirah-SOLVED.xlsx
Hello @kimsimmons9472, The formulas aren't missing! You can find both the formulas and the Excel file link in the description box. For detailed explanations of the formulas, check out the article linked as well. Article: www.exceldemy.com/create-leave-tracker-in-excel/ Excel File: www.exceldemy.com/wp-content/uploads/2022/05/Create-Leave-Tracker-1.xlsx Regards ExcelDemy
Hello @abeerashahid3569, To add more leaves you do not need to add new formulas you need to add leave types and drag the previous formulas to the new cells or columns. Follow the given steps: In the Summary sheet, First, add the Leave Type and Symbol in the summary sheet. I added Annual Leave - AL Then, insert the AL column before the Total column in the Summary sheet. Now, drag the formula from H10 to H11. Then, drag the formula from H15 to I15. Now, go to the month Jan sheet, 1. Update the drop-down list. To update it: select any cell where a drop-down exists. I selected the D9 cell. From the Data tab >> select Data Validation >> update the range from the summary sheet (New Range: =Summary!$M$9:$M$15) Drag the new drop-down list to all the cells. 2. Update Total Leaves In AP7 cell insert the following formula =Summary!I9 Now, drag the formula from AO8 to AP8. Then, drag the formula from AO9 to AP9. Finally, drag the formula from AP9 to AP13. Please update the sheet for the remaining months following the same steps. Now, you are all set to use the new AL leave. Download the Excel file with the New Leave Type: www.exceldemy.com/wp-content/uploads/2024/07/Leave-Tracker-with-New-Leave-Type.xlsx Regards ExcelDemy
Hello @abeerashahid3569, You're very welcome! I'm glad you found the description helpful. Your kind words mean a lot to me. Thank you! Keep learning Excel with ExcelDemy. Regards ExcelDemy
Dear, Thanks for your comment! You are facing trouble when counting the leave taken; instead of counting leave, it counts zeros. Several reasons, such as Data Input Errors, Data Validation, and Cell References, may lead to such a situation. So, check whether the leave data input for each employee and month is accurate. Make sure that data validation is correctly applied for leave types. All cell references in formulas must point to the correct cells or range. Providing an ultimate solution for your problem is difficult without glancing at your Excel file and being remote. So, we recommend that you share your problem within the ExcelDemy Forum and attach your current workbook. ExcelDemy Forum: exceldemy.com/forum/
Hello @Tensioncreato, Haha, caught in the act! Don't worry, your secret's safe with us. Just make sure to look busy if they walk by! 😂😂 By the way, keep learning Excel with ExcelDemy before your manager knows. Regards ExcelDemy
Hi There! I have been following this step by step with no problems until I get to step 3. The long formula in my summary is not working properly. My cell numbers are slightly different than yours. Instead of AH 9 and AH13, I'm using cells AJ9 and AH16. I made sure to change that in my formula to. Could the $B10 part of the formula be whats wrong?
Dear, thanks for your patience. Instead of range AJ9:AJ13, you are using range AJ9:AJ16, which means you created the tracker sheet for eight employees. You are right about the issues in column B of the Summary sheet. Perhaps, you modified the formula correctly, however, you did not insert the employee names. So, we have developed another sheet based on your requirements. You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/03/Customer-Service-SOLVED.xlsx
Dear @user-ss3ev8fh2s, Thank you for your feedback. Regarding your question on getting numbers instead of 29. Actually, 45320 is the date-time code in Excel, to convert this value to date follow these steps. Just select the cell with the value of 45320, go to the Home tab >> Number group >> Number Format drop-down >> select the Date format. Alternatively, choose the cell with the value of 45320 >> press Ctrl+1 on your keyboard >> select a Date format. You are good to go. Hopefully, this answers your question. Make sure to stay connected with ExcelDemy! 🎉❤. Have a good day. Regards, ExcelDemy
Is there a way to create a tracker like this for 1 person that will show the Initial carryover hours, the accrual amounts for vacation and sick time in hours and show a running tally of how much time for each category that has been used vs how much remains. In addition to how much time must be used before the end of the year in each category vacation vs sick) to not surpass the carryover cap?
Hello @that1sha405, There is a way to create personal tracker based on hours. Created headers for all the mentioned criteria: Date Type Hours Accrued Hours Used Running Total Carryover Cap Hours to Use Used the following formulas to calculate Running Total and Hours to Use: E2: =C2-D2 (Initial running total) E3: =E2+C3-D3 (Drag down for each entry) G3: =MAX(0, E3-F3) (Hours to use before year-end) Updated Excel File: www.exceldemy.com/wp-content/uploads/2024/07/Create-Database-in-a-Table-Format-Add-and-Delete.xlsm You can use this concept to create personal tracker of your choice. Regards ExcelDemy
@@exceldemy2006 perfect!!!! I added a notes column, googled how to add a drop down for the type and it all came together. Thank you so much. I can tell this will be an ever-evolving spreadsheet
Hello @that1sha405, That's fantastic to hear! We're thrilled you found the solutions to enhance your spreadsheet with the notes column and dropdown feature. Keep experimenting and evolving your spreadsheet, there are endless possibilities! Feel free to reach out if you have any more questions or need further assistance. Happy Excel-ing with ExcelDemy. Regards ExcelDemy
Dear, Thanks for sharing your difficulties! It is very difficult to provide an ultimate solution without glancing at your Excel file and being remote. So, please share your problem in the ExcelDemy Forum and attach your Excel file. ExcelDemy Forum: exceldemy.com/forum/
Hello @@elhacendiop6114 , You are most welcome. Thanks for watching our video. Your appreciation and satisfaction means a lot to us. Keep learning Excel with ExcelDemy. Regards ExcelDemy
Dear, we would appreciate it if you could describe the problem and mention which version of Excel you are using in the ExcelDemy Forum. ExcelDemy Forum: exceldemy.com/forum/
Dear @karlagatep570, Thank you for your feedback. We are sorry to hear that you’re facing difficulties counting the total leaves. Total leaves count uses the COUNTIF function, Make sure that: *1.* You’ve selected the correct range (employee whose leave you want to count) *2.* The leave type symbol is the same as in the Summary worksheet. Check the video from 16:45. The formula is *=COUNTIF($D9:$AH9,AJ$7)* If the problem persists, you can post a description of the problem with your Excel file in our Exceldemy forum. You’ll find Excel experts to help you solve your problems, sign up today and get free solutions. Exceldemy Forum: exceldemy.com/forum/ Make sure to stay connected with Exceldemy!🎉❤. Have a good day. Regards, Exceldemy
Dear, thanks for sharing a practical problem. To add half-day personal leave and count 0.5, you can use the following formula: =0.5*COUNTIF($E9:$AI9,$AP$7) Please check the following: www.exceldemy.com/wp-content/uploads/2024/03/adding-half-day-personal-leave.gif You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/03/Syafiq-Rashid-SOLVED.xlsx
very good teaching and really explanatory. I followed all the steps but I found out my summary is not updating even after copying the formular. kindly advice
Dear @stephenfamiyesin6684, Thank you for your feedback. We are delighted to hear that you have figured out the error and the formula is working perfectly. Nice work! It’s important to note, that the formula used in the summary worksheet is quite large and unwieldy as it contains lots of cell references and worksheet names. So we suggest you make as little changes to it as possible to avoid potential errors. Make sure to stay connected with ExcelDemy!🥳❤ . Have a good day. Regards, Exceldemy
Dear, Thanks for your question! The leave tracker mentioned here should be able to handle the scenarios you described effectively. So, feel free to use the leave tracker Excel file; the download link is in the description section. You can modify it according to your needs.
@@sanjeevfernandes8280 Dear, Thanks for sharing your problem! After adding 100 rows, the leave tracker needs to make all the necessary adjustments to work properly. Don't worry! Based on your goal, we have improved the file and made the necessary formula adjustments. Please check the following: www.exceldemy.com/wp-content/uploads/2024/05/How-to-Create-a-Leave-Tracker-in-Excel-for-100-employees.gif You can download the improved Excel file: www.exceldemy.com/wp-content/uploads/2024/05/Sanjeev-Fernandes-SOLVED.xlsx
@@sanjeevfernandes8280 Dear, You are very welcome! We are happy to hear that the improved tracker file is working well and are glad we could make your work easier.
Hello there. Hi there, please help. It added all the leave from January to November to the Summary but not for December.Why doesn't the December leave tracking appear in the summary? Please help. Thanks
Dear @chipiliromiyanga967, Thank you for reaching out, and we appreciate your feedback. We are sorry to hear that having some trouble with the formula in the summary worksheet. It’s important to note, that the formula used in the summary worksheet is quite large and unwieldy. Make sure your worksheet names are the same as used in the formula. Also, there are mixed and absolute cell references with dollar signs before column letters and row numbers. Any incorrect referencing can lead to erroneous results. So we suggest you make as few changes to the formula as possible to avoid errors. Make sure to stay connected with ExcelDemy!🥳❤. Have a good day. Regards, Exceldemy
Hello @CoordinatorAuditNorth-East, You are most welcome. Thanks for watching our video. Your appreciation and satisfaction means a lot to us. Keep learning Excel with ExcelDemy. Regards ExcelDemy
Hello @slc2553, Hi, thank you for your interest! Currently, we don't have a version specifically compatible with Mac Numbers, but we have plans to cover Mac topics in the future. Stay tuned! Regards ExcelDemy
Dear @deepshikhadaniel2504, Thank you for your feedback. Regarding the issue of dates not updating on your February worksheet. Follow the steps below. Make sure to type in the month name “February” in full in the C4 cell of the “Feb” worksheet. The final formula in the C4 should be the following: ="February "&Summary!C6 This should give you the correct dates for February. Now the formula for “Days” depends on the correct dates of the month. This is the correct formula that should return the days. =IF(D8="","",INDEX({"Su";"M";"Tu";"W";"Th";"F";"Sa"},WEEKDAY(D8,1))) In addition, go to the Formulas tab, you’ll find the “Calculation Options” drop-down. Make sure that the “Automatic” option is checked. This tells Excel to recalculate all dependent formulas every time a value, formula, etc. is changed. Hopefully, this solves the issue. However, if the problem persists, you can post a description of the problem with your Excel file in our Exceldemy forum. You’ll find Excel experts to help you solve your problems, signup today and get free solutions. Exceldemy Forum: exceldemy.com/forum/ Make sure to stay connected with Exceldemy! 🥳💖 Have a good day. Regards, Exceldemy
I got stuck on the formula for the dates. excel wont calculate the formula and highlights the $C6$ after Summary! have no idea why kept changing the years but still..please help
Dear, thanks for sharing your problem. It seems like you are facing an issue with the formula: =DATE(Summary!$C$6, MONTH(Jan!C4), 1) Perhaps you mistakenly use $C6$ to lock the cell. To use absolute cell reference, you must use $C$6. Though there are a few steps in developing a Create Leave Tracker, you can still get stuck when following these steps. It would be great if you could share your workbook and describe your problem in more detail within the ExcelDemy Forum. ExcelDemy Forum: exceldemy.com/forum/
Dear, thanks for the question. The leave tracker designed here is suitable for tracking for one year only. But, yes! There is a way to make it work for multiple years within the same workbook. In that case, you must create separate summary sheets for each year to track your data. Each sheet would follow the same layout but reference the specific year data. We recommend you maintain separate workbooks for each year to keep things organized.
Dear @chipiliromiyanga967, Thank you for your feedback. Here’s a detailed explanation of the last part. Formula: =IFERROR(INDEX(Jan!AJ$9:AJ$13,MATCH($B10,Jan!$C$9:$C$13,0)),0)+IFERROR(INDEX(Feb!AJ$9:AJ$13,MATCH($B10,Feb!$C$9:$C$13,0)),0)+IFERROR(INDEX(Mar!AJ$9:AJ$13,MATCH($B10,Mar!$C$9:$C$13,0)),0)+IFERROR(INDEX(Apr!AJ$9:AJ$13,MATCH($B10,Apr!$C$9:$C$13,0)),0)+IFERROR(INDEX(May!AJ$9:AJ$13,MATCH($B10,May!$C$9:$C$13,0)),0)+IFERROR(INDEX(Jun!AJ$9:AJ$13,MATCH($B10,Jun!$C$9:$C$13,0)),0)+IFERROR(INDEX(Jul!AJ$9:AJ$13,MATCH($B10,Jul!$C$9:$C$13,0)),0)+IFERROR(INDEX(Aug!AJ$9:AJ$13,MATCH($B10,Aug!$C$9:$C$13,0)),0)+IFERROR(INDEX(Sep!AJ$9:AJ$13,MATCH($B10,Sep!$C$9:$C$13,0)),0)+IFERROR(INDEX(Oct!AJ$9:AJ$13,MATCH($B10,Oct!$C$9:$C$13,0)),0)+IFERROR(INDEX(Nov!AJ$9:AJ$13,MATCH($B10,Nov!$C$9:$C$13,0)),0)+IFERROR(INDEX(Dec!AJ$9:AJ$13,MATCH($B10,Dec!$C$9:$C$13,0)),0) Explanation: MATCH($B10,Jan!$C$9:$C$13,0) The MATCH function returns the position of the employee name in the $B10 cell within the $C$9:$C$13 range of the "Jan" worksheet. IFERROR(INDEX(Jan!AJ$9:AJ$13,MATCH($B10,Jan!$C$9:$C$13,0)),0)+... The INDEX function returns the number of leaves from the “Total Leaves” table in the AJ$9:AJ$13 range of the “Jan” worksheet based on the position returned by the MATCH function. The IFERROR function is used for error handling. If a match is found, it returns that value; if a match is not found, it returns a 0 value instead of an error. This pattern continues for the rest of the months, each time searching for the matching value in the “Total Leaves” table of the respective month's worksheet and adding up the results to get the total leaves. Make sure to stay connected with ExcelDemy!🎉🥳 Have a good day. Regards, Exceldemy
Dear @Mmiilliinn, Thank you for your feedback. We are sorry to hear that you’re facing difficulties. The formula that accounts for the last couple of days of the month has mixed cell references. Make sure you’ve applied them correctly. We suggest you copy the formula and then make adjustments as needed. Here’s the formula: =IF(MONTH($AE8+3)>MONTH($D$8),"",$AE8+3) After inserting the formula, you need to apply a custom date format. Select the cell and press Ctrl+1. Choose the Custom category and replace the “m/d/yyyy” format with “d” to show only the dates. For further clarification, you can watch the video from 8:10. Make sure to stay connected with Exceldemy! 🎉❤. Have a good day. Regards, Exceldemy
This is a great video!! THANK YOU!
Hello @FeliciaGates-w5s,
You are most welcome. Thanks for your appreciation. Glad to hear that our video tutorial is great for you.
Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
Hello, I cannot get the dates formula to work on the Jan tab, when I go to change to custome and add "d", doesn't show 1 😞
Hello @RonaldRamirez-i7m,
Sorry to hear your issue. To solve the problem please verify the steps again:
First, insert the following formula : =DATE(Summary!$C$6,MONTH(Jan!C4),1)
Then go to Format Cells dialog box >> Custom >> type d
Hopefully, it will work.
You also can check our article: www.exceldemy.com/create-leave-tracker-in-excel/
Here, you will get another way to add days to date.
Regards
ExcelDemy
Just subscribing because you have replied to each cmt and solve their issue👍👍👍
Dear, Thanks for subscribing! We appreciate you for joining the ExcelDemy community. We try our best to respond to comments and help whenever possible.
If I change the year, it saves the previous data. I want to change the year; it will show the previous, current, or next year data automatically. How is it possible?
Hello @asishkarmakar974,
It will be complex to show data from the previous, current, or next year. Retrieving data from 36 months will make the formulas complex and will cause errors and performance issues.
You can create separate sheets for each year (e.g., 2023, 2024, 2025). Instead of retrieving data from all 36 months, it will retrieve data from 12 months of each year then you can create a summary from each year in a new sheet.
Regards
ExcelDemy
There is no link in the describtion part
Hello @sadeel82,
There are two links in the description box one for the article and one for to download the Excel template. Please check it again.
If you are finding it difficult to download the Excel template please download it from here:
www.exceldemy.com/wp-content/uploads/2022/05/Create-Leave-Tracker-1.xlsx
📚 ⯆ DOWNLOAD the workbook here:
www.exceldemy.com/create-leave-tracker-in-excel/#download
🌍 ⯆ Checkout the article here:
www.exceldemy.com/create-leave-tracker-in-excel/
Regards
ExcelDemy
Would be great if you explained how you hid your grid and have a blank template. I'm lost from the very beginning because when I select b4-l4 and center margain, that part doesnt work and it only inserts it into b4.
Hello @matthewhymer5252,
No worries. We will explain the steps to hide/remove the gridlines from sheet in Excel.
Go to the View tab >> from Show group >> uncheck Gridlines.
It will remove the gridlines from the sheet.
To remove gridlines you need not to use merge and center option.
For details please follow this article: www.exceldemy.com/learn-excel/gridlines/edit/
Regards
ExcelDemy
Hi thank you for making this video. It is really helpful but I have a problem generating the leave tracker. I inserted the formula correctly but I noticed the B10 in the formula and I do not know where it comes from. Can you give explanation on that or make a video specifically explaining the formula. That would be so helpful. Thank you.
Plus my leave tracker is for 6 employees
Dear, Thanks for your nice words and feedback! Your appreciation means a lot to us.
Assuming your mentioned formula is the following:
=IFERROR(INDEX(Jan!AJ$9:AJ$14,MATCH($A10,Jan!$C$9:$C$14,0)),0)+IFERROR(INDEX(Feb!AJ$9:AJ$14,MATCH($A10,Feb!$C$9:$C$14,0)),0)+IFERROR(INDEX(Mar!AJ$9:AJ$14,MATCH($A10,Mar!$C$9:$C$14,0)),0)+IFERROR(INDEX(Apr!AJ$9:AJ$14,MATCH($A10,Apr!$C$9:$C$14,0)),0)+IFERROR(INDEX(May!AJ$9:AJ$14,MATCH($A10,May!$C$9:$C$14,0)),0)+IFERROR(INDEX(Jun!AJ$9:AJ$14,MATCH($A10,Jun!$C$9:$C$14,0)),0)+IFERROR(INDEX(Jul!AJ$9:AJ$14,MATCH($A10,Jul!$C$9:$C$14,0)),0)+IFERROR(INDEX(Aug!AJ$9:AJ$14,MATCH($A10,Aug!$C$9:$C$14,0)),0)+IFERROR(INDEX(Sep!AJ$9:AJ$14,MATCH($A10,Sep!$C$9:$C$14,0)),0)+IFERROR(INDEX(Oct!AJ$9:AJ$14,MATCH($A10,Oct!$C$9:$C$14,0)),0)+IFERROR(INDEX(Nov!AJ$9:AJ$14,MATCH($A10,Nov!$C$9:$C$14,0)),0)+IFERROR(INDEX(Dec!AJ$9:AJ$14,MATCH($A10,Dec!$C$9:$C$14,0)),0)
How the formula works: As the video explains, it repeats the first part for 12 new sheets. If you understand the first section correctly, you will quickly get ideas for the rest of the formula. Let's discuss the IFERROR(INDEX(Jan!AJ$9:AJ$14,MATCH($A10,Jan!$C$9:$C$14,0)),0) section of the formula: The formula looks up the value in cell $A10 within the range Jan!$C$9:$C$14, finds the corresponding value in Jan!AJ$9:AJ$14 using the INDEX function, and returns 0 if there is any error in the process (e.g., if the value is not found).
You mentioned that your leave tracker consists of six employees. Don't worry! We have adjusted the Excel file based on your requirements. You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/05/Neo-Letsoalo-SOLVED.xlsx
Thank you so much, this is very helpful... just a quick question... in case of year change.. do I just change it from 2023 to 2024, and so on?
@@exceldemy2006 Thank you again, The only option I get on the sheet is for the year 2023, Maybe I have not got the latest leave tracker with a drop down. I am also trying to add conditional formatting as color which would be helpful to quickly identify the type of leaves taken by employees. I am learning so much while I am trying all these. It is just amazing.
Hello @ShakeelAhmed-xm5yc,
You are most welcome. We are glad to hear that this was helpful to you.
Sorry for the previous reply. You will need to manually change the year from the Overview sheet and Summary sheet then the date will be updated automatically in the Months sheet.
Here, I have added a drop-down list for the Year in the Overview and Summary sheet.
Download the Excel File: www.exceldemy.com/wp-content/uploads/2024/08/Leave-Tracker-with-Drop-Down-list.xlsx
Keep learning Excel with ExcelDemy!
Regards
ExcelDemy
@exceldemy2006 Thank you very much for the file you sent. The file I am using includes over 100 employees, and I've made several customizations, including conditional formatting and colour changes. The File you provided has an amazing drop-down feature for the year, but I can't afford to make all the changes to the new sheet. Since I am using the first file that I downloaded from Exceldemy. My only request is that you share the steps to add the drop-down year options to my existing sheet. Tha way I can learn and add years accordingly. Thank you again in advance.
Hello @ShakeelAhmed-xm5yc,
You are most welcome. No worries I am explaining step by step procedures to add drop down list in the Year cell.
In overview sheet select the Year cell. Here, I selected C6 cell.
Go to the Data tab >> from Data Tools >> select Data Validation.
You will get a dialog box of Data Validation.
In Allow: field >> select List >> in Source: insert the years of your choice.
2023,2024,2025,2026,2027,2028,2029,2030
You can change the values of source whenever you want.
Now, click on Ok.
You can follow the similar steps for the summary column or just copy the C6 cell of overview sheet then paste in C6 cell of Summary sheet.
Regards
ExcelDemy
@@exceldemy2006 Wow, this is just amazing.... the steps are so crystal clear easy to understand details... I would love to get some courses from you team. i don't mind paying for it... do you have any of these courses.. please advise.
• Data Analysis and Interpretation
• Statistical Process Control (SPC)
• Data Visualization (e.g., Tableau, Power BI)
• Data Modelling
Thank you for posting this video! I have a question: total leaves is counted as days, how to convert that in hours?
Hello @suki6542,
You are most welcome. Thanks for your appreciation. I updated the sheets with hours using the "h:mm" time format. You can use your own format. Here, I updated the summary sheet and Jan month, Please create rest of the months based on the January format.
Please download the Excel file and then modify it based on your requirements.
www.exceldemy.com/wp-content/uploads/2024/07/Create-Leave-Tracker-in-Hours.xlsx
Regards
ExcelDemy
Hi, what if we need to add more staff to the list? I tried to just copy the formulas and drag it to the new columns but it doesn't work
Dear, Thanks for sharing your problem! Adding more rows to work with more employees and dragging the formula are not enough for the leave tracker to work properly; you must also make all the necessary adjustments.
Don't worry! You have improved an Excel file where you can work with more than 100 employees. You can download the Excel file: www.exceldemy.com/wp-content/uploads/2024/06/Shahidatul-Amirah-SOLVED.xlsx
Thank you so much. This really help a lot.
Hello @McniksonEZSB,
You are most welcome. Thanks for your appreciation. Please stay connected with us.
Regards
ExcelDemy
I am not seeing the formula in the description
Hello @kimsimmons9472,
The formulas aren't missing! You can find both the formulas and the Excel file link in the description box. For detailed explanations of the formulas, check out the article linked as well.
Article: www.exceldemy.com/create-leave-tracker-in-excel/
Excel File: www.exceldemy.com/wp-content/uploads/2022/05/Create-Leave-Tracker-1.xlsx
Regards
ExcelDemy
Hey how can I add more leaves section in SUMMARY? What formula will be used?
Hello @abeerashahid3569,
To add more leaves you do not need to add new formulas you need to add leave types and drag the previous formulas to the new cells or columns.
Follow the given steps:
In the Summary sheet,
First, add the Leave Type and Symbol in the summary sheet.
I added Annual Leave - AL
Then, insert the AL column before the Total column in the Summary sheet.
Now, drag the formula from H10 to H11.
Then, drag the formula from H15 to I15.
Now, go to the month Jan sheet,
1. Update the drop-down list.
To update it: select any cell where a drop-down exists. I selected the D9 cell.
From the Data tab >> select Data Validation >> update the range from the summary sheet (New Range: =Summary!$M$9:$M$15)
Drag the new drop-down list to all the cells.
2. Update Total Leaves
In AP7 cell insert the following formula =Summary!I9
Now, drag the formula from AO8 to AP8.
Then, drag the formula from AO9 to AP9.
Finally, drag the formula from AP9 to AP13.
Please update the sheet for the remaining months following the same steps.
Now, you are all set to use the new AL leave.
Download the Excel file with the New Leave Type:
www.exceldemy.com/wp-content/uploads/2024/07/Leave-Tracker-with-New-Leave-Type.xlsx
Regards
ExcelDemy
@@exceldemy2006 Thankyou so much for this detailed description you are truly a great person.
Hello @abeerashahid3569,
You're very welcome! I'm glad you found the description helpful. Your kind words mean a lot to me. Thank you! Keep learning Excel with ExcelDemy.
Regards
ExcelDemy
Thank you, why is it that my leave tracker is not counting the leave taken? It only picks zero
Dear, Thanks for your comment! You are facing trouble when counting the leave taken; instead of counting leave, it counts zeros.
Several reasons, such as Data Input Errors, Data Validation, and Cell References, may lead to such a situation. So, check whether the leave data input for each employee and month is accurate. Make sure that data validation is correctly applied for leave types. All cell references in formulas must point to the correct cells or range.
Providing an ultimate solution for your problem is difficult without glancing at your Excel file and being remote. So, we recommend that you share your problem within the ExcelDemy Forum and attach your current workbook.
ExcelDemy Forum: exceldemy.com/forum/
Searching if my manager is watching this video or not 😂😂
Hello @Tensioncreato,
Haha, caught in the act! Don't worry, your secret's safe with us. Just make sure to look busy if they walk by! 😂😂
By the way, keep learning Excel with ExcelDemy before your manager knows.
Regards
ExcelDemy
@@exceldemy2006 hahahahah
@Tensioncreato Glad you found that funny! 😄
Hi There! I have been following this step by step with no problems until I get to step 3. The long formula in my summary is not working properly. My cell numbers are slightly different than yours. Instead of AH 9 and AH13, I'm using cells AJ9 and AH16. I made sure to change that in my formula to. Could the $B10 part of the formula be whats wrong?
Dear, thanks for your patience. Instead of range AJ9:AJ13, you are using range AJ9:AJ16, which means you created the tracker sheet for eight employees. You are right about the issues in column B of the Summary sheet. Perhaps, you modified the formula correctly, however, you did not insert the employee names.
So, we have developed another sheet based on your requirements. You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/03/Customer-Service-SOLVED.xlsx
I’m not sure what I keep doing wrong but I keep getting 45320 for the AF cell instead of 29 😅
Dear @user-ss3ev8fh2s,
Thank you for your feedback. Regarding your question on getting numbers instead of 29.
Actually, 45320 is the date-time code in Excel, to convert this value to date follow these steps.
Just select the cell with the value of 45320, go to the Home tab >> Number group >> Number Format drop-down >> select the Date format.
Alternatively, choose the cell with the value of 45320 >> press Ctrl+1 on your keyboard >> select a Date format. You are good to go.
Hopefully, this answers your question. Make sure to stay connected with ExcelDemy! 🎉❤. Have a good day.
Regards,
ExcelDemy
@@exceldemy2006 I was having the same issue thank you for this clarification😊
You are most welcome @TrudyLouvo. Please stay connected with us.
Is there a way to create a tracker like this for 1 person that will show the Initial carryover hours, the accrual amounts for vacation and sick time in hours and show a running tally of how much time for each category that has been used vs how much remains. In addition to how much time must be used before the end of the year in each category vacation vs sick) to not surpass the carryover cap?
Hello @that1sha405,
There is a way to create personal tracker based on hours.
Created headers for all the mentioned criteria:
Date Type Hours Accrued Hours Used Running Total Carryover Cap Hours to Use
Used the following formulas to calculate Running Total and Hours to Use:
E2: =C2-D2 (Initial running total)
E3: =E2+C3-D3 (Drag down for each entry)
G3: =MAX(0, E3-F3) (Hours to use before year-end)
Updated Excel File:
www.exceldemy.com/wp-content/uploads/2024/07/Create-Database-in-a-Table-Format-Add-and-Delete.xlsm
You can use this concept to create personal tracker of your choice.
Regards
ExcelDemy
@@exceldemy2006 perfect!!!! I added a notes column, googled how to add a drop down for the type and it all came together. Thank you so much. I can tell this will be an ever-evolving spreadsheet
Hello @that1sha405,
That's fantastic to hear! We're thrilled you found the solutions to enhance your spreadsheet with the notes column and dropdown feature. Keep experimenting and evolving your spreadsheet, there are endless possibilities! Feel free to reach out if you have any more questions or need further assistance. Happy Excel-ing with ExcelDemy.
Regards
ExcelDemy
my may got #value how to solve this?
someone help
Dear, Thanks for sharing your difficulties! It is very difficult to provide an ultimate solution without glancing at your Excel file and being remote. So, please share your problem in the ExcelDemy Forum and attach your Excel file.
ExcelDemy Forum: exceldemy.com/forum/
Thank you so mush
Hello @@elhacendiop6114 ,
You are most welcome. Thanks for watching our video. Your appreciation and satisfaction means a lot to us. Keep learning Excel with ExcelDemy.
Regards
ExcelDemy
at 7:55 my excel doesnt recognise the month from that and after downloading your version it also breaks without even touching anything
Dear, we would appreciate it if you could describe the problem and mention which version of Excel you are using in the ExcelDemy Forum.
ExcelDemy Forum: exceldemy.com/forum/
Hi, one of the options does not count on the "Total Leaves" section, what do I do?
Dear @karlagatep570,
Thank you for your feedback. We are sorry to hear that you’re facing difficulties counting the total leaves. Total leaves count uses the COUNTIF function, Make sure that:
*1.* You’ve selected the correct range (employee whose leave you want to count)
*2.* The leave type symbol is the same as in the Summary worksheet.
Check the video from 16:45. The formula is *=COUNTIF($D9:$AH9,AJ$7)*
If the problem persists, you can post a description of the problem with your Excel file in our Exceldemy forum. You’ll find Excel experts to help you solve your problems, sign up today and get free solutions.
Exceldemy Forum: exceldemy.com/forum/
Make sure to stay connected with Exceldemy!🎉❤. Have a good day.
Regards,
Exceldemy
How to add half day personal leave so it count 0.5 instead of 1?
Dear, thanks for sharing a practical problem. To add half-day personal leave and count 0.5, you can use the following formula: =0.5*COUNTIF($E9:$AI9,$AP$7)
Please check the following: www.exceldemy.com/wp-content/uploads/2024/03/adding-half-day-personal-leave.gif
You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/03/Syafiq-Rashid-SOLVED.xlsx
Hey there! Thanks again.... i got it.
very good teaching and really explanatory. I followed all the steps but I found out my summary is not updating even after copying the formular. kindly advice
I figured out my error and it has worked perfectly
Dear @stephenfamiyesin6684,
Thank you for your feedback. We are delighted to hear that you have figured out the error and the formula is working perfectly. Nice work!
It’s important to note, that the formula used in the summary worksheet is quite large and unwieldy as it contains lots of cell references and worksheet names. So we suggest you make as little changes to it as possible to avoid potential errors.
Make sure to stay connected with ExcelDemy!🥳❤ . Have a good day.
Regards,
Exceldemy
What about the employee taking annual leave continuasely for one month or more?
Dear, Thanks for your question! The leave tracker mentioned here should be able to handle the scenarios you described effectively.
So, feel free to use the leave tracker Excel file; the download link is in the description section. You can modify it according to your needs.
@@exceldemy2006 I added more raws for 100 employees but the summary does not work!
@@sanjeevfernandes8280 Dear, Thanks for sharing your problem! After adding 100 rows, the leave tracker needs to make all the necessary adjustments to work properly.
Don't worry! Based on your goal, we have improved the file and made the necessary formula adjustments. Please check the following: www.exceldemy.com/wp-content/uploads/2024/05/How-to-Create-a-Leave-Tracker-in-Excel-for-100-employees.gif
You can download the improved Excel file: www.exceldemy.com/wp-content/uploads/2024/05/Sanjeev-Fernandes-SOLVED.xlsx
@@exceldemy2006 Super bro!! you made my work easy...👏👏👏Thank you very much....
@@sanjeevfernandes8280 Dear, You are very welcome! We are happy to hear that the improved tracker file is working well and are glad we could make your work easier.
Hello there.
Hi there, please help. It added all the leave from January to November to the Summary but not for December.Why doesn't the December leave tracking appear in the summary? Please help. Thanks
All is good now. I copied the Nov formula and changed it to Dec. Many thanks
Dear HLauHK,
Thanks for your appreciation and what you did is right. You can use the same formula for every month just change the month name.
After coping the formula, my summary isn’t coming out as expected, need some help
Dear @chipiliromiyanga967,
Thank you for reaching out, and we appreciate your feedback. We are sorry to hear that having some trouble with the formula in the summary worksheet.
It’s important to note, that the formula used in the summary worksheet is quite large and unwieldy. Make sure your worksheet names are the same as used in the formula. Also, there are mixed and absolute cell references with dollar signs before column letters and row numbers. Any incorrect referencing can lead to erroneous results. So we suggest you make as few changes to the formula as possible to avoid errors.
Make sure to stay connected with ExcelDemy!🥳❤. Have a good day.
Regards,
Exceldemy
Thank you
Thanks
Hello @CoordinatorAuditNorth-East,
You are most welcome. Thanks for watching our video. Your appreciation and satisfaction means a lot to us. Keep learning Excel with ExcelDemy.
Regards
ExcelDemy
Hi, is there a version of this that is compatible with Mac Numbers? :)
Hello @slc2553,
Hi, thank you for your interest! Currently, we don't have a version specifically compatible with Mac Numbers, but we have plans to cover Mac topics in the future. Stay tuned!
Regards
ExcelDemy
Why my Feb sheet didn't auto update it's days?
Dear @deepshikhadaniel2504,
Thank you for your feedback. Regarding the issue of dates not updating on your February worksheet. Follow the steps below.
Make sure to type in the month name “February” in full in the C4 cell of the “Feb” worksheet. The final formula in the C4 should be the following:
="February "&Summary!C6
This should give you the correct dates for February. Now the formula for “Days” depends on the correct dates of the month. This is the correct formula that should return the days.
=IF(D8="","",INDEX({"Su";"M";"Tu";"W";"Th";"F";"Sa"},WEEKDAY(D8,1)))
In addition, go to the Formulas tab, you’ll find the “Calculation Options” drop-down. Make sure that the “Automatic” option is checked. This tells Excel to recalculate all dependent formulas every time a value, formula, etc. is changed.
Hopefully, this solves the issue. However, if the problem persists, you can post a description of the problem with your Excel file in our Exceldemy forum. You’ll find Excel experts to help you solve your problems, signup today and get free solutions.
Exceldemy Forum: exceldemy.com/forum/
Make sure to stay connected with Exceldemy! 🥳💖 Have a good day.
Regards,
Exceldemy
Thanks it's a good explanation, thanks for your quick response to help me.
You are most welcome.
I got stuck on the formula for the dates. excel wont calculate the formula and highlights the $C6$ after Summary! have no idea why kept changing the years but still..please help
Dear, thanks for sharing your problem. It seems like you are facing an issue with the formula: =DATE(Summary!$C$6, MONTH(Jan!C4), 1)
Perhaps you mistakenly use $C6$ to lock the cell. To use absolute cell reference, you must use $C$6.
Though there are a few steps in developing a Create Leave Tracker, you can still get stuck when following these steps. It would be great if you could share your workbook and describe your problem in more detail within the ExcelDemy Forum.
ExcelDemy Forum: exceldemy.com/forum/
Can you track multiple years in the same workbook or is it only for 1 year?
Dear, thanks for the question. The leave tracker designed here is suitable for tracking for one year only. But, yes! There is a way to make it work for multiple years within the same workbook.
In that case, you must create separate summary sheets for each year to track your data. Each sheet would follow the same layout but reference the specific year data.
We recommend you maintain separate workbooks for each year to keep things organized.
Want a proper explanation for the last step, I am lost.
Dear @chipiliromiyanga967,
Thank you for your feedback. Here’s a detailed explanation of the last part.
Formula:
=IFERROR(INDEX(Jan!AJ$9:AJ$13,MATCH($B10,Jan!$C$9:$C$13,0)),0)+IFERROR(INDEX(Feb!AJ$9:AJ$13,MATCH($B10,Feb!$C$9:$C$13,0)),0)+IFERROR(INDEX(Mar!AJ$9:AJ$13,MATCH($B10,Mar!$C$9:$C$13,0)),0)+IFERROR(INDEX(Apr!AJ$9:AJ$13,MATCH($B10,Apr!$C$9:$C$13,0)),0)+IFERROR(INDEX(May!AJ$9:AJ$13,MATCH($B10,May!$C$9:$C$13,0)),0)+IFERROR(INDEX(Jun!AJ$9:AJ$13,MATCH($B10,Jun!$C$9:$C$13,0)),0)+IFERROR(INDEX(Jul!AJ$9:AJ$13,MATCH($B10,Jul!$C$9:$C$13,0)),0)+IFERROR(INDEX(Aug!AJ$9:AJ$13,MATCH($B10,Aug!$C$9:$C$13,0)),0)+IFERROR(INDEX(Sep!AJ$9:AJ$13,MATCH($B10,Sep!$C$9:$C$13,0)),0)+IFERROR(INDEX(Oct!AJ$9:AJ$13,MATCH($B10,Oct!$C$9:$C$13,0)),0)+IFERROR(INDEX(Nov!AJ$9:AJ$13,MATCH($B10,Nov!$C$9:$C$13,0)),0)+IFERROR(INDEX(Dec!AJ$9:AJ$13,MATCH($B10,Dec!$C$9:$C$13,0)),0)
Explanation:
MATCH($B10,Jan!$C$9:$C$13,0)
The MATCH function returns the position of the employee name in the $B10 cell within the $C$9:$C$13 range of the "Jan" worksheet.
IFERROR(INDEX(Jan!AJ$9:AJ$13,MATCH($B10,Jan!$C$9:$C$13,0)),0)+...
The INDEX function returns the number of leaves from the “Total Leaves” table in the AJ$9:AJ$13 range of the “Jan” worksheet based on the position returned by the MATCH function. The IFERROR function is used for error handling. If a match is found, it returns that value; if a match is not found, it returns a 0 value instead of an error.
This pattern continues for the rest of the months, each time searching for the matching value in the “Total Leaves” table of the respective month's worksheet and adding up the results to get the total leaves.
Make sure to stay connected with ExcelDemy!🎉🥳 Have a good day.
Regards,
Exceldemy
Hi,
when I type +3 in the formula of January 31, the number is gone 😢
Dear @Mmiilliinn,
Thank you for your feedback. We are sorry to hear that you’re facing difficulties. The formula that accounts for the last couple of days of the month has mixed cell references. Make sure
you’ve applied them correctly. We suggest you copy the formula and then make adjustments as needed. Here’s the formula: =IF(MONTH($AE8+3)>MONTH($D$8),"",$AE8+3)
After inserting the formula, you need to apply a custom date format. Select the cell and press Ctrl+1. Choose the Custom category and replace the “m/d/yyyy” format with “d” to show only the dates. For further clarification, you can watch the video from 8:10.
Make sure to stay connected with Exceldemy! 🎉❤. Have a good day.
Regards,
Exceldemy