Operations & HR Analytics
Attendance Tracking Formula: Net Attendance Rate (Excluding Approved Leave) & Bradford Factor
Calculate fair employee shift attendance % by excluding pre-approved statutory leave from scheduled shifts, and pair it with the HR Bradford Disruption Factor B = S^2 * D.
Separate approved statutory vacation from unscheduled absence and spell frequency!
Dividing Days_Worked by Gross_Calendar_Shifts penalizes employees who take earned annual vacation or statutory parental leave! Fair workforce attendance removes pre-approved excused shifts (B3) from both numerator and denominator: Net_Attendance_% = 100 * (Gross_Shifts - Excused_Leave - Unscheduled_Absence) / (Gross_Shifts - Excused_Leave).
Furthermore, six isolated Monday absences disrupt shift handoffs far more than one 6-day flu recovery! HR operations quantify this via the Bradford Factor B = S^2 * D, where S (B5) is the count of separate absence spells/episodes and D (B4) is total unscheduled days lost (6^2 * 6 = 216 vs 1^2 * 6 = 6).
| Absence Pattern (Net 60 Scheduled Shifts) | Spells S (B5) | Days Lost D (B4) | Attendance % & Bradford Score |
|---|---|---|---|
| Baseline Case (2 spells, 3 days) | 2 | 3 | 95.00% Attendance | Bradford = 12 |
| Single Surgery Spell (1 spell, 6 days) | 1 | 6 | 90.00% Attendance | Bradford = 6 (Low disruption) |
| Six Isolated 1-Day Absences (6 spells, 6 days) | 6 | 6 | 90.00% Attendance | Bradford = 216 (Review trigger) |
Net Attendance Rate (%) Excluding Pre-Approved Statutory Leave
Computes attended shifts as a percentage of net available scheduled shifts (B2 - B3).
=IF(OR(B2 <= B3, B3 < 0, B4 < 0, B4 > B2 - B3), "Invalid Shift or Leave Counts", ROUND(100 * (B2 - B3 - B4) / (B2 - B3), 2))Bradford Absence Disruption Factor (S^2 * D)
Squares separate absence episodes B5 and multiplies by total unscheduled days lost B4.
=IF(OR(B5 < 0, B4 < 0, AND(B4 > 0, B5 = 0), B5 > B4), "Invalid Spell/Day Combination", ROUND((B5 ^ 2) * B4, 0))Try the example
Use the original values or change them to test your understanding.