Cellwise Start free

Excel guide · 6 min read

How to calculate agent occupancy in Excel

Quick answerOccupancy is the share of an agent's available-or-busy time spent handling contacts: handle time / (handle time + available time). With handle time in C2 and available (idle) time in D2, use =C2/(C2+D2) and format it as a percentage. Breaks, meetings and other not-ready time stay out of both the top and the bottom.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGH
1AgentIntervalHandle timeAvailableNot readyOccupancyAgent
2Ana9:00 AM12:41 AM12:13 AM12:06 AM0.76543209877Ana
3Ana10:00 AM12:50 AM12:05 AM12:05 AM0.91818181818
4Ana11:00 AM12:06 AM12:09 AM12:45 AM0.4Paid hours
5Ben9:00 AM12:35 AM12:25 AM12:00 AM0.586111111113
6Ben10:00 AM12:48 AM12:09 AM12:03 AM0.84210526316
7Ben11:00 AM12:53 AM12:02 AM12:05 AM0.95757575758Occupancy target
8Chloe9:00 AM12:00 AM12:00 AM1:00 AM0.85
9Chloe10:00 AM12:44 AM12:11 AM12:05 AM0.80909090909
10Chloe11:00 AM12:50 AM12:08 AM12:02 AM0.8591954023

Download this sheet as an Excel practice file (.xlsx)

Occupancy for one agent and interval

Start from the agent interval report your phone system or WFM tool exports: one row per agent per interval, with time in each state. The sample has three agents over three hourly intervals. Handle time in C is talk + hold + after-call work, available in D is time logged in and waiting for a contact, and not ready in E is breaks, meetings, coaching and training.

Occupancy only looks at C and D. In Ana's 9:00 interval she handled contacts for 41:20 and waited for 12:40, so =C2/(C2+D2) is 41:20 out of 54:00 = 76.5%. Her 6 minutes on a break don't count against her, because she wasn't available to take a call.

Two denominators look reasonable and give the wrong number. Dividing by the whole hour, =C2/TIME(1,0,0), or by all logged-in time, =C2/(C2+D2+E2), both give 68.9%, because they charge the break to occupancy. That's a different measure (see the utilization section below).

=C2/(C2+D2)

Result: 0.76543209877 shows as 76.5% with Percent format

0.765: Ana handled contacts for 41:20 of the 54:00 she was available or busy at 9:00.

Times in h:mm:ss, seconds, or text

The division works on any unit, as long as top and bottom use the same one. Times like 0:41:20 are fractions of a day, and the ratio of two of them is already the percentage. You only convert when you want to show the times themselves: multiply by 86400 for seconds or 1440 for minutes. The 10:00 interval had 143 minutes of handle time across the team, =SUMIFS(C2:C10,B2:B10,TIME(10,0,0))*1440.

Many systems export seconds instead (2480 for 41:20). Occupancy is the same formula on the seconds columns. To show seconds as a time, divide by 86400 and format as [h]:mm:ss, or use =TEXT(2480/86400,"h:mm:ss").

If the export has times as text (left-aligned, often with a leading apostrophe), SUM skips them and the totals come out too low. Convert a column with =TIMEVALUE(C2) or =--C2, or select it and run Data > Text to Columns > Finish. For durations of 24 hours or more, format totals as [h]:mm:ss: plain h:mm:ss drops whole days. See sum time in Excel.

=C2*86400

Result: 2480

2480: Ana's 41:20 of handle time at 9:00 in seconds.

An interval with no available or handle time

Chloe spent the whole 9:00 hour in training, so C8 + D8 is zero and =C8/(C8+D8) returns #DIV/0!. That's correct in a way: occupancy has no meaning when the agent wasn't taking contacts. But one error breaks every AVERAGE, MAX or chart that touches the column.

Guard it in the occupancy column: =IF(C2+D2=0,"",C2/(C2+D2)), as in column F of the sample. Showing a blank rather than 0% matters: 0% would claim Chloe was available all hour and got no calls.

The blank has its own trap. In a comparison, Excel ranks text above every number, so "" > 0.85 is TRUE. =FILTER(A2:A10,F2:F10>H8) lists Chloe at 9:00 as over the 85% target. Add a test that the cell isn't blank, (F2:F10<>"")*(F2:F10>H8), and the list is Ana 10:00, Ben 11:00 and Chloe 11:00. COUNTIFS(F2:F10,">"&H8) skips the text by itself and gives 3.

=FILTER(A2:A10&" "&TEXT(B2:B10,"h:mm"),(F2:F10<>"")*(F2:F10>H8),"None")

Result: spills 3 rows × 1 column

Ana 10:00
Ben 11:00
Chloe 11:00

Ana 10:00, Ben 11:00, Chloe 11:00: the intervals above the 85% target in H8, without the blank training hour.

Occupancy per agent and for the team: total over total

For an agent's day, add up their handle time and available time, then divide. SUMIFS does it from the interval rows: Ana handled 1:37:50 and was available for 0:26:10, so her occupancy for the day is 78.9%.

