How to Create Leave Tracker in Excel

[@ejss]

at 7:55 my excel doesnt recognise the month from that and after downloading your version it also breaks without even touching anything

[@exceldemy2006]

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/

[@ravinryandomaoal1873]

Thank you so much,I followed all steps and have created my leave form.do you have a video tutorial on how to create and send salary slips using gsheets?

[@exceldemy2006]

Hello @ravinryandomaoal1873, You are most welcome. Thanks for your feedback and appreciation. We are mainly focused on MS Excel but will update the Google Sheets tutorial sooner. We have some tutorials regarding salary sheets/slips. How to Make Salary Sheet in Excel: kzbin.info/www/bejne/inTdq5aFh7mri68 How to Create Salary Slip Format with Formula in Excel Sheet: www.exceldemy.com/salary-slip-format-in-excel-sheet-with-formula/ How to Make a Payroll System with Payslip in Microsoft Excel: www.exceldemy.com/how-to-make-a-payroll-system-in-microsoft-excel-with-payslip/ How to Create Automatic Salary Slip Generator Using Excel: www.exceldemy.com/automatic-salary-slip-generator-using-excel/ You can apply Excel concepts in Google Sheets. Regards ExcelDemy

[@onebunnechisom7874]

Thank you so much for this video, I have been able to create the worksheet for 2024 without so much of a struggle. Just a quick question, how do I manage my summary sheet if some employees resign along the year and we onboard new employees along the year. for example, let's say I have an employee Alex from Jan 2024 and recruit Jules by May 2024, then Alex decides to exit by July 2024 and we get Jack to replace him by August 2024. How do we ensure our summary sheet is not distorted by all these exits and entry?

[@exceldemy2006]

Hello @onebunnechisom7874, You are most welcome. Glad to hear that you created the worksheet for 2024. To manage employee exits and entries on your summary sheet, you can follow these steps: For resignations: Remove the employee’s leave record from the active sheet but retain their historical data for past leave tracking. For new hires: Add their details with the appropriate start date. Formula adjustment: Use IF or IFERROR formulas to calculate leave based on their joining date and tenure. This ensures that the summary sheet remains accurate even with employee transitions throughout the year. Regards ExcelDemy

[@onebunnechisom7874]

@exceldemy2006 thank you so much for this. I will implement it and see how that goes.

[@exceldemy2006]

Hello @onebunnechisom7874, You are most welcome. We are glad you found the guide helpful. Best of luck implementing the solutions. Let us know how it goes or if you need further assistance! Regards ExcelDemy

[@taylorbrule5371]

Hello! Great video and super helpful, if you were to add in half sick days and half vacation days what would you need to change in the formulas that count the totals to accommodate this? For example, "V" would be a full vacation day but "V1" would be just the morning off and "V2" would be just the afternoon off. However I would like the number in the total leaves table to be all the vacation time off. If someone had one "V" and one V1" in their row I would like the "V" total to be 1.5. Is this possible or too complicated?

[@taylorbrule5371]

I have used =COUNTIF(D9:AH9,"V")+COUNTIF(D9:AH9,"V1")/2+COUNTIF(D9:AH9,"V2")/2 but the total count value is not displaying properly. If I have one "V1" the count will be 0, but if I have two "V1" in the row than the count will be 1. The count is not updating the halves but is recognizing that two halves make 1.

[@exceldemy2006]

Hello @taylorbrule5371, You are most welcome. glad to hear that you found the tutorial helpful. If you want to input 0.5 as V and 1 for V1. You can use the following formula : =SUMPRODUCT(-($D9:$AH9=”HD”)*0.5) It will look for all occurrences of “HD” within the range $D9:$AH9. Each “HD” will be counted as 0.5. You can modify the formula. Download the Excel file to understand it properly: www.exceldemy.com/wp-content/uploads/2024/09/Create-Leave-Tracker-ExcelDemy.xlsx Regards ExcelDemy

[@che89lavi]

Hi. Tnx for the video. Why formulas don't work if we just change the year in the sheet "Summary?"

[@exceldemy2006]

Hello @che89lavi, You are most welcome. Formulas must work after changing the year in summary sheet. Formulas will calculate leave based on the dates. The Year change update Months. It doesn't affect the any formula. Please check out this Excel file: www.exceldemy.com/wp-content/uploads/2024/12/Create-Leave-Tracker-2025.xlsx Regards ExcelDemy

[@che89lavi]

@@exceldemy2006 Hi, tnx, but for some reason, when you try to adjust it on Office 365, it does not work. Even if you click Undo, formulas still showing error in values.

