Bradford Factor Formula & Excel Template

Based on the default pattern below, a typical score is 160 (Warning). Adjust the inputs for your exact figure.

Need a single score quickly? Calculate your score using the Bradford Factor Calculator — or use the method, formulas and template below for a whole team.

4 spells
10 days

160

Warning

Disruptive short-term absence pattern — a Stage 1 absence review and Return-to-Work interview are usually required.

050125400+

S² × D = 4² × 10 = 160

Audit note

Trigger points are employer POLICY, not law — set the Notice / Warning / Critical thresholds under Adjust assumptions to match your own absence policy (the 50 / 125 / 400 defaults are just common practice). Apply them with discretion: under the Equality Act 2010, disability- and pregnancy-related absence may need to be discounted and the thresholds adjusted before any formal action.

Stop tracking this by hand

A Bradford score is only as accurate as the absence dates behind it. Tempry logs every request against an immutable ledger, so you're always calculating from an accurate record — not a spreadsheet you have to keep updating by hand.

Free forever for teams up to 15. No credit card required.

Different scenario? Try the full Bradford Factor calculator

How this calculation works

Work out a Bradford Factor score from absence spells and days, see which trigger-point tier it falls into, and understand what UK employers can and can't do with the result.

S² × D = 4² × 10 = 160

How is the Bradford Factor calculated?

  • The formula is B = S² × D (also written as Bradford points = spells² × days). S is the number of separate absence spells and D is the total number of days absent, both over a rolling 12 months.
  • Step 1: count the separate absences (spells) in the last 12 months. A spell is one continuous period of absence — three consecutive days off sick is one spell of three days, not three spells.
  • Step 2: square the number of spells.
  • Step 3: multiply by the total days absent.
  • Worked example: 4 spells totalling 10 days → 4² × 10 = 16 × 10 = 160.

How does the Bradford score work?

  • One 10-day absence scores 1² × 10 = 10; ten one-day absences score 10² × 10 = 1,000. Squaring the spells is the point of the formula: frequent short absences are harder to plan around.
  • Because the window rolls, a score falls on its own as old spells pass the 12-month mark.

Free Bradford Factor Excel template

A ready-made spreadsheet that logs absences and works out each employee’s rolling 12-month Bradford score, with trigger bands colour-coded. Works in Excel and Google Sheets. Free, no signup.

Bradford Factor formulas for Excel and Google Sheets

  • Single score from two numbers: =A2^2*B2 (A2 spells, B2 days).
  • Days counted for one absence (Absence Log, column D) — working days Monday–Friday, skipping bank holidays, counting an ongoing absence up to today, unless a manual override is entered: =IF(B2="","",IF(E2<>"",E2,IF(AND(ISNUMBER(B2),ISNUMBER(IF(C2="",TODAY(),C2))),IF(IF(C2="",TODAY(),C2)<B2,"Check dates",NETWORKDAYS(B2,IF(C2="",TODAY(),C2),Settings!$D$2:$D$40)),"Check dates")))
  • Spells in the last 12 months for the employee in A2 (Scores sheet, column B): =IF($A2="","",COUNTIFS('Absence Log'!$A$2:$A$500,$A2,'Absence Log'!$B$2:$B$500,">="&EDATE(TODAY(),-12),'Absence Log'!$B$2:$B$500,"<="&TODAY(),'Absence Log'!$F$2:$F$500,"<>Yes",'Absence Log'!$D$2:$D$500,">=0"))
  • Days absent in the last 12 months (Scores sheet, column C): =IF($A2="","",SUMIFS('Absence Log'!$D$2:$D$500,'Absence Log'!$A$2:$A$500,$A2,'Absence Log'!$B$2:$B$500,">="&EDATE(TODAY(),-12),'Absence Log'!$B$2:$B$500,"<="&TODAY(),'Absence Log'!$F$2:$F$500,"<>Yes",'Absence Log'!$D$2:$D$500,">=0"))
  • Bradford score (Scores sheet, column D): =IF($A2="","",B2^2*C2)
  • Absences marked “Yes” in the “Exclude from score?” column are left out (a blank cell counts as “No”); future-dated absences are ignored until they start; staff who don’t work Monday–Friday need the “Days override” column. A row showing “Check dates” has a last day before its first day (or text instead of a date) and is left out of the score until fixed.

Common spreadsheet mistakes

  • Multiplying spells × days without squaring.
  • Counting each day of one absence as a separate spell.
  • Using a calendar year instead of a rolling 12 months.
  • Including holidays, bank holidays or approved leave.
  • Using NETWORKDAYS with a blank end date for an ongoing absence, which returns a large negative day count.

Frequently asked questions

What is the Bradford Factor formula?
B = S² × D. S is the number of separate absence spells in a rolling 12 months and D is the total number of days absent. So four separate absences totalling ten days scores 4² × 10 = 160.
How do I calculate the Bradford Factor in Excel?
Put spells in one cell (say A2) and total days in another (B2), then use =A2^2*B2. Squaring the spells is the key step — a common mistake is multiplying spells × days without the square, which understates frequent absence.
Is a spreadsheet the best way to track this?
A spreadsheet works for a handful of people, but it doesn't roll the 12-month window forward automatically, alert you at trigger points, or keep an audit trail. That's why most teams move Bradford tracking into absence software once headcount grows.