Employee Leave Manager – Step by Step User Guide

Posted on

This article will walk you through how to use the Employee Leave Manager Excel Template.

If you are new to Excel templates, please visit the Support Page where there is important information that you should know about using Excel templates.

Overview of Steps

  1. ENTER ONE-TIME SETTINGS
    • Enter Leave Types (up to 12)
    • Choose Weekend
    • Enter Holidays
    • Enter Reporting Period
    • Enter Entitlement Settings
  2. ENTER DATA
    • Enter Employees Data
    • Enter Leave Entries
  3. VIEW AUTOMATED REPORTS & DASHBOARD
    • Team Balance Report
    • Daily Report
    • Employee Report
    • Monthly Team Dashboard

Enter One-time Settings

Leave Types

Enter Leave Types in the LEAVE TYPES table.

Enter Leave Types
Enter Leave Types

You can enter up to 12 leave types. You can name the leave types as you need for your organization.

Colors are shown as they will be used in the Reports and Dashboards.

Colors are automatically assigned and cannot be modified.

Choose No for BALANCE TRACKING if you would like the specific leave type to be not tracked in Balance Reports. Examples would be Offsite meetings and training that the employee has to attend but should not be counted as leave against the allowed leave. The employee is unavailable for normal work but is still working for the company in offsite events or training sessions.

Leave the field blank if you would like to track in Balance reports.

Weekends

The template can support customized weekends. You can choose the days of the week that your company considers as weekends. Just select TRUE for those days.

Choose Weekends
Choose Weekends

When calculating the workdays, the template will exclude weekends.

If an employee is taking leave from one Monday to the next Monday (assuming Sat and Sun are weekends), it will count as 6 leave days and not 8. Sat and Sun will not be counted as leave days against the leave allowed for the employee.

Holidays

The template can support customized holidays. You can choose the days of the week that your company considers as company holidays. Just enter the date and name of the holiday.

Enter Holidays
Enter Holidays

You can enter as many holidays as needed. When you continue using template for next year, you can just keep adding holidays for next year.

When calculating the workdays, the template will exclude holidays.

If an employee is taking leave from one Monday to Wednesday (assuming Tue is a holiday) it will count as 2 leave days and not 3. Tue will not be counted as leave day against the leave allowed for the employee.

Reporting Period

The reporting period is the range of dates that are used to calculate the leave balance of employees. Just enter the Start and End Dates for your company’s reporting period.

Enter Reporting Period
Enter Reporting Period

In the above image, we have set up the reporting period to be from Jan 1, 2019 to Dec 31, 2019.

The max reporting period allowed is 12 months. If you enter a date range that is more than 12 months you will see an error message.

Enter Reporting Period - Error - More than 12 months
Enter Reporting Period – Error – More than 12 months

Report End Date cannot be before the Report Start Date. An error message will appear if that is detected.

Enter Reporting Period - Error - End Before Start
Enter Reporting Period – Error – End Before Start

These validations are present to ensure that the date inputs are correct. If there are errors, the Balance report and Employee report will not be populated.

Entitlement Setup

The template provides two options for entitlement (or quota) setup. You can set the entitlement at the total level or per leave type.

Enter Entitlement Option
Enter Entitlement Option

If you choose TOTAL, you can then enter the total entitlement of leave days.

Set Total Leave Entitlement
Set Total Leave Entitlement

In the above example, we have chosen 15 days as total allowed leave for all employees.

If you choose PER LEAVE TYPE, you can then enter the entitled leave days for each leave type.

Set Leave Entitlement per Leave Type
Set Leave Entitlement per Leave Type

The leave types that we set to NO for Balance Tracking earlier, do not need any Entitled leave and can be set to 0. In the above example, each employee is allowed 8 days of Vacation, 5 days of Sick, 2 days of Medical and so on.

If you would like to have some employees have more or less entitled leave, you can enter them in Adjustments sheet.

Enter Data

There are two data inputs you would have to enter. 1) Employees 2) Leave Entries

Enter Employee Data

In the EMPLOYEES sheet, you can enter list of employees in your organization.

Enter Employees Data
Enter Employees Data

Employee Name and Start Date are Required Fields. End Date should be used if the employee has left the company.

Employee Name should be unique. Do not repeat the same employee name twice in this table.

Other columns in blue fill can be customized as needed. You can rename the columns and use to store any information you want to store about the employees.

Enter Leave Entries

In the LEAVE sheet, you can enter list of leave entries for all employees in your organization.

Enter Employee Leave Data in Table
Enter Employee Leave Data in Table

