Employee Absence tracking Template for Microsoft Excel

Microsoft ExcelLeave a Comment on Employee Absence tracking Template for Microsoft Excel

Employee Absence tracking Template for Microsoft Excel

From time to time, every employee will be absent from work. Companies must deal with sick leave in some form or another for a variety of reasons. Absence might range from a minor cold that clears up in a few days to a lengthy sick leave that has a significant influence on a team’s performance and deliveries. In other circumstances, absences may be due to vacations, courses, or other personal reasons. That is entirely natural and unavoidable.

Employee Absence tracking template

Visit our online store to download the absence tracking template for Microsoft Excel.

You must accurately manage absence and attendance in order to establish good work schedules and deliveries for the forthcoming periods. A full-fledged integrated software solution is usually not required for smaller organizations, activities, and projects. A basic Excel template can do just fine.

Employee absence tracking template in Microsoft Excel.
Employee absence tracking template in Microsoft Excel.

Coragi has developed a simple and easy to use employee absence tracking template in Microsoft Excel to visually manage staff holidays, course attendances and sickness. It is a visual tracker tool which helps you to stay on top of your employee’s absences.

Keeping track of absence is a key success factor when running a business or a one-off project. The success of a project is highly dependent on key resources always performing at their best to meet predefined milestones and deadlines. As an employer or project leader, you have a responsibility to follow up your employees and team members who are absent, and to prevent absence due to stress.

If an employee is sick or unable to be present at work for other reasons, then there is an absence. Absence can be divided into several variants:

  • Leave: Sick leave is paid time off from work that workers can use to stay home to address their health needs without losing pay. Some policies also allow paid sick time to be used to care for sick family members.
  • Vacation: A holiday or vacation is leisure time away from work or duty which is devoted to rest or pleasure. If you manage a support organization, you need to ensure that you have people available at all times, or stand-by in case emergencies happen. This is especially important during typical low-activity holiday seasons where most employees would prefer to spend time with their families and friends.
  • Course: Staying up to date on new technology and trends is vital for the survival of a growing business. Although courses and training sessions are mandatory, they could potentially cause staffing challenges up the road. Any time not spent on growing the business must be tracked.

Why should you use the Absence tracking template?

The Coragi Absence Template for Microsoft Excel is an easy to use and cheap alternative for absence tracking, that can be re-used over and over again. If you need to keep track of your team members’ holidays in your ongoing projects; then this is the template for you. Managing annual leave is a must for teams of all sizes. The template has been used both by projects and line managers to track the employees’ time off and unplanned absences. Once you start using a reliable and transparent absence tracker the frequency of unnecessary absences might drop, and it is far easier to plan ahead.

The employee time tracking template contains a single worksheet for the entire period. Passed periods can easily be hidden from the current view by using the standard functionality for hiding columns in a Excel sheet. The main purpose of the template is to get a visual presentation of the team’s absence, and not to calculate the number of days or reduction in pay. Anything related to salary should be handled by a standard HR system.

If your company is running the online version of Microsoft Office 365, the Excel template can be shared in SharePoint or Teams with all the team members. The web- and desktop clients of Microsoft Excel allow multiple users to edit a single document at the same time.

Let’s go through some of the advantages of managing employee leave with an Excel template:

  • Shared with everyone: Everyone can see when their coworkers are in or out of the office.
  • Easy to understand: The generated calendar provides a clear visual presentation of who is unavailable on specific dates.
  • Easy to use: A simple coloring scheme shows when the team members are away. A right-click menu allows easy access to the different types of absence. Microsoft Excel is a user-friendly program that is simple to learn. Anyone who is a little bit familiar with Microsoft Office will have no trouble entering their vacations and leaves.

Privacy

To protect the privacy of every employee and team member, we have not included any detailed description field of the type of leave. A descriptive column can easily be added to the spreadsheet. However, please make sure that this information isn’t in a spreadsheet that’s open to anyone, as it could be considered sensitive.

How to use the template

  1. To use this template you must first purchase a valid license of Microsoft Office, and then install the software on your computer. The template can also be used in the online version of Microsoft Office 365. This template works with Microsoft Excel 2007 and newer versions.
  1. The next step is to download the template and save it locally on your computer. Press the Download now button below to download the file. Save the file to a location on your hard drive.

Employee Absence tracking template

Visit our online store to download the absence tracking template for Microsoft Excel.

  1. Now, open the template by either double-clicking on the filename of the downloaded file, or starting Microsoft Excel from the Start-menu, and then selecting File > Open.

