Complaint Ticket Management System in Excel: Priorities, Due-by Time, Hours Taken, Met or Breached and Agent Report

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: complaint-ticket-management-system-template.xlsx: a 6-sheet system with example data and real formulas (Read me, Setup, Tickets, Report, Ticket slip, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Salman Appliances Service, Solapur, as of 17 October 2026, 10:00 am (made-up data). High 24 hours, Medium 48, Low 72.

Average time to resolve: 32 hours across 11 closed tickets.

2. Build it sheet by sheet

Step 1. Setup: priorities

1. B3 as-of date and time. Priorities with hours in A6:B8; categories in D6:D10; agents in F6:F8.

Step 2. Tickets: due by and status

1. A5 ="TK-"&TEXT(ROW()-4,"000"). Type opened date and time, customer, category, priority and agent from drop-downs.
2. G5 due by =B5+XLOOKUP(E5,Setup!$A$6:$A$8,Setup!$B$6:$B$8,0)/24: hours ÷ 24 turns them into days (time calculations).
3. I5 hours taken =IF(H5="","",ROUND((H5-B5)*24,1)).
4. J5: =IF(H5<>"",IF(H5<=G5,"Met","Breached"),IF(Setup!$B$3>G5,"Overdue","Open")), red for Breached and Overdue.

Step 3. Hours open

1. K5 =IF(H5="",ROUND((Setup!$B$3-B5)*24,1),"") shows how long a pending complaint has waited.

Step 4. Report

1. By agent: tickets, open, overdue, resolved and met with COUNTIFS; G5 met % =IFERROR(F5/E5,0); H5 average hours with AVERAGEIFS.
2. By category: tickets, open or overdue, breached.

Step 5. Ticket slip and Dashboard

1. Ticket slip: type a ticket no in B4. Set the print area.
2. KPI cards: tickets, open, overdue, met %, average hours and breached. Charts: tickets by category and average hours by agent.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Typing only the date. Type date and time (17-10-2026 10:00). Hours need the time.
2. Adding hours to a date directly. Dates count days. Divide hours by 24 before adding.
3. As-of time left old. Update Setup B3 before review, or Open and Overdue are out of date.
4. Closing a ticket without a time. Type the resolved date and time; it decides Met or Breached.
5. Average over open tickets. AVERAGEIFS over hours taken skips blanks, so open tickets do not drag it down.

5. More real examples

Example 1: IT help desk. Priorities by impact; categories become hardware, software, network, access.

Example 2: Housing society complaints. Plumbing, lift, security; agents are staff or vendors, like the housing society system.

Example 3: Courier claims. Lost or damaged shipments with a response time, beside the courier system.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I add hours to a date-time in Excel?

Add hours ÷ 24: =B5+24/24 adds one day.

How do I calculate hours between two date-times?

=(end − start)*24, rounded to one decimal.

How do I show Met or Breached?

Compare resolved with due by: on or before is Met, after is Breached.

How do I show overdue open complaints?

No resolved time and the as-of time after due by means Overdue.

How do I find average resolution time per agent?

AVERAGEIFS over hours taken where the agent matches.

7. Quick quiz

Ten questions on this page. Sign in to save your score.