For each leave entry, enter the Employee Name, Start of leave, End of leave, Leave Type and Daily Leave Value.

If an employee is taking a single day leave, enter the END DATE same as START DATE. Do not leave the END DATE blank or empty.

DAILY LEAVE VALUE can be partial days as well. 0.5 would represent half a day.

If an employee is taking multiple day leave, for example, from Aug 5, 2019 to Aug 8, 2019 and Daily Leave Value is 0.5, that would mean 4 days of 0.5 each, resulting in 2 whole days of leave taken. Do not enter the total leave value in the DAILY LEAVE VALUE field.

DAILY LEAVE VALUE can be entered as negative value if an employee is working on a holiday or a weekend. Let’s call it a ‘Make up Day’. The template can support such scenarios.

Validations

There are some validations put in place to highlight data entry errors in different colors.

Leave Entries - Validations
Leave Entries – Validations
  • If you are entering negative leave value (make up day) it needs to be for a holiday or a weekend.
  • Leave Start Date cannot be after the Employee has left the company.
  • Leave Start Date cannot be before the Employee has joined the company.
  • Leave End Date cannot be before Leave Start Date.

Any leave Entries with these errors will be excluded from reporting.

Custom Columns

Just like in Employees table , we can add custom columns to the Leave table.

Enter Employee Leave Data - Custom columns
Enter Employee Leave Data – Custom columns

You can enter new columns to the end of the table to record information about leave. However, do not insert any columns in between.

That completes our data input for the template. You are now ready to view the automated output from the template.

View Automated Reports & Dashboards

Balance Report

Balance report lists the employees along with their leave balance information at total as well as each leave type.

Team Balance Report
Team Balance Report

There are 3 controls available in the Balance report to customize it.

  1. Display
    • You can choose to display one of three measures on the report.
    • Entitled: Number of days of leave entitled; Used: Number of days of leave used ; Balance: Number of days of leave available as balance
  2. Filter
    • You can choose a Filter field from the drop down. The list of fields in Employees table are available for you to filter by, including any custom columns you created.
    • You can then enter a Filter value. For example, if Filter field was ‘Department’ and filter value was ‘Finance’, the report will display only employees in Finance Department
    • Please note that any employee who is not employed during the reporting period will automatically not be displayed in this report.
  3. Page #
    • The report shows 100 employees at a time. You can enter 2 as Page number to see employees 101 to 200.

Negative balances will be shown in red fill.

Daily Report

Daily report lists all the leave entries for a specific date.

Daily Report
Daily Report

 

Here, you can type in any date and the report will pull the leave entries for that specific date.

The report also summarize the total leave by leave type on the left.

The last column on display is a custom column where you can replace it with any column from Leave table.

Employee Report

Employee report is a printable report that will display the summary and details of leave and balance for a specific employee.

This report has two pages. Page 1 displays the summary by leave type for the entire reporting period at the top and a calendar view in the bottom showing leave information.

Employee Report Page 1 with Calendar
Employee Report Page 1 with Calendar

 

Page 2 displays the monthly totals by leave type and the summary for the entire reporting period. It also displays the Worked Days calculated as (Total Workable Days in the Month – Leave taken by each employee).

Employee Report Page 2 - Monthly Totals by Leave Type
Employee Report Page 2 – Monthly Totals by Leave Type

Monthly Team Dashboard

Team Dashboard displays the leave information of all employees in one specific month.

Page 1 displays the calendar view showing leave of each employee, where leave types are shown in different colors.

Monthly Team Dashboard Page 1 with Calendar view
Monthly Team Dashboard Page 1 with Calendar view

Page 2 shows the totals for each employee by leave type. It also displays the overall total leave and worked days.

Monthly Team Dashboard Page 2 with totals by Leave Types
Monthly Team Dashboard Page 2 with totals by Leave Types

There are 3 controls available in the Team Dashboard to customize it.

  1. Custom Column
    • As the second column next to Employee Name, you can choose any column from Employees table. In the above example, we have chosen Department.
  2. Filter
    • You can choose a Filter field from the drop down. The list of fields in Employees table are available for you to filter by, including any custom columns you created.
    • You can then enter a Filter value. For example, if Filter field was ‘Department’ and filter value was ‘Finance’, the report will display only employees in Finance Department
      • Please note that any employee who is not employed during the Month chosen will automatically not be displayed in this report.
  3. Page #
    • The report shows 100 employees at a time. You can enter 2 as Page number to see employees 101 to 200.
Leave a Reply

Your email address will not be published. Required fields are marked *