Cellwise Start free

Excel guide · 4 min read

How to calculate average handle time (AHT) in Excel

Quick answerAverage handle time is (talk time + hold time + after-call work) divided by the number of calls handled. With handle time per call in G (=D2+E2+F2), team AHT is =SUM(G2:G11)/COUNT(G2:G11). Format the result as m:ss, or multiply by 86400 to get seconds.

Sample data

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

RowABCDEFGH
1CallAgentStartedTalkHoldAfter-call workHandle timeHour
2C-1001PriyaNov 10, 202612:04 AM12:01 AM12:01 AM0:069
3C-1002TomNov 10, 202612:07 AM12:01 AM12:02 AM0:109
4C-1003PriyaNov 10, 202612:03 AM12:00 AM12:01 AM0:049
5C-1004ElenaNov 10, 202612:13 AM12:03 AM12:04 AM0:209
6C-1005PriyaNov 10, 202612:05 AM12:01 AM12:01 AM0:0710
7C-1006TomNov 10, 202612:07 AM12:00 AM12:02 AM0:0910
8C-1007PriyaNov 10, 202612:03 AM12:00 AM12:01 AM0:0410
9C-1008ElenaNov 10, 202612:10 AM12:02 AM12:03 AM0:1611
10C-1009PriyaNov 10, 202612:05 AM12:01 AM12:01 AM0:0711
11C-1010TomNov 10, 202612:05 AM12:01 AM12:02 AM0:0811

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

Handle time per call

A call log usually has talk time, hold time and after-call work (wrap-up) per call. Make sure Excel sees them as durations: 4:10 typed into a cell means 4 hours 10 minutes, not 4 minutes 10 seconds. Type minutes and seconds as 0:04:10. In the sample they're built with =TIME(0,4,10).

Handle time in G2 is =D2+E2+F2. Excel stores durations as fractions of a day, so they add like any number. Format G with a custom format of [m]:ss or h:mm:ss (Ctrl+1 > Number > Custom) to read them.

=TIMEVALUE("4:10")*1440

Result: 250

250: Excel reads "4:10" as 250 minutes. Typed that way, a 4-minute talk time becomes a 4-hour one, and AHT goes through the roof.

Team AHT

AHT is total handle time divided by calls handled: =SUM(G2:G11)/COUNT(G2:G11). Count calls that were actually handled; abandoned calls that never reached an agent have no handle time and shouldn't be in the count. Measure those with the call abandonment rate instead.

The result is a fraction of a day, so it looks like 0.0063 until you format it. Use m:ss for a dashboard, or turn it into a number your reporting tool understands: x1440 for minutes, x86400 for seconds (=SUM(G2:G11)/COUNT(G2:G11)*86400 gives 546.5). Breaking it down helps coaching: talk averages 6:14, hold 1:00 and after-call work 1:53.

=SUM(G2:G11)/COUNT(G2:G11)

Result: 0.0063252314815 shows as 9:07 with Number format

0.0063: the team's AHT is 9:07, 546.5 seconds per call.

Totals over 24 hours need [h]:mm:ss

A day's total handle time for a whole team easily passes 24 hours. With the format h:mm:ss, Excel rolls over at 24 hours like a clock: 36 hours of handle time displays as 12:00:00. The square brackets in [h]:mm:ss tell Excel to keep counting hours: =TEXT(1.5,"[h]:mm:ss") shows 36:00:00.

The sample's ten calls total 1:31:05, or 5,465 seconds. Use the bracket format on every total cell anyway, so the sheet still reads correctly on a busy day.

=SUM(G2:G11)

Result: 0.063252314815 shows as 1:31:05 with the custom [h]:mm:ss format

0.0633: ten calls add up to 1:31:05 of handle time.

AHT per agent, and why you shouldn't average the averages

Per agent, divide that agent's handle time by that agent's calls: =SUMIFS(G2:G11,B2:B11,"Priya")/COUNTIFS(B2:B11,"Priya"). Priya handles 5 calls at 5:34, Tom 3 calls at 9:15, Elena 2 complex calls at 17:45.

Average those three numbers and you get 10:51, which isn't the team's AHT. Elena's 2 calls would count as much as Priya's 5. The team's real AHT is 9:07, total time over total calls. If you only have each agent's AHT and call count, weight it: =SUMPRODUCT(aht_range,calls_range)/SUM(calls_range).

Treat a low AHT with care too. Rushing customers off the phone lowers AHT and raises repeat calls; look at it beside first contact resolution or quality scores.

=SUMIFS(G2:G11,B2:B11,"Elena")/COUNTIFS(B2:B11,"Elena")

Result: 0.012326388889 shows as 17:45 with Number format

0.0123: Elena's AHT is 17:45 over her 2 calls.

AHT by hour of day

To staff the right hours, add a helper column H with =HOUR(C2): the hour each call started (9, 10, 11). Then =SUMIFS(G2:G11,H2:H11,9)/COUNTIFS(H2:H11,9) gives the AHT for calls that started between 9:00 and 9:59: 9:59. The 10:00 hour runs at 6:42 and 11:00 at 10:22.

If your export stores durations as plain seconds (250 instead of 0:04:10), divide by 86400 to turn them into Excel time, or keep everything in seconds and skip the time formats. If durations arrive as text, convert them first with TIMEVALUE or Data > Text to Columns; text durations are ignored by SUM.

=SUMIFS(G2:G11,H2:H11,10)/COUNTIFS(H2:H11,10)

Result: 0.0046489197531 shows as 6:42 with Number format

0.0047: calls starting in the 10:00 hour averaged 6:42.

Common mistakes

Typing 4:10 for 4 minutes 10 seconds

Type 0:04:10, or use =TIME(0,4,10); Excel reads 4:10 as hours and minutes.

Averaging each agent's AHT for the team number

Divide total handle time by total calls, or weight agent AHTs by their call counts.

Totals that reset after 24 hours

Format total cells as [h]:mm:ss instead of h:mm:ss.

Counting abandoned calls

Divide by calls handled, which excludes calls that never reached an agent.

FAQ

What is the formula for average handle time?

AHT = (total talk time + total hold time + total after-call work) / number of calls handled. In Excel, with handle time per call in G2:G11: =SUM(G2:G11)/COUNT(G2:G11). For AHT next to service level, abandonment, occupancy, FCR and CSAT on one sheet, see Excel for call centers and its free call center KPI dashboard template.

How do I convert AHT to seconds in Excel?

Multiply the time value by 86400, the number of seconds in a day: =A1*86400. Use 1440 for minutes. Format the result as a number, not as a time.

How do I show minutes and seconds in Excel?

Apply a custom number format of m:ss, or [m]:ss if the total can exceed 60 minutes. Press Ctrl+1, choose Custom and type the format.

Why is my AHT showing as a decimal like 0.0063?

Excel stores durations as fractions of a day. Format the cell as m:ss to see 9:07, or multiply by 86400 for seconds.

Functions used

Related guides

Updated