Confident Cells

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.

Your turn

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.

ABCD
1EmployeeFirst dayLast dayWorking days
2Owen23/01/202626/01/2026
3Ivan26/01/202602/02/2026
4Zoe30/03/202630/03/2026
5Zoe09/04/202620/04/2026
6Zoe04/05/202608/05/2026
7Ivan20/05/202620/05/2026
8Ivan01/06/202612/06/2026
9Ella24/08/202624/08/2026
10Ivan04/09/202607/09/2026
11Ella16/09/202624/09/2026
12Ella23/09/202605/10/2026
13Owen14/10/202626/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

ABCDEFGHI
1NameFirst dayLast dayDaysNameSpellsBradford
2Amira02/03/202604/03/20263Amira
3Ben05/03/202610/03/20264Ben
4Amira16/03/202616/03/20261
  1. =NETWORKDAYS(B2,C2) in D2. Ben was off Thursday to the next Tuesday: the weekend doesn't count, so 4 working days.
  2. =COUNTIF($A$2:$A$4,G2) in H2. Amira appears twice in the log, so she has had 2 spells.
  3. =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

  1. 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.
  2. In H2, count how many spells of absence each person in G2:G6 has had. Your formula will be copied down to H6.
  3. In I2, add up the days each person in G2:G6 has lost to absence.
  4. 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.
  5. 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.