Business & Annual Leave Tracker
Business & Annual Leave Tracker is Excel based an automated tool to track and generate leave reports for a team or a function. This tracker is fully customizable and you can make the changes according to your business required. It supports 15 employees and 4 types of leaves. It’s totally dynamic in nature and you can change the Holidays list, Weekend details, Employee names and Leave types as per your requirement. It’s generate automatic report which shows all the dates against Leave type, Employee name and month.
In this post, we will discuss how to customize this tool as per your requirement and use the same. This tool is password protected to protect the sheet as well as VBA code so that it doesn’t get deleted or changed accidentally by user. You can unprotect the sheets, workbook and VBA code and make the required changes, if you want. Workbook and Worksheets password is 4 times zero (0000) and password for VBA is abc@1234.
Let’s discuss the structure of Leave tracker and how to customize this tool as per your requirement.
There are five different sheets have been used in this tracker. Each and every sheet has different functions and we are utilizing those sheet to automate tracker.
- Home Sheet – This sheet is providing User Interface to users so that user can open the form, apply leave, jump to database and see the summary.
- Support Sheet – This sheet contains 4 different types of tables named 1. Holidays List, 2. Weekend details, 3. Employee Name and 4. Leave Types.
Table 1 – Holidays List : Please update the name of holidays in Column A and respective dates in Column B from row number 2. There is no limitation of Public Holidays in this table. You can mention as many leaves as you want or applicable in office/company. Public Holidays will be get excluded when user will apply the leaves.
Table 2 – Weekend Details: This tool supports only two Weekly off in a week. You can change the existing days i.e. Saturday and Sunday. If Saturday and Sunday are not the weekly off in your office or company then replace the Saturday and Sunday with other days. Make sure you are not changing the reference of cells and updating only two days as Weekly Off. If you think that you need to update more than two days leave then you will have to make the changes in VBA code so that tool can exclude the Week Offs from the leave date range which user will apply through form.
Table 3 – Employees Name: This tool supports only 15 employees by default. Please replace the employees name in cell range $H$3:$H$17 in Column H. Don’t change the value of cell $H$2 and reference of the column. Once you will update all the 15 employees in the list then this tool will automatically update the drop-down and all the reports.
Table 4 – Leave Details: This tool only supports 4 types of leave. You can make the changes in default leaves mentioned in range $L$3:$L$6. Don’t make any changes in cell $L$2. If you think that you need more leave then you will have to make changes in VBA code and formula references for ‘Summary’ and ‘Raw Summary’ sheets.
Once you will update all the tables with valid details and without changing references or adding rows/columns then tool will pick updated details automatically in form as will as reports.
How to use the Business & Annual Leave Tracker?