Excel guide · 5 min read
How to calculate schedule adherence in Excel
=MAX(0,MIN(End1,End2)-MAX(Start1,Start2))*1440. Add them up per agent, then divide by scheduled minutes.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Agent | Activity | Start | End | Sched min | Adhered min | Exception min | Agent | State | Start | End | |
| 2 | Kim | Phone | 8:00 | 10:00 | 120 | 116 | 0 | Kim | Phone | 8:04 | 10:06 | |
| 3 | Kim | Break | 10:00 | 10:15 | 15 | 9 | 0 | Kim | Break | 10:06 | 10:24 | |
| 4 | Kim | Phone | 10:15 | 12:00 | 105 | 96 | 0 | Kim | Phone | 10:24 | 12:00 | |
| 5 | Raj | Phone | 8:00 | 9:30 | 90 | 90 | 0 | Raj | Phone | 7:58 | 9:30 | |
| 6 | Raj | Training | 9:30 | 10:30 | 60 | 55 | 0 | Raj | Not ready | 9:30 | 9:35 | |
| 7 | Raj | Phone | 10:30 | 11:30 | 60 | 40 | 20 | Raj | Training | 9:35 | 10:30 | |
| 8 | Raj | Phone | 10:30 | 11:10 | ||||||||
| 9 | Raj | Not ready | 11:10 | 11:30 |
Download this sheet as an Excel practice file (.xlsx)
Lay out the schedule and the state log
You need two tables. The schedule (A:D) has one row per planned block: agent, activity, start and end. The state log (I:L) is what the phone system recorded: agent, state, start and end. Both store times as real Excel times, which are fractions of a day, so 8:00 is 0.3333.
Scheduled minutes per block are =ROUND((D2-C2)*1440,0): the time difference times 1,440 minutes in a day. The ROUND matters, because times like 10:15 aren't exact binary fractions, and a difference that should be 105 can come out as 104.99999, which then fails an = test or shows odd decimals.
Use the same activity names in both tables. If the schedule says "Phone" and the log says "Available" and "On call", map the log states to schedule activities in a helper column first (XLOOKUP against a small mapping table).
Result: 450
450: Kim is scheduled for 240 minutes this morning and Raj for 210.
Overlap minutes for one scheduled block
Adherence only counts time in the right activity at the right time. For one scheduled block and one logged block, the overlap starts at the later of the two starts and ends at the earlier of the two ends. If that's negative, they don't overlap at all, so MAX(0, ...) turns it into zero.
Kim was scheduled on the phone from 8:00 to 10:00 and logged in at 8:04, staying on until 10:06. The overlap is 8:04 to 10:00, 116 minutes. The six minutes after 10:00 were phone time too, but during her scheduled break, so they don't count as adherence for either block.
Result: 116
116: Kim's minutes on the phone inside her first scheduled phone block.
Add up every logged block per schedule row
A scheduled block can overlap several log rows (an agent goes not ready and comes back). Column F checks every log row at once: same agent, same activity, and the overlap clamped at zero. The formula in F2 is:
=LET(o,IF($L$2:$L$9<D2,$L$2:$L$9,D2)-IF($K$2:$K$9>C2,$K$2:$K$9,C2),ROUND(SUM(($I$2:$I$9=A2)*($J$2:$J$9=B2)*(o>0)*o)*1440,0))
Why IF and not MIN and MAX? Over a range, MIN and MAX return one number for the whole column, so MIN($L$2:$L$9,D2) is just the earliest end time on the sheet. IF compares row by row. The (o>0)*o part does what MAX(0, ...) did for one pair. LET needs Excel 365 or 2021; in older versions write the overlap expression out twice inside SUMPRODUCT.
Result: spills 6 rows × 3 columns
| Kim | Phone | 116 |
| Kim | Break | 9 |
| Kim | Phone | 96 |
| Raj | Phone | 90 |
| Raj | Training | 55 |
| Raj | Phone | 40 |
Kim Phone 116, Break 9, Phone 96; Raj Phone 90, Training 55, Phone 40.
Adherence per agent and for the team
Per agent, divide that agent's adhered minutes by their scheduled minutes: =SUMIFS(F2:F7,A2:A7,"Kim")/SUMIFS(E2:E7,A2:A7,"Kim") gives 221 / 240 = 92.1%. Raj's is 185 / 210 = 88.1%. He left the phone at 11:10, twenty minutes before the end of his block.
For the team, divide total adhered minutes by total scheduled minutes: 406 / 450 = 90.2%. Averaging the two agents' percentages gives 90.1% instead, because it gives Raj's shorter shift the same weight as Kim's. Here the gap is small; with part-timers and full-timers mixed on a big team, total over total is the number that matches the minutes.
Result: 0.90222222222 shows as 90.2% with Percent format
0.9022: the team was in the right activity at the right time for 90.2% of scheduled minutes.
Exceptions: approved changes out of top and bottom
Raj's 11:10 to 11:30 wasn't a no-show: his supervisor pulled him into a coaching session, logged as not ready. Most centers treat approved changes like that as exceptions so they don't count against the agent. Column G holds exception minutes per schedule row.
Take exceptions out of the scheduled minutes. They usually have no adhered minutes in them (he wasn't on the phone), so the top stays the same and the bottom shrinks: 185 / (210 - 20) = 97.4% for Raj. If an exception window does contain adhered minutes, take those out of the top too, or the agent gets credit for time that's no longer scheduled.
Result: 0.97368421053 shows as 97.4% with Percent format
0.9737: Raj's adherence once the approved coaching session is excluded.
Adherence vs conformance
Conformance asks a different question: did the agent spend the scheduled amount of time in the activity, whenever they did it? For phone time, it's total logged phone minutes / scheduled phone minutes. Kim logged 218 phone minutes against 225 scheduled, 96.9% conformance, while her phone adherence is (116 + 96) / 225 = 94.2%. The gap is timing: late from break, on the phone during break.
Raj's two minutes on the phone before 8:00 count for conformance but not for adherence. Report both: low adherence with good conformance usually means timing (late breaks, long lunches made up later); low conformance means the hours themselves were short. For night shifts, store full date-times in both tables so blocks that cross midnight still overlap correctly.
Result: 0.96888888889 shows as 96.9% with Percent format
0.9689: Kim's phone conformance, 218 of 225 scheduled phone minutes.
Common mistakes
Using MIN and MAX across whole ranges
They collapse the range to one value. Compare row by row with IF, or use the single-pair MAX(0,MIN()-MAX()) formula in a helper column per log row.
Leaving times as text
"8:04" pasted as text can't be subtracted. Convert with TIMEVALUE or Text to Columns, and check with ISNUMBER.
Averaging agent percentages for the team
Divide total adhered minutes by total scheduled minutes so longer shifts get their real weight.
Counting exception minutes against the agent
Subtract approved exception minutes from scheduled minutes, and any adhered minutes inside them from the top.
FAQ
What is a good schedule adherence percentage?
Many contact centers target somewhere around 90% to 95%. The right target depends on how strict your definition is (whether short deviations or exceptions count), so compare against your own history rather than another center's number.
What is the difference between schedule adherence and conformance?
Adherence counts minutes in the scheduled activity at the scheduled time. Conformance compares total time in the activity with the scheduled total, regardless of when it happened.
How do I convert time to minutes in Excel?
Multiply by 1440: =(D2-C2)*1440. Wrap it in ROUND to remove tiny floating-point leftovers, for example =ROUND((D2-C2)*1440,0).
Can I allow a grace period before counting an agent out of adherence?
Some centers ignore deviations shorter than a set number of minutes. Calculate each log row's overlap in a helper column and only count deviations longer than the threshold, with the threshold in an input cell.
Does poor adherence count as shrinkage?
Not in most definitions. Shrinkage counts paid hours that were never meant to be on contacts or were lost to absence; adherence measures whether scheduled phone time happened at the right time. Some centers add adherence loss as its own category. The contact center shrinkage guide shows how to keep the definition fixed.
Functions used
MAXLargest numberMINSmallest numberROUNDRounds to digitsSUMAdds numbersSUMIFSAdds with several conditionsSUMPRODUCTMultiply then addLETNames values inside a formulaIFPicks a result from a testHSTACKJoins arrays side by sideXLOOKUPModern lookup in any direction
Related guides
- How to calculate contact center shrinkage in Excel
- How to calculate agent occupancy in Excel
- How to calculate average handle time (AHT) in Excel
- How to calculate utilization rate in Excel
- How to calculate elapsed time and time difference in Excel
- Excel formulas cheat sheet: the 25 everyone should know
- How to calculate sales commission in Excel
Updated