How to Measure Average Ticket Resolution Time in Excel

Turn opened/resolved timestamps into resolution hours, average them while skipping open tickets, and break the average down by priority.

You will learn to

• Convert the gap between two timestamps into hours
• Average resolution time while ignoring unresolved tickets
• Average resolution time for one priority level

How it works

Excel stores date-times as days, so resolved − opened is a number of days; multiply by 24 for hours: =(D4-C4)*24. That gives each ticket’s resolution time, including overnight and multi-day tickets.

When averaging, leave out tickets that are still open — their blank resolved cell would otherwise produce a large negative time. =SUMPRODUCT((D2:D11<>"")*(D2:D11-C2:C11))*24/COUNT(D2:D11) totals only resolved tickets and divides by how many there are. Add a priority condition to check service-level targets, such as "high-priority tickets resolved within 8 hours".

=(D4-C4)*24