Averaging her three interval percentages gives 69.5% instead. Her 11:00 interval was mostly a meeting: only 15 minutes available or busy, 6 of them on contacts, so 40%. AVERAGE gives those 15 minutes the same weight as a full hour on the phones. The same goes for the team: =SUM(C2:C10)/(SUM(C2:C10)+SUM(D2:D10)) is 80.2%, while =AVERAGE(F2:F10) says 76.7%. Report total over total; it's the number that matches the hours your system logged.

For every agent at once in Excel 365: =LET(p,UNIQUE(A2:A10),h,SUMIFS(C2:C10,A2:A10,p),a,SUMIFS(D2:D10,A2:A10,p),HSTACK(p,h/(h+a))) spills Ana 78.9%, Ben 79.0% and Chloe 83.5%. Swap A2:A10 for B2:B10 to get occupancy by interval instead: 9:00 67.1%, 10:00 85.6%, 11:00 84.8%.

=SUMIFS(C2:C10,A2:A10,H2)/(SUMIFS(C2:C10,A2:A10,H2)+SUMIFS(D2:D10,A2:A10,H2))

Result: 0.78897849462 shows as 78.9% with Percent format

0.789: Ana (the agent in H2) was handling contacts for 78.9% of her available-or-busy time across the three hours.

Occupancy vs utilization vs adherence

These three get mixed up because they come from the same state data. Occupancy asks how busy agents were while they were available. Utilization asks how much of the paid (or scheduled) time went to that work, so breaks, meetings and training pull it down. Contact centers define the top of utilization differently: some use handle time, some use all logged-in time. Use whatever your WFM tool uses, and write the definition on the report.

With 3 paid hours in H5, Ana's utilization on handle time is =SUMIFS(C2:C10,A2:A10,H2)*24/H5 = 54.4%, and on logged-in available-or-busy time it's 68.9%. Both are far below her 78.9% occupancy, because the 11:00 meeting counts against utilization and not occupancy. The *24 turns the summed time into hours so it can be divided by a number of hours.

Adherence is different again: whether the agent was in the scheduled state at the scheduled time. An agent can have perfect occupancy and poor adherence (took lunch an hour late) or the reverse.

=SUMIFS(C2:C10,A2:A10,H2)*24/H5

Result: 0.54351851852 shows as 54.4% with Percent format

0.544: Ana's 1.63 hours of handle time out of 3 paid hours.

Reading the number

Occupancy mostly reflects how many contacts arrived compared with how many agents were logged in, not how hard people worked. The 9:00 interval at 67.1% had more people available than calls; the 10:00 interval at 85.6% was close to full. An agent can't raise their occupancy by trying harder if the phone doesn't ring, so it's a staffing and forecasting measure first and a coaching measure second.

Put your target in a cell (H8 here) rather than in formulas, so it can change without editing every formula. Targets vary, but many centers avoid running above roughly 85-90% for long stretches, because very high occupancy leaves agents no recovery time between contacts and usually goes with long queues. A per-interval view like the one above shows where to move breaks and training: Chloe's training hour fell in the quietest interval, which is the right place for it.

Common mistakes

Dividing by the whole interval or by logged-in time

Occupancy is handle / (handle + available). Breaks, meetings and training (not ready) stay out of both the top and the bottom.

Averaging interval or agent percentages

Sum handle time and available time first, then divide. AVERAGE gives a 15-minute interval the same weight as a full hour.

Treating a blank occupancy cell as a number

"" counts as greater than any number in a comparison. Test for F2<>"" in FILTER and IF, or use COUNTIFS, which skips text.

Text times in the export

If SUM gives less than you expect, the times are text. Convert with TIMEVALUE, --, or Data > Text to Columns.

FAQ

What is the formula for occupancy in a call center?

Occupancy = handle time / (handle time + available time), where handle time is talk + hold + after-call work. In Excel: =C2/(C2+D2), formatted as a percentage. Excel for call centers shows how to flag the half-hour intervals above your occupancy cap.

What is the difference between occupancy and utilization?

Occupancy measures how busy agents were while logged in and available. Utilization measures productive time against paid or scheduled time, so breaks, meetings and training lower utilization but not occupancy.

How do I calculate occupancy from seconds in Excel?

Use the same formula on the seconds columns: handle seconds / (handle seconds + available seconds). The units cancel out. Divide seconds by 86400 only when you want to display them as h:mm:ss.

Why is my team occupancy different from the average of my agents?

Agents and intervals have different amounts of logged-in time. Total over total weights each by its time; AVERAGE treats a short interval like a long one. Use =SUM(handle)/(SUM(handle)+SUM(available)).

What is the difference between occupancy and shrinkage?

Occupancy is how busy agents were while available. Shrinkage is the share of paid hours they were not available at all (breaks, meetings, training, time off, sickness), and it decides how many agents to schedule: net agents / (1 - shrinkage). See contact center shrinkage in Excel.

Functions used

Related guides

Updated