How to calculate absenteeism rate in Excel

Step 1: Scheduled working days

The bottom of the rate is the days people were supposed to work, not calendar days. NETWORKDAYS counts Monday to Friday between two dates, both included, and skips any holidays you list.

October 2026 has 22 weekdays. With the company holiday on October 12 in O4, it's 21 scheduled days. Keep holidays in cells (a range like O4:O6 for several) instead of typing dates into the formula, so next month's sheet only needs new dates.

Step 2: Part-time schedules with NETWORKDAYS.INTL

A person who works Monday, Wednesday and Friday was only scheduled for 12 days in October. Counting 21 for them makes their rate look much better than it is.

NETWORKDAYS.INTL takes a 7-character weekend pattern, Monday first, where 1 means a day off. "0000011" is a normal Monday-to-Friday week. "0101011" means Monday on, Tuesday off, Wednesday on, Thursday off, Friday on, weekend off. Store each person's pattern in the Workweek column (I) and the scheduled formula in J2 is =NETWORKDAYS.INTL($O$2,$O$3,I2,$O$4), copied down. Format column I as Text, or Excel turns 0000011 into the number 11.

Step 3: Log absences as spells

Record one row per absence spell: who, first day off, last day off and a reason. The Days column counts only the days that person was scheduled: =NETWORKDAYS.INTL(B2,C2,XLOOKUP(A2,$G$2:$G$9,$I$2:$I$9),$O$4). XLOOKUP pulls their workweek pattern from the staff table.

Chloe was off October 19 to 23. That's 5 calendar days but only 3 scheduled ones (Monday, Wednesday, Friday), and the formula counts 3. For a half day, type 0.5 over the formula in that row.

If a spell crosses into the next month, clip it to the month so it isn't counted twice: =MAX(0,NETWORKDAYS.INTL(MAX(B2,$O$2),MIN(C2,$O$3),pattern,$O$4)). A spell from September 28 to October 2 counts 2 days in October. When the log covers several months, add date conditions to the Absent column too: =SUMIFS($E$2:$E$10,$A$2:$A$10,G2,$B$2:$B$10,">="&$O$2,$B$2:$B$10,"<="&$O$3).

Step 4: Rate per person and per team

Each person's rate is absent / scheduled. Chloe's 3 of 12 is 25%, while Dan's 3 of 21 is about 14.3%. Same number of days off, very different share of the schedule.

For a team, add up the days first and divide once. Support missed 7 of 75 scheduled days (9.3%) and Warehouse 5 of 75 (6.7%); the whole team is 12 of 150, 8.0%. Averaging the eight personal rates gives 8.9% instead, because it gives a part-timer's 12 days the same weight as a full-timer's 21. Pooled days are the honest team number.

Absence by reason

SUMIFS by the Reason column shows what's behind the number: 8 of the 12 days were recorded as Sick, about 67%, with 2 Family and 2 Other. Keep reasons to a few broad categories your HR policy defines.

Don't put medical details in the sheet. "Sick" is enough for a rate; anything more personal belongs in whatever confidential system your HR team uses, under the privacy rules that apply to you.

The Bradford factor (and its limits)

Some HR teams also track the Bradford factor: spells squared x total days, S x S x D. It weights frequent short absences more than one long one. Dan had 3 one-day spells: 3 x 3 x 3 = 27. Chloe had one 3-day spell: 1 x 1 x 3 = 3. Ana had 2 spells and 3 days: 12. In the staff table, =COUNTIFS($A$2:$A$10,G2)^2*K2 copied down gives everyone's score.

Treat it as a prompt for a conversation, never as an automatic trigger. It counts every absence the same way, including ones that may be protected by law or by your own policies, such as disability-related absence or family leave. Decisions about what a score means belong to HR and the rules where you work, not to a cell.