Log each absence once — as an occasion, not a pile of days — and the workbook calculates days lost, absence rate and the Bradford Factor for every employee. Planned leave is excluded on purpose, so the numbers mean something. Excel and Google Sheets, no email required.
Free download. No email required.
| Employee | Occasions | Days Lost | Absence Rate | Bradford Factor | Flag |
|---|---|---|---|---|---|
| Ben Cooper | 6 | 8 | 3.1% | 288 | Review — above trigger |
| Aisha Khan | 1 | 5 | 1.9% | 5 | |
| Carla Mendez | 3 | 4 | 1.5% | 36 | |
| David Osei | 2 | 3 | 1.4% | 12 | |
| Elena Rossi | 1 | 1 | 0.4% | 1 |
Preview of the template — the download includes formulas and all tabs.
Occasions, days lost, absence rate and Bradford Factor per person, all derived from the log. Set your own trigger score at the top and anyone above it is flagged. This is the tab that turns a list of sick days into something you can act on.
Best for: monthly or quarterly absence reviews, and preparing for a return-to-work conversation.
| Employee | Occasions | Days Lost | Absence Rate | Bradford Factor | Flag |
|---|---|---|---|---|---|
| Ben Cooper | 6 | 8 | 3.1% | 288 | Review — above trigger |
| Aisha Khan | 1 | 5 | 1.9% | 5 | |
| Carla Mendez | 3 | 4 | 1.5% | 36 | |
| David Osei | 2 | 3 | 1.4% | 12 | |
| Elena Rossi | 1 | 1 | 0.4% | 1 |
One row per occasion — a three-day flu is one row, not three. Working days between the dates are counted with NETWORKDAYS. There is a column for the reason given and one for whether a return-to-work discussion happened, which is the record you will want if a pattern ever becomes formal.
Best for: recording absences the day they happen, with an audit trail.
| Employee | Type | First Day | Last Day | Working Days | Reason Given |
|---|---|---|---|---|---|
| Ben Cooper | Sickness | 12 Jan | 12 Jan | 1 | Migraine |
| Ben Cooper | Sickness | 09 Feb | 10 Feb | 2 | Stomach bug |
| Ben Cooper | Unauthorised | 02 Mar | 02 Mar | 1 | No contact |
| Aisha Khan | Sickness | 16 Feb | 20 Feb | 5 | Flu |
| Carla Mendez | Medical | 08 Apr | 08 Apr | 1 | Hospital appointment |
Days lost per person per month. Annual totals hide the thing that matters — someone at two days every single month is a different problem from someone who lost eight days to one operation, even though both total eight.
Best for: spotting when a pattern started, and seasonal absence spikes.
| Employee | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | … | Total |
|---|---|---|---|---|---|---|---|---|---|---|
| Ben Cooper | 1 | 2 | 1 | – | 2 | 1 | – | 1 | … | 8 |
| Aisha Khan | – | 5 | – | – | – | – | – | – | … | 5 |
| Carla Mendez | – | – | 1 | 1 | – | 2 | – | – | … | 4 |
| David Osei | – | – | – | – | 2 | – | 1 | – | … | 3 |
| Elena Rossi | – | – | – | – | – | – | 1 | – | … | 1 |
The controlled vocabulary. Free-text reasons cannot be counted, so the log uses a fixed set and this tab defines which ones count toward the rate. Vacation is listed but excluded — mixing planned leave into an absence rate makes a well-run team look absent a tenth of the year.
Best for: keeping the log consistent when more than one person maintains it.
| Type | Counts toward rate? | Notes |
|---|---|---|
| Sickness | Yes | Short-term illness — the main Bradford driver. |
| Unauthorised | Yes | No notice, no approved reason. |
| Late | Part | Log as a part day — shows up as occasions. |
| Medical | Yes | Appointments, hospital, dental. |
| Family | Yes | Dependant emergency, childcare breakdown. |
| Vacation | No | Planned leave — excluded on purpose. |
On the Employees tab, enter each person's scheduled working days for the period — 260 for a full-time year, or 21–22 if you run this monthly. Part-timers get their own figure. Without this, days lost cannot become a comparable rate.
One row per continuous absence: employee, type, first day, last day. Working days are counted for you; overwrite with 0.5 for a half day. Record the reason given and whether a return-to-work conversation took place.
Occasions, days lost, absence rate and Bradford Factor update automatically. Set your trigger score in the cell at the top — 50 and 125 are common first and second thresholds — and treat a flag as a prompt to talk to someone, not a verdict.
Two employees each lose eight days in a year. One had an operation and was away for eight consecutive days, planned three weeks in advance. The other was off for a single day, eight separate times, each one announced that morning.
Any tracker that only counts days says these two are identical. They are not remotely identical to the people who had to cover the shifts. The first absence was scheduled around; the second wrecked eight mornings. This is the entire reason the Bradford Factor squares the number of occasions — and the reason this workbook asks you to log one row per absence rather than shading in a calendar.
| Pattern | Occasions × days | S | D | Bradford Factor |
|---|---|---|---|---|
| One long absence | 1 occasion × 10 days | 1 | 10 | 10 |
| Two short absences | 2 occasions × 4 days | 2 | 4 | 16 |
| Four short absences | 4 occasions × 6 days | 4 | 6 | 96 |
| Frequent single days | 8 occasions × 8 days | 8 | 8 | 512 |
| Persistent pattern | 12 occasions × 14 days | 12 | 14 | 2,016 |
Note how row four and row five differ by only six days lost but by 1,500 points. That steepness is a feature at the low end and a trap at the high end — which is why the score should start a conversation, not end one.
The Bradford Factor is a screening tool with a well-known failure mode: it is blind to the reason for an absence. Several categories of absence produce high scores precisely because of circumstances you must not penalise.
The practical approach: use the score to decide who to talk to, keep the reason column populated, and make every actual decision on the individual facts. Nothing in this template is a substitute for your own absence policy or local employment law advice.
All of these are already in the workbook and all work unchanged in Google Sheets. Row 6 is the first employee row.
| Calculate | Formula | What it does |
|---|---|---|
| Working days per occasion | =NETWORKDAYS(C6,D6) | Weekdays inclusive of both dates. Overwrite with 0.5 for a half day. |
| Occasions per employee | =COUNTIFS(Log!$A:$A,$A6)-COUNTIFS(Log!$A:$A,$A6,Log!$B:$B,"Vacation") | Counts rows, not days — and drops planned leave. |
| Days lost per employee | =SUMIFS(Log!$E:$E,Log!$A:$A,$A6)-SUMIFS(Log!$E:$E,Log!$A:$A,$A6,Log!$B:$B,"Vacation") | Same idea on the day totals. |
| Absence rate | =C6/D6 | Days lost over scheduled days. Format as a percentage to one decimal. |
| Bradford Factor | =B6^2*C6 | Occasions squared, times days lost. |
| Trigger flag | =IF(F6>=$B$4,"Review - above trigger","") | One editable threshold cell drives every flag in the column. |
Log! is shorthand — the workbook uses 'Absence Log'! with bounded ranges.
Every formula on this page is trivial arithmetic. The reason absence data is usually wrong has nothing to do with the maths — it is that the absence never got written down. Someone calls a supervisor at 07:40, the supervisor covers the shift, and the log is updated on Friday if at all. By the quarterly review you are reconstructing three months of mornings from memory, and the reconstruction always undercounts.
That is a capture problem, not an analysis problem. ClockIt knows who was scheduled and who actually clocked in — on mobile, a web kiosk, or a biometric time clock — so a no-show is flagged at the start of the shift rather than remembered later. The absence record is a by-product of people turning up, and it feeds the same timesheets you run payroll from. Take the spreadsheet if your team is small; move when writing it down becomes the job.
Absence rate = days lost ÷ days scheduled, for the same period. So 8 days lost against 260 scheduled days is 3.1%. The two mistakes that make the number meaningless are dividing by calendar days instead of working days, and including planned vacation in the numerator. This template does neither — the formula is =C6/D6 where D comes from each person's scheduled days.
Bradford Factor = S × S × D, where S is the number of separate absence occasions and D is the total days absent. Ten separate one-day absences score 10 × 10 × 1 = 1,000. One ten-day absence scores 1 × 1 × 10 = 10. The formula deliberately weights frequency far more heavily than duration, because short unplanned absences are harder to cover for. In the template it is =B6^2*C6.
There is no official standard — organisations set their own. Commonly used first triggers sit around 50 (an informal conversation), with 125 and 500 as later stages. The template ships with the trigger at 50 and it is a single editable cell. Set yours in your absence policy first, then match the sheet to it, not the other way round.
Not on its own, and treating a score as automatic grounds for action is how employers get into trouble. It is a screening tool that tells you where to look. Disability-related absence, pregnancy-related absence and protected medical leave all inflate scores while being precisely the absences you must not penalise. Use it to prioritise conversations; make decisions on the facts of each case.
One continuous period away from work, regardless of length. Monday to Wednesday off sick is one occasion of three days. Off sick Monday, back Tuesday, off again Wednesday is two occasions of one day each — and that is a genuinely different situation, which is why the distinction is worth getting right in the log.
An attendance sheet is a day-by-day grid: a code in every cell for every person, every day, present or not. This is event-based — you only write a row when something happens, and the analysis you get back is about patterns rather than daily presence. If you need the daily grid, use the attendance sheet template; many teams keep the grid for records and this for the monthly review.
Broadly, 2–3% of scheduled time is typical for office-based work, with higher rates common in shift, healthcare and frontline roles. Compare each person against your own team's average rather than a national figure — your working patterns, sick pay policy and workforce make far more difference than any published benchmark.
Largely, yes — and the recording is the part that fails in practice. Absences get logged late, or reconstructed from memory at month end, which is when the data stops being trustworthy. ClockIt knows who was scheduled and who clocked in, so a no-show is flagged at the start of the shift rather than remembered afterwards, and the same records feed timesheets and payroll. The free plan covers small teams.
Daily, weekly, monthly and yearly attendance grids with auto totals.
Log leave as date ranges — entitlement, taken and remaining calculate themselves.
Track vacation, sick and personal leave balances per employee.
Weekly, biweekly, monthly and project timesheets for payroll.
Weekly, bi-weekly, monthly and shift schedules for your team.
Printable sign-in log for recording start and finish times.
A simple leave request form your team can fill in and submit.
Work out statutory leave entitlement, pro-rata and part-year.