How to Create a First Response Time Report: Complete Guide

First response time is how long a customer waits between sending a ticket and getting the first human reply. A first response time report tracks that wait over weeks, split by median and 90th percentile rather than by average. Build it from a ticket export with two timestamps, and the whole thing takes one prompt or three formulas.

The measurement is simple. The reporting is where teams quietly mislead themselves, because the default statistic and the default percentile function both hide the customers who waited longest.

What first response time means, and how to calculate it

First response time (FRT) is the elapsed time between a ticket arriving and an agent's first reply to the customer. The formula is as plain as it looks:

First response time = timestamp of first human reply − timestamp of ticket creation

Two decisions turn that one-line formula into something a team can actually agree on.

Does an automated acknowledgement count? It should not. An auto-reply that says "we got your message" is not a response to the question. Counting it produces a beautiful chart and an unhappy customer base. This is usually an honest mistake rather than a deception, and it is the single most common way FRT gets inflated.

Does the clock run overnight? On a calendar clock, a Friday evening ticket answered Monday morning is a 60-hour failure. On a business clock it may be under an hour. Neither is wrong. Quoting one while your colleague quotes the other is what starts the argument.

Average FRT is the third decision, and the one most tools make for you. Response times are right-skewed: most tickets are answered quickly and a small tail waits a very long time. The mean gets dragged into a region almost nobody experienced. Report the median and the 90th percentile instead.

What you need before you start

• A ticket export with one row per conversation.
• Two timestamps: when the ticket arrived, and when an agent first replied.
• A definition of "first response" that excludes auto-acknowledgements and internal notes.
• Your support hours, if you intend to report on a business clock.
• A minimum volume rule, so thin weeks do not produce confident-looking nonsense.

Decide the last three before you build anything. Changing them later invalidates every earlier week on the chart, and a trend line built on a shifting definition is worse than no trend line.

How to build the report in a spreadsheet

Option 1: turn two timestamps into a duration

Subtract the arrival timestamp from the first-reply timestamp and format the result as hours. That is the raw first response time for each ticket, on a calendar clock.

If you need a business clock instead, NETWORKDAYS.INTL is the usual starting point. Microsoft's documentation is precise about what it returns. The function gives "the number of whole workdays between two dates," using parameters that indicate which days count as weekend days. Weekend days and any days named in the holidays argument "are not considered as workdays."

Note the phrase whole workdays. The function answers in days, not hours, so a four-hour wait and a seven-hour wait inside one shift look identical to it. Sub-day durations need a construction that combines the workday count with the time-of-day remainder. That is why business-hours reporting is a project rather than a formula.

Option 2: compute the median and the 90th percentile

MEDIAN gives you the typical wait. For the tail, Excel offers two percentile functions, and this is where the same data produces two different answers.

PERCENTILE.EXC accepts a k value "in the range 0..1, exclusive." Its documentation states that "if k is not a multiple of 1/(n + 1), PERCENTILE.EXC interpolates to determine the value at the k-th percentile." PERCENTILE.INC accepts k "in the range 0 to 1, inclusive," and interpolates when k "is not a multiple of 1/(n - 1)."

Different denominators, different interpolation, different p90. Neither is wrong. They are two conventions, and a report that silently switches between them is telling a story about your formula rather than your queue.

There is a sharper edge on the exclusive version. Its documentation warns that "if it cannot interpolate for the percentile, k specified, Excel will return #NUM! error." On a thin week, a handful of tickets in one queue, a p90 can simply fail to compute.

Option 3: lay out the report

Group tickets by week. Put median and p90 side by side as two lines, and add the ticket count as a bar behind them. The count is not decoration. It is what stops a reader from over-reading a spike built on eleven tickets.

Then add one line of text stating the clock and the percentile method. That sentence is what makes the report reproducible next quarter by someone who was not in the room.

Where the spreadsheet route slows down

None of the three steps is hard. The cost is that all three have to be redone every week, on an export whose column names change whenever someone edits the helpdesk view.

Three specific frictions repeat.

The reply column is rarely clean. Automated acknowledgements, internal notes, and agent replies often land in the same field, so the first row is not always the first human answer. Separating them is a per-export judgement, not a formula.

Percentiles need a decision, every time. Which function, which weeks have enough volume, and what to display when the calculation errors on a thin queue.

Business hours are maintenance. Once you commit to a support-hours clock, every public holiday and every shift change becomes a task in the workbook. Miss one and a whole week shifts.

The result is a report that is accurate the week it is built and quietly drifts afterwards.

How to create a first response time report with AI

Step 1: Upload the ticket export

Open Powerdrill Bloom and upload the export straight from your helpdesk. Excel, CSV, PDF, and docs are listed as supported uploads on the free plan, so a raw export can go in as it is.

Keep every timestamp column, including the automated ones. You need them to prove which reply counted, and to switch the definition later without re-exporting.

Step 2: State the clock and the statistics you want

Describe the measurement in natural language rather than in formulas. Name which timestamp starts the clock, which reply ends it, and whether weekends count. Then ask for the median and the 90th percentile by week, with the ticket count alongside.

Say what should happen on thin weeks too. Ask for the percentile to be suppressed below a minimum ticket count. That beats an error cell, and it beats a confident number built on four rows.

Each figure comes back with the row behind it, so a suspicious week can be opened and read rather than argued about.

