Helpdesk dashboard: backlog, response time, and resolution

A helpdesk dashboard compares incoming tickets, unresolved backlog, first response time, and resolution time by team. Start with a ticket export and a fixed reporting cutoff, then reconcile ticket movement before interpreting speed. Separate the tickets created during the period from those resolved during it.

Start with a ticket export and a reporting rule

Take one row per ticket, with a unique ticket ID, accountable team, creation timestamp, first human response timestamp, and resolution timestamp. Exclude automated acknowledgments from first response. Keep unanswered and unresolved timestamps blank rather than replacing them with zero.

For this worked example, a US distributor reviews January 5 through January 6, 2026. The start is January 5 at midnight; the exclusive cutoff is January 7 at midnight. All timestamps use the same time zone. Durations use calendar hours, including overnight time.

The example assumes no reopened, deleted, or transferred tickets. Team means the same accountable team throughout the period. If ownership changes matter, the separate ticket handoff and queue-delay analysis addresses that history.

Paste these fields into columns A through E in Google Sheets. The dash below means an empty spreadsheet cell.

Calculate durations without changing the cohort

In F2 calculate first response hours; in G2 calculate resolution hours. Fill both down through row 7. Spreadsheet timestamps represent days, so multiplying the difference by 24 converts it to hours.

F2: =IF(D2="","",(D2-C2)*24)
G2: =IF(E2="","",(E2-C2)*24)

First response performance here uses tickets created during the reporting period that received a response before the cutoff. Resolution performance uses tickets resolved during the period, including older arrivals. T03 therefore belongs in resolution time but not the new-ticket response calculation. Those cohorts answer different management questions. Check that response and resolution timestamps are no earlier than creation; a negative duration signals a data error.

Put the start timestamp in Q1 and the exclusive cutoff in Q2. Put Billing in J2 and Technical in J3. These formulas return new tickets and resolved tickets for the team in J2:

K2: =COUNTIFS($B$2:$B$7,J2,$C$2:$C$7,">="&$Q$1,$C$2:$C$7,"<"&$Q$2)
L2: =COUNTIFS($B$2:$B$7,J2,$E$2:$E$7,">="&$Q$1,$E$2:$E$7,"<"&$Q$2)

Google's COUNTIFS documentation confirms that the function counts rows meeting multiple criteria and requires equally sized criteria ranges. Keep the dates as actual timestamp values, rather than text labels that only look like dates.

Count backlog at the cutoff

A ticket is open at the cutoff when it was created before that time and either has no resolution timestamp or was resolved at or after the cutoff. The two branches below are mutually exclusive:

M2: =COUNTIFS($B$2:$B$7,J2,$C$2:$C$7,"<"&$Q$2,$E$2:$E$7,"")+COUNTIFS($B$2:$B$7,J2,$C$2:$C$7,"<"&$Q$2,$E$2:$E$7,">="&$Q$2)

For opening backlog, use the same formula with Q1 in place of Q2. Fill each team formula down. The resulting movement table is:

The check is 2 + 4 − 3 = 3. The remaining ticket IDs are T02, T05, and T06, which also count to 3. Reconcile both the movement and the actual unresolved list; a matching headline alone can hide offsetting errors.

Compare speed alongside counts

Use these conditional averages for the team in J2:

N2: =IFERROR(AVERAGEIFS($F$2:$F$7,$B$2:$B$7,J2,$C$2:$C$7,">="&$Q$1,$C$2:$C$7,"<"&$Q$2,$D$2:$D$7,"<>",$D$2:$D$7,"<"&$Q$2),"")
O2: =IFERROR(AVERAGEIFS($G$2:$G$7,$B$2:$B$7,J2,$E$2:$E$7,">="&$Q$1,$E$2:$E$7,"<"&$Q$2),"")

Google documents AVERAGEIFS as averaging values subject to multiple criteria. The blank result above means no qualifying records; it does not mean instant service. Covirage is not affiliated with Google.

Total response time is (1 + 2 + 4 + 6) / 4 = 3.25 hours. Total resolution time is (6 + 48 + 24) / 3 = 26.00 hours. Display averages to two decimal places and calculate them from ticket durations, without averaging team averages.

The service manager should investigate Technical's slower response and growing backlog. Billing's longer resolution average includes an older ticket being cleared, which may be progress. Check repeat contacts before treating faster resolution as better service. Compare contractual commitments through the separate SLA report method, using its required clock and exclusions.

Where it goes wrong

• Current status used as history. Reopening makes the simple timestamp reconstruction incomplete. Add the actual status events before claiming historical backlog.
• Unanswered tickets disappearing. An answered-ticket average can improve while unanswered work grows. Display the unanswered count beside it.
• Different clocks compared. Calendar-hour results cannot be read against business-hour targets without conversion under the agreed schedule.
• Changing team definitions. Current owner, owner at arrival, and owner at resolution produce different team totals. Choose and label one basis.

Work through your own ticket file in Covirage

Bring a ticket export with IDs, teams, and event timestamps to Covirage. Its tools map the columns, compute and check the figures, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export; account creation takes about a minute.

See Covirage for customer service teams for the supporting business context.