[@exceldemy2006]

Hello @che89lavi, Thank you for your feedback. Since the file was created in Excel 365, it should work as expected when you change the year. Here are a few possible reasons for the issue and how to fix them: Recalculate Formulas: Press Ctrl + Alt + F9 to force a full recalculation of all formulas. Check Date Formats: Ensure the date cells are properly formatted as dates and not as text, as this may disrupt calculations. Formula References: If any formulas rely on named ranges or absolute references, make sure they adjust correctly with the year change. Check for Errors: Look for any circular references or cells showing #VALUE! or #REF! errors, as they might need to be corrected. Regards ExcelDemy

[@stephenfamiyesin6684]

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

[@stephenfamiyesin6684]

I figured out my error and it has worked perfectly

[@exceldemy2006]

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

[@Teesh1812]

Thank you so much for this video. I followed all steps and have created my leave form. Is there a way to make the leave year match the financial year? April to March? This is the period that my employee's leave runs across. I would be so grateful if you have a solution. Thank you :)

[@exceldemy2006]

Hello @Teesh1812, You are most welcome. To align the leave year with your financial year (April to March), you can adjust the leave balance formulas to reset in April. One approach is to use an IF formula that checks if the current date is after March and then resets the leave balance accordingly. You can also modify the year reference in your tracker to match this period. Regards ExcelDemy

[@ShawneePolchisLanteigne]

This video has helped me so much. I've made many modifications so easily to help with my organization. Is there a possibility to have ONLY Sick Time and Personal Time count hourly? Like if they only used 1 hour of sick time, we can calculate it on the same table?

[@exceldemy2006]

Hello @ShawneePolchisLanteigne, You are most welcome. To track Sick Time and Personal Time hourly, modify your leave tracker to include hours for specific categories. Here’s how: 1. Insert new columns, “Sick Hours” and “Personal Hours,” next to your leave days columns. 2. Enter the number of hours (e.g., 1 for a 1-hour sick leave). 3. Use a formula to sum these hours for each category, like =SUM(Sick Hours Range) and =SUM(Personal Hours Range). 4. Adjust your main formula to add hours from these new columns into your overall total, maintaining accurate records for partial-day leave tracking. This setup tracks partial hours while keeping all leave data in a single table. Regards ExcelDemy

[@ShawneePolchisLanteigne]

@@exceldemy2006 amazing! Thank you so much!

[@exceldemy2006]

Hello @ShawneePolchisLanteigne, You are most welcome. Keep learning Excel with ExcelDemy. Regards ExcelDemy

[@CustomerService-w1z]

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?

[@exceldemy2006]

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

[@emilylynn7621]

Hi, is there any chance you could explain how I can get a half day to show up as 0.5 in the total leave column? I have tried the two methods that you left in other comments, however it is still coming up as 1 when selecting a half day in the tracker

[@exceldemy2006]

Hello @emilylynn7621, If you want to input 0.5 as half-day leave. You can use the following formula for Half Day (HD) column: =SUMPRODUCT(-($D9:$AH9=”HD”)*0.5) It will look for all occurrences of “HD” within the range $D9:$AH9. Each “HD” will be counted as 0.5. You will need to format the cells with 2 decimal places to properly display the decimal number; otherwise, it will show you a rounded 1. Regards ExcelDemy

[@ShakeelAhmed-xm5yc]

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?

[@ShakeelAhmed-xm5yc]

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

[@exceldemy2006]

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

[@ShakeelAhmed-xm5yc]

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

[@exceldemy2006]

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

[@ShakeelAhmed-xm5yc]

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

[@aliciaamistoso1801]

Thank you for the great tutorial. I have completed all of the months but it does not give a a totals on my summary? Help?

[@exceldemy2006]

