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.
Bradford score
160
Disruptive short-term absence pattern — a Stage 1 absence review and Return-to-Work interview are usually required.
How it's worked out
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.
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.
Worked example
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
NETWORKDAYSwith a blank end date for an ongoing absence, which returns a large negative day count.