PeopleMuster

Absence tracker template for sickness and unplanned absence

An absence tracker records the days nobody planned for: sickness, no-shows, late starts and people sent home early. Planned leave belongs in the leave tracker. Keeping the two apart is what lets you spot a pattern, such as three Mondays in one month, before it becomes a problem.

Use it from the first unplanned absence of the year, logged the same day by the manager or HR. It suits a team that records attendance by hand or wants a monthly view on top of its attendance data. Run a return-to-work chat after every absence and note it in the last column.

TAB 1: ABSENCE LOG (name this sheet Log; one row per absence, logged the same day)
Date | Employee | Department | Absence type | Days lost | Reported by | Reported at | Reason given | Return-to-work chat held
[DATE] | [EMPLOYEE NAME] | [DEPARTMENT] | Sickness | 1 | Employee | [TIME REPORTED] | [SHORT REASON] | Yes, [CHAT DATE]
[DATE] | [EMPLOYEE NAME] | [DEPARTMENT] | Partial day | 0.5 | Manager | [TIME REPORTED] | [SHORT REASON] | Yes, [CHAT DATE]
[DATE] | [EMPLOYEE NAME] | [DEPARTMENT] | No-show | 1 | Manager | [TIME REPORTED] | Not reported | No
[DATE] | [EMPLOYEE NAME] | [DEPARTMENT] | Late start | [HOURS AS FRACTION OF DAY] | Employee | [TIME REPORTED] | [SHORT REASON] | Not needed

Absence types: Sickness | Partial day | No-show | Late start | Family emergency | [OTHER TYPE]


TAB 2: MONTHLY SUMMARY PER PERSON
Employee | Month | Absence spells | Days lost | Mondays or Fridays | Return-to-work chats | Absence score
[EMPLOYEE NAME] | [MONTH] | =COUNTIFS(Log!B:B,A2,Log!A:A,">="&[MONTH START],Log!A:A,"<="&[MONTH END]) | =SUMIFS(Log!E:E,Log!B:B,A2,Log!A:A,">="&[MONTH START],Log!A:A,"<="&[MONTH END]) | [MON OR FRI COUNT] | [CHAT COUNT] | =C2*C2*D2


TAB 3: TEAM VIEW
Department | Headcount | Days lost this month | Absence rate
[DEPARTMENT] | [HEADCOUNT] | [DAYS LOST] | =C2/(B2*[WORKING DAYS IN MONTH])


TRIGGER POINTS (AGREE THESE BEFORE YOU USE THE TRACKER)
[SPELL TRIGGER] or more absence spells in [ROLLING PERIOD] months: manager check-in
Absence score of [SCORE TRIGGER] or more: HR review meeting
Any no-show: same-day call from the manager

What to put in each blank

[SHORT REASON]
Write what the employee told you in three or four words, such as 'stomach bug'. Keep medical detail out of a shared sheet.
[HOURS AS FRACTION OF DAY]
Divide the hours missed by the standard day. Two hours late on an eight-hour day is 0.25.
[MON OR FRI COUNT]
Count spells that start on the first or last working day of the week. On its own it proves nothing; repeated, it starts a conversation.
[SPELL TRIGGER]
Pick a number your policy already uses, such as three spells in six months. Publish it, so nobody is surprised by a review.
[SCORE TRIGGER]
Set a threshold for the absence score in Tab 2. The score squares the number of spells, so many short absences count more than one long one.
[WORKING DAYS IN MONTH]
Enter the working days in the month after company holidays. The absence rate is days lost divided by days that could have been worked.

Before you send it

  • Log absences on the day. A tracker filled in at month-end relies on memory, and memory is exactly what a disputed absence turns on.
  • Count spells as well as days. One week of flu is a single spell; five separate Mondays are five, and that is the pattern the score in Tab 2 brings to the top.
  • Hold a short return-to-work chat after every absence, even a single day. The chat is where you learn about a problem at home or at work early.
  • Limit who can open the log. Absence reasons are personal, so share the monthly summary with managers and keep the reasons with HR.

PeopleMuster marks each day Present, Absent, Partial or Leave from the fingerprint terminal or browser check-in, with late minutes on every record

Questions people ask about this letter

What is an absence tracker?

An absence tracker is a log of unplanned time off, such as sickness, no-shows and partial days, with a monthly summary per person. Planned leave lives in a separate leave tracker, so the absence log shows patterns that a balance never would.

How do I track employee absence in Excel?

Keep one row per absence in a log tab, then summarise it per person each month with COUNTIFS for spells and SUMIFS for days lost. The template above has both formulas and a team absence rate.

How do you calculate an absence rate?

Divide the days lost by the days that could have been worked, then multiply by 100. A team of 10 on a 20-day month that loses 6 days has an absence rate of 3%.

What does the absence score measure?

The absence score multiplies the number of spells by itself and then by the days lost. Five one-day absences score 125, while one five-day absence scores 5. Short, frequent absences stand out, which is usually what a manager needs to see.

Other templates