Hello @aliciaamistoso1801, You are most welcome. Now you will need to use formula in the summary sheet to get the summary of all leaves from all the months sheet. =IFERROR(INDEX(Jan!AJ$9:AJ$13,MATCH($B11,Jan!$C$9:$C$13,0)),0)+IFERROR(INDEX(Feb!AJ$9:AJ$13,MATCH($B11,Feb!$C$9:$C$13,0)),0)+IFERROR(INDEX(Mar!AJ$9:AJ$13,MATCH($B11,Mar!$C$9:$C$13,0)),0)+IFERROR(INDEX(Apr!AJ$9:AJ$13,MATCH($B11,Apr!$C$9:$C$13,0)),0)+IFERROR(INDEX(May!AJ$9:AJ$13,MATCH($B11,May!$C$9:$C$13,0)),0)+IFERROR(INDEX(Jun!AJ$9:AJ$13,MATCH($B11,Jun!$C$9:$C$13,0)),0)+IFERROR(INDEX(Jul!AJ$9:AJ$13,MATCH($B11,Jul!$C$9:$C$13,0)),0)+IFERROR(INDEX(Aug!AJ$9:AJ$13,MATCH($B11,Aug!$C$9:$C$13,0)),0)+IFERROR(INDEX(Sep!AJ$9:AJ$13,MATCH($B11,Sep!$C$9:$C$13,0)),0)+IFERROR(INDEX(Oct!AJ$9:AJ$13,MATCH($B11,Oct!$C$9:$C$13,0)),0)+IFERROR(INDEX(Nov!AJ$9:AJ$13,MATCH($B11,Nov!$C$9:$C$13,0)),0)+IFERROR(INDEX(Dec!AJ$9:AJ$13,MATCH($B11,Dec!$C$9:$C$13,0)),0) You will get the formula in our Excel workbook: www.exceldemy.com/wp-content/uploads/2024/09/Create-Leave-Tracker.xlsx Please adjust the cell references based on your month's sheet. Regards ExcelDemy

[@aliciaamistoso1801]

@@exceldemy2006 if you have more employees in the list, will the formula still works?

[@exceldemy2006]

Hello @aliciaamistoso1801, Of course, the formula will work if you have more employees in the list. You will need to update the cell range in the Summary sheet formula. Change the reference of months sheet: Jan!$C$9:$C$13 Regards ExcelDemy

[@abeerashahid3569]

Hey how can I add more leaves section in SUMMARY? What formula will be used?

[@exceldemy2006]

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

[@abeerashahid3569]

@@exceldemy2006 Thankyou so much for this detailed description you are truly a great person.

[@exceldemy2006]

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

[@matthewhymer5252]

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.

[@exceldemy2006]

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

[@Venus_rey]

Hello where I can get the formula for leaves summary?

[@Venus_rey]

can't see it😢

[@exceldemy2006]

Hello @Venus_rey, In our article, you will get all the formulas to use. The article link is given in the description box. Here I am attaching it again: www.exceldemy.com/create-leave-tracker-in-excel/ Regards ExcelDemy

[@slc2553]

Hi, is there a version of this that is compatible with Mac Numbers? :)

[@exceldemy2006]

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

[@HLauHK]

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

[@HLauHK]

All is good now. I copied the Nov formula and changed it to Dec. Many thanks

[@exceldemy2006]

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.

[@that1sha405]

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?

[@exceldemy2006]

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

[@that1sha405]

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

[@exceldemy2006]

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

[@karlagatep570]

Hi, one of the options does not count on the "Total Leaves" section, what do I do?

[@exceldemy2006]

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

[@shahriarnayan646]

I have 50 employee, after 5 employee this formula is not working... How can solve it?

[@exceldemy2006]

Hello @shahriarnayan646, While adding new employees you must need to include those cell in the formula. Include the 50 employee's cell reference in all the formula. Please remember to update the formula according to your dataset. The initial formula is: =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) To add new employee change the ranges of the formula in the Summary sheet. =IFERROR(INDEX(Jan!AJ$9:AJ$14,MATCH($B15,Jan!$C$9:$C$14,0)),0)+IFERROR(INDEX(Feb!AJ$9:AJ$14,MATCH($B15,Feb!$C$9:$C$14,0)),0)+IFERROR(INDEX(Mar!AJ$9:AJ$14,MATCH($B15,Mar!$C$9:$C$14,0)),0)+IFERROR(INDEX(Apr!AJ$9:AJ$14,MATCH($B15,Apr!$C$9:$C$14,0)),0)+IFERROR(INDEX(May!AJ$9:AJ$14,MATCH($B15,May!$C$9:$C$14,0)),0)+IFERROR(INDEX(Jun!AJ$9:AJ$14,MATCH($B15,Jun!$C$9:$C$14,0)),0)+IFERROR(INDEX(Jul!AJ$9:AJ$14,MATCH($B15,Jul!$C$9:$C$14,0)),0)+IFERROR(INDEX(Aug!AJ$9:AJ$14,MATCH($B15,Aug!$C$9:$C$14,0)),0)+IFERROR(INDEX(Sep!AJ$9:AJ$14,MATCH($B15,Sep!$C$9:$C$14,0)),0)+IFERROR(INDEX(Oct!AJ$9:AJ$14,MATCH($B15,Oct!$C$9:$C$14,0)),0)+IFERROR(INDEX(Nov!AJ$9:AJ$14,MATCH($B15,Nov!$C$9:$C$14,0)),0)+IFERROR(INDEX(Dec!AJ$9:AJ$14,MATCH($B15,Dec!$C$9:$C$14,0)),0) Follow this forum post solution for better understanding: exceldemy.com/forum/threads/issue-in-leave-tracker.374/#post-1580 Regards ExcelDemy