Step 3: Generate the report and save the prompt

Ask for two lines and one bar series on a weekly axis, plus a note stating which percentile convention was used. Export it as an image, a sheet, or a slide.

Next week, upload the new export and run the same prompt. The definition stays fixed, which is the only way week-to-week comparison means anything. The make graphs from Excel page covers the charting route directly.

What belongs in a first response time report

Element	Why it belongs	What goes wrong without it
Median line	The typical customer's wait	Averages hide behind outliers
90th percentile line	The experience of your slowest tenth	Tail pain stays invisible
Ticket volume bars	Context for every movement	Small weeks read as trends
Stated clock	Calendar hours or business hours	Two teams quote different numbers
Stated percentile method	Reproducibility	The same week changes value between builds
Minimum-volume rule	Honesty on thin segments	Errors or fictional precision
Breakdown by channel	Where the tail actually lives	One bad queue drags the whole line

A worked example

Take one week with 240 tickets. The mean first response time is 5.1 hours, the median is 1.4 hours, and the p90 is 19.7 hours.

All three numbers are correct. The mean is the only one that describes nobody. Most customers waited under 90 minutes, and the slowest tenth waited most of a day. Present the mean alone and a reader concludes the queue is mediocre everywhere, when in fact it is fast with a bad tail. The fix for a bad tail is different from the fix for a slow queue. That is why the distinction is worth a chart rather than a single cell.

Segmenting without breaking the report

The obvious next question is which channel or queue is slowest. Splitting by segment is useful, and it is also the fastest way to make the report lie.

Two rules keep it honest. Only split where the weekly volume stays above your minimum, and keep the overall line on the chart so readers have a baseline. A queue with nine tickets a week deserves a monthly view, not a weekly one.

If the split matters more than the trend, a small table of median and p90 per queue will carry more information than five overlapping lines.

How to improve first response time

The report is only worth building if it points somewhere. Four levers show up repeatedly, and the report tells you which one you need.

A bad tail with a good median usually means coverage, not speed. Tickets arriving outside staffed hours, or in a queue with one specialist, sit until someone comes back. Look at arrival time-of-day before you look at agent performance.

A rising median with flat volume usually means the queue is absorbing a new ticket type nobody has a template for. The channel breakdown will show it.

A rising median with rising volume is a staffing question, and the volume bars are the argument.

A flat report that nobody trusts is a definition problem. Publish the clock and the percentile method at the top and the arguments stop.

For the volume and status view that sits beside this one, see our guides on the support ticket report and the CSAT report. The customer service chatbot page covers the deflection side of the same queue.

What a good target looks like

Teams often set a first response time target before they have a distribution, which is how targets end up either meaningless or unreachable.

Set two numbers instead of one, and publish both. A median target describes the normal experience, and a p90 target describes the worst experience you are willing to accept. A team at a 1.4 hour median and a 19.7 hour p90 has a tail problem. A single two-hour target would not surface it at all.

Then state the clock alongside the target. "Two hours" on a business clock and "two hours" on a calendar clock are different promises. Support and leadership will each assume the one that flatters their view.

Common mistakes

Reporting the average. It is the one statistic almost guaranteed to mislead on a right-skewed distribution, and it is the default in most spreadsheet tools.

Counting the auto-reply as the first response. This is the fastest way to a chart that looks excellent and matches nobody's experience.

Switching percentile functions mid-quarter. If your p90 improved the same week somebody edited the formula, you have measured the edit.

Mixing calendar and business clocks. A weekend on a calendar clock is 48 hours of failure. On a business clock it may be zero. Pick one and label it.

Dropping the volume series to reduce clutter. The bars are the reason anyone can tell a real regression from a quiet week.

Reporting a target without a distribution. A single "we hit two hours" number tells the reader nothing about the customers who waited nine.

Conclusion

A first response time report is worth building properly because it is one of the few support metrics customers actually feel. Two lines, one bar series, a stated clock, and a stated percentile convention will beat any dashboard that shows a single average.

If rebuilding it every week is what stops you, move the definition into a saved prompt and regenerate it from each new export. Try Powerdrill Bloom on last month's ticket export and see the median and the tail on the same axis.

Frequently asked questions

What is first response time in customer service?

It is the elapsed time between a customer's ticket arriving and the first reply from a human agent. Automated acknowledgements are normally excluded, because they do not answer the customer's question.

What is the first response time formula?

Subtract the ticket creation timestamp from the timestamp of the first human reply. For a business-hours version, start from NETWORKDAYS.INTL, which returns whole workdays and lets you define weekends and holidays, then add the time-of-day remainder.

Should a first response time report use mean or median?

Median, with the 90th percentile beside it. Response times are right-skewed, so a small number of very slow tickets pulls the mean toward a value that few customers actually experienced.

What is the difference between PERCENTILE.EXC and PERCENTILE.INC?

They use different interpolation rules. PERCENTILE.EXC interpolates when k is not a multiple of 1/(n + 1) and accepts k strictly between 0 and 1. PERCENTILE.INC uses 1/(n - 1) and accepts 0 and 1 themselves.

Why does my p90 return a #NUM! error?

PERCENTILE.EXC returns that error when the array is empty, or when k is at or outside the 0 to 1 bounds. It also returns it when it cannot interpolate for the percentile you asked for, which is what thin weeks trigger.