The first tab in the Excel Template contains a short user guide on hwo to use the template. There are some important points you need to be aware of.

  • Make a copy: We hope that you will re-use this template over and over again. Please make sure that you have a copy of the original template stored somewhere on your computer or in the cloud for later use.
  • File format: If you save the template as a new file (File > Save As), make sure that you always save the document as the .xlsm (Excel Macro-Enabled Workbook) file extension. The calendar in the template is generated using VBA macros (Visual Basic for Applications). A macro is a set of instructions or commands to be executed to create the calendar. If you save the template with a .xlsx extension, then the macros are removed from the document and it will not work. Macros are also supported by the old .xls file format, but there is a limitation in the number of columns (maximum 256) which could make the macros fail. The template adds one column for each day, and will soon fall short when generating a calendar for a full year.
  • Enable macros: As mentioned, this template uses VBA macros. When you open the document, you will see a yellow security warning like the one below. You must press the Enable Content button, or else the template will not work. If for some reason you don’t see the security warning, and the macros don’t run, you need to enable macros manually. To do this, press File > Options > Trust Center > Trust Center Settings > Message Bar > Show the Message Bar in all applications when active content, such as ActiveX controls and macros, has been blocked. Then close, and re-open the document.
Security warning for macros

Configuration of the Absence tracking template

The template is a macro enabled spreadsheet which generates a nice-looking calendar with user-friendly month- and day names, week numbers and dates. In order to generate the correct period, and the correct week numbers, we ask you to fill in a few settings. As previously stated, making a copy of the original template before continuing with the setup is a smart idea.

Configuration of the absence tracking template.
Configuration of the absence tracking template.

The configuration contains the following fields:

The configuration contains the following fields:

  1. Name

    Specify the name of your company, department, project or event that this absence tracker will be used for.

  2. Year

    You can specify the year in which the absence planner template will start. Since the template is generated dynamically any year is supported. The absence planner template is defaulted to the year 2021, but can be changed to your preferred period. How many years do you want your absence tracker to cater for?

  3. First day of the Week

    The absence template is used all over the world. If you live in the USA, the first day of the week is Sunday. In Europe the first day of the week is normally Monday (ISO 8601).

  4. First Week of the Year

    Different countries calculate the week number differently. There are two options: “First 4 days” and “First January 1st”. In the USA you would choose “First January 1st”, while in Europe the ISO 8601 standard is used, i.e. “First 4 days”. For more information about week numbers, please visit Wikipedia.

  5. View

    You can specify if you want to show all days of the week, or only the weekdays (i.e. Monday to Friday).

  6. Number of People

    Any number of employees can be specified. Whether you have 10, 20 or 50 employees this template is for you. However, the generation of the document will take longer time the more people (rows) you add.

  7. Create Calendar

    Press the Create Calendar button when you are ready to generate the absence calendar. You have to wait a few seconds or minutes for the macros to complete the work. When the document is ready, a message box is displayed.

The Calendar

The generated calendar includes the following information (from the top):

  • Week number
  • Month name and year
  • Day number
  • A three letter day name
Generated absence calendar.
Generated absence calendar.

Non-working days are displayed as a light gray cell in the template. This is for display purposes only; they are given no special treatment. The non-working days are only visible if you have chosen to generate a calender for the whole week (including weekends) – and not only for the working days.

We have added 4 buttons with different colors to differensiate between the different types of absence.

  1. Normal: The white button represents normal working hours. It means that the employee is a work.
  2. Vacation: The red button shows when the employee is on vacation. This may include both paid and unpaid vacation.
  3. Leave: The purple button shows when the employee is on a sick-, maternity- or patternity leave, or any other accepted type of leave. This may include both paid and unpaid leave.
  4. Course: The green button shows when the employee is attending an approved internal or external course.

To specify any type of absence, mark the applicable cells with your mouse by clicking the left mouse button, and then press any of the colored buttons. Instead of clicking on a button, you can also right-click with your mouse, and then select the applicable leave type.

Right-click menu to select the type of absence.
Right-click menu to select the type of absence.

How did we do?

We would love to hear how well our Excel template worked for you and how we could improve. Please write you feedback in the comments field below. Also check out our video explaining how to use the template on YouTube.

AppSumo badge

Related


Ulf Emsoy has long working experience in project management, software development and supply chain management.

Leave a Reply

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

Back To Top


Subscribe to our newsletter