[@RonaldRamirez-i7m]

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 😞

[@exceldemy2006]

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

[@suki6542]

Thank you for posting this video! I have a question: total leaves is counted as days, how to convert that in hours?

[@exceldemy2006]

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

[@nishamistry9339]

How do I add company holidays?

[@exceldemy2006]

Hello @nishamistry9339, To add company holidays in your leave tracker, create a separate list for holidays and mark those days as non-leave days. You can use Excel’s conditional formatting or formula features (such as IF or VLOOKUP) to ensure these dates are excluded from leave calculations. You can also add a "Holiday" column next to your leave tracker and input the dates manually or use a holiday calendar. For more detailed steps, please refer to the guide in the article. Regards ExcelDemy

[@sp1375]

Great video! Do you do custom jobs also? I’m interested in getting a few templates designed.

[@exceldemy2006]

Hello @sp1375, Thanks for your appreciation. You can post your templates requirements in our ExcelDemy Forum: exceldemy.com/forum/ exceldemy.com/forum/members/shamimarita.2/ Keep learning Excel with ExcelDemy! Regards ExcelDemy

[@JemimahMuchugia]

Thank you, why is it that my leave tracker is not counting the leave taken? It only picks zero

[@exceldemy2006]

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/

[@shahidatul42]

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

[@exceldemy2006]

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

[@cz3143]

@@exceldemy2006 Is it possible to ensure that the rows are organized alphabetically? Ensuring that the formulas still correspond to the right employee? Thanks in advance

[@exceldemy2006]

Hello @cz3143, To organize the rows alphabetically you can use the Sort feature from the data tab. Select all rows of Employee then select A--->Z command. Regards ExcelDemy

[@cz3143]

@@exceldemy2006 Wouldn't that affect any of the formulas/values from other sheets?

[@exceldemy2006]

Hello @cz3143, Summary sheet extracts values based on the cell reference. If you properly select the whole range it won't create any problem. You can try this on a copy of summary sheet if you encounter any problem you can let us know. Regards ExcelDemy

[@syafiqrashid8064]

How to add half day personal leave so it count 0.5 instead of 1?

[@exceldemy2006]

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

[@FeliciaGates-w5s]

This is a great video!! THANK YOU!

[@exceldemy2006]

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

[@chipiliromiyanga967]

After coping the formula, my summary isn’t coming out as expected, need some help

[@exceldemy2006]

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

[@JohnstonsBakery]

hello, fab video. i have copied this video but none of the total leave seems to be adding to the counter. its not working on the indvidual count sheet or the summary sheet. i have triple checked the formulas. any help??

[@exceldemy2006]

Hello @JohnstonsBakery, Thank you for the compliments! We are glad you liked the video. It sounds like there might be a small issue with the formulas or how they're referencing the data. Could you check if: the ranges in the formulas are correct and cover all relevant cells based on you existing sheet. check there are no hidden rows or columns that could affect the calculation and the cell formats are set correctly (e.g., numbers instead of text) If everything seems right, feel free to share your problem in the ExcelDemy Forum with images and Excel file. Regards ExcelDemy

[@A109-w7x]

I’m not sure what I keep doing wrong but I keep getting 45320 for the AF cell instead of 29 😅

[@exceldemy2006]

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

[@TrudyLouvo]

@@exceldemy2006 I was having the same issue thank you for this clarification😊

[@exceldemy2006]

You are most welcome @TrudyLouvo. Please stay connected with us.

[@sanjeevfernandes8280]

What about the employee taking annual leave continuasely for one month or more?

[@exceldemy2006]

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]

@@exceldemy2006 I added more raws for 100 employees but the summary does not work!

[@exceldemy2006]

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

@@exceldemy2006 Super bro!! you made my work easy...👏👏👏Thank you very much....

[@exceldemy2006]

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

[@anniemikhaeil7583]

What is the formula if leave taken for 0.5day..how to calculate the SUM?

[@exceldemy2006]

