Absence records: practice exercises
NETWORKDAYS, spells and days per person, Bradford Factor from a log, lost time rate. 5 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
Each row of this absence log is one spell of absence, from the first day off to the last. In D2, work out how many working days (Monday to Friday) each spell cost.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | First day | Last day | Working days |
| 2 | Owen | 23/01/2026 | 26/01/2026 | |
| 3 | Ivan | 26/01/2026 | 02/02/2026 | |
| 4 | Zoe | 30/03/2026 | 30/03/2026 | |
| 5 | Zoe | 09/04/2026 | 20/04/2026 | |
| 6 | Zoe | 04/05/2026 | 08/05/2026 | |
| 7 | Ivan | 20/05/2026 | 20/05/2026 | |
| 8 | Ivan | 01/06/2026 | 12/06/2026 | |
| 9 | Ella | 24/08/2026 | 24/08/2026 | |
| 10 | Ivan | 04/09/2026 | 07/09/2026 | |
| 11 | Ella | 16/09/2026 | 24/09/2026 | |
| 12 | Ella | 23/09/2026 | 05/10/2026 | |
| 13 | Owen | 14/10/2026 | 26/10/2026 |
How it works
An absence log usually has one row for each spell of absence. NETWORKDAYS(first day, last day) counts the working days (Monday to Friday) a spell cost, including both ends.
To total up each person, count their rows with COUNTIF and add up their days with SUMIF. Lock the log's ranges with $ so they don't move when the formula is copied down. The Bradford Factor is then spells² × days, all in one formula.
=NETWORKDAYS(B3,C3) =COUNTIF($A$2:$A$4,G3) =SUMIF($A$2:$A$4,G3,$D$2:$D$4) =SUM(D2:D4)/(3*220)
One row per spell
An absence log has a row for each spell: one continuous absence. NETWORKDAYS(first, last) counts the working days in it, Monday to Friday, including both ends.
Then each person's number of spells is COUNTIF of their name, and their days lost is SUMIF.
Worked example
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Name | First day | Last day | Days | Name | Spells | Bradford | ||
| 2 | Amira | 02/03/2026 | 04/03/2026 | 3 | Amira | ||||
| 3 | Ben | 05/03/2026 | 10/03/2026 | 4 | Ben | ||||
| 4 | Amira | 16/03/2026 | 16/03/2026 | 1 |
=NETWORKDAYS(B2,C2)in D2. Ben was off Thursday to the next Tuesday: the weekend doesn't count, so 4 working days.=COUNTIF($A$2:$A$4,G2)in H2. Amira appears twice in the log, so she has had 2 spells.=COUNTIF($A$2:$A$4,G2)^2*SUMIF($A$2:$A$4,G2,$D$2:$D$4)in I2. Spells squared times days, straight from the log: Amira 2² × 4 = 16, Ben 1² × 4 = 4.
Lost time rate
The lost time rate is the share of working time lost to absence: total days absent ÷ (number of employees × working days each). Brackets round the bottom half matter: =SUM(days)/(COUNTA(names)*H1).
What you'll practise
- Each row of this absence log is one spell of absence, from the first day off to the last. In D2, work out how many working days (Monday to Friday) each spell cost.
- In H2, count how many spells of absence each person in G2:G6 has had. Your formula will be copied down to H6.
- In I2, add up the days each person in G2:G6 has lost to absence.
- Work out each person's Bradford Factor straight from the log, in one formula: their number of spells, squared, times their total days lost. Put it in H2.
- The lost time rate is the share of working time lost to absence. In H2, divide the total days absent by the days everyone could have worked: the number of employees times the working days in H1.