Annual Leave Tracker

April 19, 2019

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.

Summary Sheet -This sheet contains the report view of leaves. It shows leave dates against Employee Name, Leave Type and Months. It also shows the aggregation of leaves for Month and Quarter.

RawSummary Sheet – By default, this sheet is not visible and very hidden in VBE. This sheet is only for internal usage of this tool to prepare reports in ‘Summary’ sheet.. VBA code generates the leave dates against Employee Names, Leave Types and Months in this sheet. In ‘Summary’ sheet, we retrieve the concatenated dates from ‘RawSummary’ sheet using lookup function to show the report. You should only refer this sheet when you need to add more than 15 employees and/or more than 4 leave types in Support sheet.

Database Sheet – This sheet acts like a database to hold all the leave details applied by user. You can either utilize Form to apply leaves or make manual entry in this sheet directly. Make sure you are updating the correct details while adding manual entries in this sheet. If there will be any mistake in Employee Name, Leave Type and other details then tool will not pick the details in report view. I would strongly recommend you to use the form to apply leave (we will discuss how to use the form to apply leaves in later section of this page.)