Hello @anniemikhaeil7583, If you want to input 0.5 as half-day leave. You can use the following formula for Half Day (HD) column: =SUMPRODUCT(-($D9:$AH9=”HD”)*0.5) It will look for all occurrences of “HD” within the range $D9:$AH9. Each “HD” will be counted as 0.5. You will need to format the cells with 2 decimal places to properly show the decimal number otherwise it will show you rounded 1. Regards ExcelDemy

[@KATLEGOMANGATE]

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

[@exceldemy2006]

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/

[@asishkarmakar974]

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?

[@exceldemy2006]

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

[@JustinSohail]

I want to track employees who are often taking leaves on Friday and Saturday. Can anyone help me out...

[@exceldemy2006]

Hello @JustinSohail, To track employees who frequently take leaves on Fridays and Saturdays, you can customize your leave tracker by using conditional formatting to highlight these specific days. You can set up a formula to check if the leave date falls on a Friday or Saturday, then flag or count them accordingly. Additionally, you can create a summary table or pivot table to track the number of leaves taken on these days for easy monitoring. Regards ExcelDemy

[@Pearl7e]

What if we make attendance sheet from October 2024 to April 2025, what change required in summary sheet

[@exceldemy2006]

Hello @Babu8e, In the summary sheet, change the Year from 2023 to 2024 or 2025. This will automatically update the whole sheet according to the year. Download the Excel file: www.exceldemy.com/wp-content/uploads/2024/12/Create-Leave-Tracker-2025.xlsx Regards ExcelDemy

[@deepshikhadaniel2504]

Why my Feb sheet didn't auto update it's days?

[@exceldemy2006]

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

[@deepshikhadaniel2504]

Thanks it's a good explanation, thanks for your quick response to help me.

[@exceldemy2006]

You are most welcome.

[@azazkhan6252]

Just subscribing because you have replied to each cmt and solve their issue👍👍👍

[@exceldemy2006]

Dear, Thanks for subscribing! We appreciate you for joining the ExcelDemy community. We try our best to respond to comments and help whenever possible.

[@kimyenchu]

Can you track multiple years in the same workbook or is it only for 1 year?

[@exceldemy2006]

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.

[@dwightmichael9581]

I didnt get how to generate all final leave in the tracker😢

[@exceldemy2006]

Hello @dwightmichael9581, You will need to use a combined formula in the summary sheet to generate all final leave. Formula is: =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) Regards ExcelDemy

[@kimsimmons9472]

I am not seeing the formula in the description

[@exceldemy2006]

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

[@sadeel82]

There is no link in the describtion part

[@exceldemy2006]

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

[@abreakristinefaith1229]

my may got #value how to solve this? someone help

[@exceldemy2006]

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/

[@Mmiilliinn]

Hi, when I type +3 in the formula of January 31, the number is gone 😢

[@exceldemy2006]

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

[@deepshikhadaniel2504]

Hey there! Thanks again.... i got it.

[@McniksonEZSB]

Thank you so much. This really help a lot.

[@exceldemy2006]

Hello @McniksonEZSB, You are most welcome. Thanks for your appreciation. Please stay connected with us. Regards ExcelDemy

[@chipiliromiyanga967]

Want a proper explanation for the last step, I am lost.

[@exceldemy2006]

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

[@billy007ization]

MY VERSION KEEP AUTO CORRECTING + TO * I CANT FIX IT HELP 😭😭😭

[@exceldemy2006]

Hello @billy007ization, To fix Excel auto-correcting + to *, try the following: To disable AutoCorrect: 1. Go to File > Options. 2. Select Proofing, then click on AutoCorrect Options. 3. Uncheck any rule converting + to * under the "Replace text as you type" section. Ensure that the cells are formatted as General or Text, not formulas. Regards ExcelDemy

[@Tensioncreato]

Searching if my manager is watching this video or not 😂😂

[@exceldemy2006]

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

[@Tensioncreato]

@@exceldemy2006 hahahahah

[@exceldemy2006]

@Tensioncreato Glad you found that funny! 😄

[@elhacendiop6114]

Thank you so mush

[@exceldemy2006]

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

[@CoordinatorAuditNorth-East]

Thanks

[@exceldemy2006]

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

[@glo9106]

Thank you

[@hassanfazeel6853]

when i do dates 29 30 31 it comes 45686 45687 45688

[@exceldemy2006]

Hello @hassanfazeel6853, Your dates are showing as number instead of date, It's a formatting issue. Select the Date format from the Numbers group. Regards ExcelDemy

[@AparnaWilliam]

Thanks

[@exceldemy2006]

You are most welcome.