This template enables users to track the leave that is applied for by all employees and to ensure that there is always adequate staff in each department or for every defined role. All leave that is applied for needs to be recorded on an individual employee basis and is then displayed visually on the Summary sheet. The Summary sheet can also be filtered by department or role to determine whether there are always sufficient resources available.
The template includes the following sheets:
Setup - enter the leave tracking start date and specify whether Fridays, Saturdays, Sundays and public holidays should be included as work days for leave purposes. The 60 day leave cycle which is included on the Summary sheet can be rolled forward or back by simply changing the start date on this sheet. The public holidays that need to be taken into account needs to be maintained in the list on this sheet and the maximum & minimum employee levels are only used for conditional formatting purposes to highlight days when these thresholds do not apply.
Summary - A complete list of employee codes and names should be added to this sheet. Employees can also be assigned to departments or roles to display leave applications on a departmental or role level. The calculations & formatting from column D onwards are all automated and based on the template setup and the leave dates that need to be entered on the Tracking sheet. Only the columns with yellow column headings require user input.
Tracking - Enter the leave application dates of all employees on this sheet. The data that is entered on this sheet is used to update the leave tracking summary on the Summary sheet. The template accommodates an unlimited number of entries & multiple entries for each employee - you therefore do not need to start with a new version of the template for each new financial period.
Template Set-up
The first step in customizing the template for your business is to specify the tracking start date in cell B5 on the Setup sheet. The date that is entered in this cell determines the 60 day period that is included on the Summary sheet. You can therefore roll the reporting period on the Summary sheet forward or back by simply changing the tracking start date on the Setup sheet.
Note: The template accommodates an unlimited number of employees and an unlimited number of leave dates. You therefore do not need to start a new version of the template for every new financial period and any tracking start date can therefore be used.
The next step in the template setup is to specify whether Fridays, Saturdays, Sundays and public holidays need to be included as work days for leave calculation purposes. The list boxes in cell B8 to cell B11 on the Setup sheet can be used for this purpose by simply selecting "Yes" if the appropriate days are to be included and "No" if the appropriate days are to be excluded.
The selection will automatically be updated to the Summary sheet. If the appropriate day selection is not to be included, any leave period that includes this day selection will not be indicated on the Summary sheet and will also not be counted as work days on the Tracking sheet.
If you want to use a maximum and minimum employee threshold, these values can be entered in cells E9 and E11 on the Setup sheet. These settings only affect the formatting on the Summary sheet - when the maximum employee setting is exceeded, the number of employees in row 2 on the Summary sheet for the appropriate day will be highlighted in red.
Similarly, if the number of employees in row 2 on the Summary sheet is less than the minimum number of employees setting on the Setup sheet, the value for the appropriate day will be highlighted in red.
Note: If you do not want to use the minimum and maximum values, you can simply enter values which will never be applicable. For instance, enter 999 as the maximum employee setting if you will never realistically employ this many employees and enter 0 in the minimum setting to effectively turn the setting off.
The template also requires users to enter a list of public holidays on the Setup sheet. If any date matches a public holiday date, the appropriate column on the Summary sheet will be highlighted in light blue. If public holidays are included as work days, including a date in this list will also affect whether leave is displayed in the column which relates to the specific date.
Note: A new version of the template does not need to be created for every new financial year. You can therefore simply add the public holidays for the appropriate calendar year at the beginning of a new year to have them displayed on the Summary sheet. If you are only using the template for future planning, the public holidays for the previous calendar year can simply be replaced by the public holiday dates for the new calendar year.
Days can be added to the public holiday list on the Setup sheet by simply inserting a new row anywhere between the first date and the "End of list" entry. All the dates that are added are automatically included in the calculations on the Summary and Tracking sheets.
Adding Employees
All employees need to be added to the template in order to include their leave dates on the Summary sheet. The Summary sheet has the following user input columns (columns with yellow column headings):
Employee Code - a unique employee code should be entered for each employee in column A. We recommend that you use the employee number as per your payroll system for this purpose. If your payroll system does not include unique employee numbers, you can use any unique numbering system.
Employee Name - enter the name of the employee in column B.
Department / Role - enter the department or role of each employee in this column. The template can be used for individual departments or if it is a small business, for individual roles.
It is important to consider the department or role column carefully - you basically want to use this column in order to group employees together in order to ascertain whether sufficient resources are available when a lot of staff members are applying for leave over the same period. The template enables users to filter the Summary sheet by department or role in order to display the leave applications for the selected department or role only. You therefore need to use a unique department or role for each business function where a minimum number of employees always need to be in attendance.
Note: All the columns on the Summary sheet have been included in an Excel table. This feature is extremely useful when entering data in a table format because the formulas that are included in calculated columns (the columns with a light blue column heading) are automatically copied when new rows are inserted into the table or when data is entered into the first blank row below the table. You can therefore add a new employee to the Summary sheet by simply entering a new employee code in the first blank cell in column A - the table will then automatically be extended to include the new employee.
Note: All the column headings on the Summary sheet contain a filter selection arrow - this feature indicates that a Filter has been applied to the table data. This Excel feature is very useful when you need to filter the data that forms part of a table based on one of the filter criteria that are available after clicking the selection arrow. For the purpose of this template, it is especially useful to filter the Summary sheet by the department / role column in order to only analyse the leave applications for a specific department or role.
Note: The calculations at the top of the Summary sheet are all automatically updated when the sheet is filtered. These calculations and the formatting on the Summary sheet are covered later on in these instructions under the Template Calculations & Formatting section.
Recording Leave Application Dates
When an employee applies for leave, the employee code of the employee, date from which leave will be taken and the date to which leave will be taken need to be recorded on the Tracking sheet. This sheet includes the following user input columns:
Employee Code - select the appropriate employee code from the list box in column A. All employee codes that have been included on the Summary sheet are included in the list box. For new employees, you need to add the appropriate employee code to the Summary sheet before it will be available for selection from this list box.
From Date - enter the first day of the leave application date range.
To Date - enter the last day of the leave application date range.
Note: The from and to dates do not need to be workdays and the date range does not need to only include workdays - the template automatically only takes workdays into account based on the day settings that are included on the Setup sheet.
The Tracking sheet also includes the following calculated columns (with light blue column headings):
Work Days - the number of workdays that are included in the date range that is formed by the From and To Dates in columns B and C are displayed in this column.
Error Code - this column will contain an error code if there is a problem with the input in any of the user input columns. All the error codes that are displayed in this column should therefore be corrected in order to ensure that all the template calculations remain accurate. Refer to the Error Codes section of these instructions for more information about the reasons for an error code and how to resolve it.
Note: All the columns on the Tracking sheet have been included in an Excel table. This feature is extremely useful when entering data in a table format because the formulas that are included in the calculated columns (the columns with light blue column headings) are automatically copied when new rows are inserted into the table or when data is entered into the first blank row below the table. You can therefore add a new entry to the Tracking sheet by simply entering a new employee code in the first blank cell in column A - the table will then automatically be extended to include the new entry.
All the leave tracking dates that are added to the Tracking sheet are automatically included in the calculations and formatting on the Summary sheet. Multiple entries can be added for each employee and there is no limit on the number of entries which can be included on the Tracking sheet.
Template Calculations & Formatting
All the leave date ranges that are added to the Tracking sheet will be displayed next to the employee code, employee name and department / role on the Summary sheet. The display is in the form of orange highlighting which will be included for all days where the employee will be on leave.
The orange highlighting will only be applied to work days and the classification of a work day depends on the settings which have been applied to the Setup sheet. All public holidays will be highlighted in light blue and all weekend days will be highlighted in grey. The orange highlighting will therefore clearly indicate which employees will be on leave on any day that is included in the date range on the Summary sheet.
Note: The date range that is included from column D on the Summary sheet starts on the Tracking Start Date which has been specified on the Setup sheet and includes a period of 60 days.
If you want to extend the 60 day default period, we suggest completing the following steps in the sequence in which they are provided:
- Select the cell with D60 as the column heading.
- Insert the required number of additional columns - one column for each day that you want to add to the 60 day period. Remember that the template automatically updates when you change the Tracking Start Date on the Setup sheet and that it is therefore very easy to roll the tracking period forward or back. The number of days that you include on the Summary sheet should therefore only be the window in which you would like to review leave and not an entire period to monitor.
- Select the two column headings immediately before the columns that you inserted (these columns will have data above the column headings at this point).
- Hover your mouse over the bottom right corner of the second column heading (mouse cursor should change to a +) then left click & drag the mouse to the last column heading. All the column headings should now be in a numerical sequence of D60, D61, D62, etc.
- Select the first cell below the D59 column heading and copy this cell to all the cells below the column headings which have been added to the sheet by inserting new columns. You should copy from the first row up to the end of the table (last row that contains data).
- Select the cells in the first 5 rows above the D59 column heading and copy this cell range to all the cells above the column headings which do not contain data. Before pasting the copied data, select the Paste Special feature and elect to only paste formulas. This will ensure that the conditional formatting rules that are used to display the leave dates are not duplicated unnecessarily.
Note: The main purpose of completing the above steps in this sequence is to ensure that the conditional formatting rules that are used to indicate leave, remain accurate.
Note: If you want reduce the number of days that are included in the tracking period, you can simply select the appropriate number of columns and delete them. These columns need to be selected from the end of the 60 day date range.
The Summary sheet also includes information above the column headings which are used in the conditional formatting rules that highlight leave days. The following information is included:
Workday Status - this row indicates whether the appropriate day is workday or not. A "Y" is displayed for a workday and a "N" for off days. The classification of workdays depends largely on the settings that have been included on the Setup sheet.
Number of Employees Present - this calculation indicates the total number of employees that are NOT on leave on the appropriate day.
Public Holiday Indicator - indicates whether the appropriate day is a public holiday. "Y" for public holiday and "N" if not applicable. Public holidays are defined by including the appropriate dates in the list on the Setup sheet.
Weekday - displays the weekday name for the appropriate date.
Date - displays the date. The first day that is included in the 60 day tracking range is based on the tracking start date which is specified on the Setup sheet. All subsequent dates are determined by adding a day to this date.
Note: All the calculations in the rows above the column headings are updated automatically when you filter a column. This is especially useful if you filter the department / role column based on a specific department or role selection. All the calculations will be updated automatically and the total number of employees that are present will be adjusted automatically.
Error Codes
The following error codes may result from inaccurate input on the Tracking sheet and will be displayed in the Error Code column.
- E1 - this error code means that from date that has been specified in the appropriate row is before the to date. Fix the error by entering the correct date range.
- E2 - this error code means that the employee code that has been selected in column A on the Tracking sheet is invalid. All the employee codes that have been entered on the Summary sheet will be included in the list boxes in column A and the error can therefore be rectified by simply selecting a valid employee code from the list box. New employee codes must be created on the Summary sheet before being available for selection.
Note: Input errors may result in inaccurate template calculations and it is therefore imperative that all errors are resolved before reviewing the leave data on the Summary sheet.
Troubleshooting Formatting Errors
The accuracy of the template is dependent on the conditional formatting rules that have been implemented in order to display when employees are on leave and to indicate weekends and public holidays. If you follow the template instructions to the letter, these rules will always function as they should.
In some instances, you may however inadvertently break some of these rules mainly by way of not inserting data as per our instructions or copying data into the template in such a way that some of the conditional formatting rules are replaced.
If you therefore find that an employee's leave is not indicated correctly on the Summary sheet, we suggest completing the following steps before you contact our Support department for assistance:
- Go to the Tracking sheet and check whether the date inputs for the appropriate employee are correct and that both the from and to dates have been entered or copied as valid dates (no error code).
- Select the Conditional Formatting feature from the Home tab on the ribbon. Then select the Manage Rules feature before selecting the This Worksheet option at the top of the window.
- There should only be 6 unique conditional formatting rules - two that are applied to row 2 and four that are applied to all the cells that form part of the Excel table on the Summary sheet. If there are more than 6 rules, it probably means that some of the default rules have been duplicated.
- Identify the rules that are duplicates of the default rules (which start from a cell range containing cell D7) - it will basically be the same rules but just applied to different cell ranges. Delete all the duplicated rules.
- Determine the location of the first cell in column D below the column heading (should be cell D7 if no columns or rows were inserted before column D and row 7). Then determine the location of the last cell in the Excel table on the Summary sheet. The last cell should be in the last row that contains data and in the last column with a column heading. There should also be a small triangle in the bottom right corner of the cell. Make a note of the cell location (something like cell BK18).
- Now return to the Manage Rules feature and replace the cell range of the 4 rules that do not refer to row 2 with the cell range between the first and last cells that form part of the table as per the previous two steps. Then click OK to save your changes.
- Re-open the Name Manager feature and review the two rules that are applied to row 2. These two rules only affect the use of minimum & maximum staff totals and can be deleted if you are not using these settings. If the red highlighting is missing from some cells in row 2, check that all columns form part of the cell range to which these rules are applied.
Note: Existing conditional formatting rules that are not duplicated should not be deleted otherwise the template will not function as it has been designed.
Note: If you want to change the default colours that are being used for leave days, public holidays or weekends, open the Conditional Formatting - Manage Rules feature (Home tab on ribbon), double click the appropriate rule (based on the colour that is displayed next to the rule), click the Format feature and change the formatting accordingly. Background colours are applied with the Fill formatting features.
