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.

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.

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, but must be opened in the Desktop App when editing the file (due to the use of macros). This template works with Microsoft Excel 2007 and newer versions.

2. 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.

3. 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.

• Open in Desktop App: If you have upload the template to Microsoft Sharepoint or Microsoft Teams, the Excel file must always be opened in the Desktop App. This is because the Online version of Excel running in the Web Browser does not support macros. To enable Macros, please press “Open in Desktop App” at the top of your workbook inside the Web Browser (Microsoft Excel Online, M365). If you can’t see the button, there should be a search bar along the top of your workbook. In that search bar, type “open“, and then select “Open in Desktop App“.

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.

The configuration contains the following fields:

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

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.

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.

Related

• Download the Employee Absence tracking template
• Attendance tracker template
• AppSumo deals: $10 discount when you make your first purchase
• ImagePrint – Easy Printing of Photos
• Get Microsoft Excel

---