Tabellab

First Response Time: Minutes from Message to First Human Reply, a Target Check, Average, Median and Share Within Target

Lesson 1 of 20Lab

All lessons
  1. First Response Time: Minutes from Message to First Human Reply, a Target Check, Average, Median and Share Within TargetLabFree
  2. Resolution Time by Topic: Hours from Open to Close, a Resolution Target, AVERAGEIFS per Topic and the Longest CaseLab Locked
  3. Service Levels by Channel: Target Lookup per Channel, a Met-or-Missed Flag, Attainment with COUNTIFS and a Warning RuleLab Locked
  4. Volume by Hour: Weekday Averages, Share of the Week, the Peak Hour with INDEX/MATCH, the Busiest Day and a Column ChartLab Locked
  5. Staffing Requirement: Workload Hours, Agents Needed at a Target Occupancy, the Roster Gap and Shortage HighlightingLab Locked
  6. Occupancy and Idle Time: Busy Minutes, Hourly Occupancy, Overloaded and Underused Hours, a Weighted Day Figure and a Line ChartLab Locked
  7. Deflection to Help Content: Self-Served Sessions, Self-Service Rate, Contacts Avoided and the Cost AvoidedLab Locked
  8. Help-Content Priorities: Share of Contacts by Topic, Rank, Contacts a New Article Could Avoid and the Top-Three ConcentrationLab Locked
  9. Sentiment Before and After: Score Change per Case, a Threshold for Real Change, Outcome Labels, the Average Change and the Share ImprovedLab Locked
  10. Reply Speed and Sentiment: Speed Bands with COUNTIFS, Average Sentiment Change per Band, CORREL and a Column ChartLab Locked
  11. Escalation Rate by Topic: Rate, Target Check, Escalations Above Target with MAX, the Weighted Overall Rate and an Above-Average RuleLab Locked
  12. What Escalations Cost: Cost per Case from Tier-1 and Tier-2 Minutes, Total Cost by Reason, Avoidable Escalations and the Weighted AverageLab Locked
  13. Cost per Contact by Channel: Labour and Tool Cost, Cost per Contact, an Index versus Phone, the Blended Cost and a Bar ChartLab Locked
  14. Cost per Resolution: Contacts per Resolved Case, Cost per Resolution, Savings at a Target, a Rank and the Total OpportunityLab Locked
  15. Agent Productivity and Quality: Cases per Hour, Team Rates, a Review-Weighted Quality Score and a Fair Coaching FlagLab Locked
  16. First-Contact Resolution and Reopens: a 1/0 Flag with IF and AND, FCR Rate, Reopen Rate, Messages per Case by Topic and Long ThreadsLab Locked
  17. Weekly Volume Forecast: SLOPE and FORECAST.LINEAR, a Campaign Uplift, Agents Needed from the Forecast and a Trend ChartLab Locked
  18. Is the Gain Real? Paired Differences, an Average with its 95 % Margin of Error (CONFIDENCE.T), a Paired T-Test and a VerdictLab Locked
  19. What-If Grid: Monthly Care Cost for Every Deflection Rate and Cost per Contact, a Heat Map, Net Saving, Break-Even and Scenario CountLab Locked
  20. Final Project: Social Care Scorecard by Month with SLA Attainment, Escalation Rate, Cost per Contact, a Cap Flag and a Combo ChartLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Loading the spreadsheet…

Why the first reply matters

If you answer customers on social channels, the first reply is the moment they find out whether anyone is listening. A message about a broken connection, a wrong bill or a missing parcel is public, short and written in a hurry. The customer then waits, and the minutes feel longer than they are. A fast, human first reply does not solve the problem, but it changes the mood of everything that follows.

That is why care teams measure the first response time: the time between the moment a message arrives and the moment a person sends the first useful answer. It is the simplest number in customer care, and it is a good first Excel exercise because it forces you to work with date-times, to convert units and to summarise a skewed set of numbers honestly.

The example in this lesson is Lumenpath Broadband, a fictional home-internet provider. All customers, cases, times and targets are invented for learning. You will use the same fourteen cases as in the lab above, all received on Monday 2 November 2026.

The core concept: the response clock

A response clock starts when the customer's message arrives and stops at the first human reply. Three details decide whether your numbers are honest. First, an automatic acknowledgement such as "thanks, we got your message" does not stop the clock, because it tells the customer nothing. Second, a reply that only asks the customer to move to another channel is a judgement call; write down your rule and stick to it. Third, business hours: a message at 23:00 should not count as a 9-hour miss if the team starts at 08:00, so many teams count only open hours. This lab keeps it simple and uses cases received during opening hours.

A case also has a second clock: the resolution time, from the first message until the problem is solved and the case is closed. The two clocks answer different questions. The first asks "did we show up quickly?", the second asks "did we fix it?". Lesson 2 covers the second one.

Two clocks on one caseMessage arrives12:02Automatic replydoes not countFirst human reply14:22Case closednext dayFirst response time = 140 minutesResolution time = from the first message to closing
Figure 1. The two clocks on one case. The first response time ends at the first human reply (here S-109, 140 minutes after the message arrived); the resolution time runs until the case is closed. The automatic reply stops neither clock.

Do not game the number. Sending an empty "we are looking into it" reply to stop the clock raises your attainment and lowers the customer's trust. Count only replies that move the case forward.

Targets: what does within target mean?

A number without a target is hard to judge. A target (often called a service level) states a channel and a time, for example "first human reply within 30 minutes". Teams set it from what customers expect on that channel and from what the roster can really deliver. The target belongs in one cell, here C2, so that you can change it and see every result move. Never type 30 into dozens of formulas.

The test for one case is simply minutes <= target. The <=, not <, matters: a reply after exactly 30 minutes meets a 30-minute promise. Cases that exactly hit the target are common, because people answer just before the deadline.

A fully worked example: Lumenpath Broadband on 2 November 2026

Here are the fourteen cases of the lab. The times were read from the inbox: received and first human reply. The last two columns are what the formulas produce.

CaseChannelReceivedFirst human replyMinutesWithin 30 min?
S-101Facebook08:1208:164Yes
S-102Instagram08:3508:4712Yes
S-103X09:0009:077Yes
S-104Facebook09:2610:2155No
S-105Instagram09:5010:0818Yes
S-106X10:2810:5830Yes
S-107Facebook10:5511:049Yes
S-108X11:4012:0222Yes
S-109Instagram12:0214:22140No
S-110Facebook12:5013:0515Yes
S-111X13:3513:416Yes
S-112Instagram14:2015:0141No
S-113Facebook15:0515:1611Yes
S-114X16:0016:2727Yes

How the minutes are calculated. Excel stores a date-time as one number: the whole part counts days, the fraction counts the time of day. Subtracting received from reply gives a fraction of a day, for example 0.0382 for S-104. One day has 24 × 60 = 1,440 minutes, so the formula is =ROUND((D4-C4)*1440,0). The ROUND removes tiny floating-point leftovers such as 54.99999999.

  • Minutes to first reply (E4): =ROUND((D4-C4)*1440,0), copied down to E17. S-104 was received at 09:26 and answered at 10:21, so 55 minutes.
  • Within target? (F4): =IF(E4<=$C$2,"Yes","No"), copied down to F17. The dollar signs lock the target in C2 so it stays put in every row.
  • Average (E19): =AVERAGE(E4:E17). Median (E21): =MEDIAN(E4:E17). Share within target (E20): =COUNTIF(F4:F17,"Yes")/COUNT(E4:E17).

Walk through a few rows. S-101 was a Facebook post answered after 4 minutes: easily within target. S-106, an X post, was answered after exactly 30 minutes and still counts as met because the test is <=. S-109 on Instagram arrived at 12:02, in the middle of lunch, and was only answered at 14:22, 140 minutes later. That one case is the story of the day.

Minutes to the first reply, 2 November 2026Within the 30-minute targetOver the target4S-101 Facebook12S-102 Instagram7S-103 X55S-104 Facebook18S-105 Instagram30S-106 X9S-107 Facebook22S-108 X140S-109 Instagram15S-110 Facebook6S-111 X41S-112 Instagram11S-113 Facebook27S-114 Xtarget 300306090120Minutes from message to first human reply (fictional cases)
Figure 2. Minutes to the first reply per case. Green bars are within the 30-minute target, peach bars are over it. Three cases miss the target: S-104 (55 minutes), S-109 (140 minutes) and S-112 (41 minutes).

Average, median and share within target

Now summarise the day. The three formulas below answer three different questions, and a good care report quotes more than one of them.

MeasureFormulaResult
Average minutes to first reply=AVERAGE(E4:E17)28.4
Median minutes to first reply=MEDIAN(E4:E17)16.5
Share replied within target=COUNTIF(F4:F17,"Yes")/COUNT(E4:E17)11 of 14 = 78.6 %
Slowest case=MAX(E4:E17)140 (S-109)

The average is 28.4 minutes, which is just inside the 30-minute target and could be read as "fine". The median, the middle case when the minutes are sorted, is only 16.5 minutes: half of the customers got an answer within about a quarter of an hour. The gap exists because one case waited 140 minutes and pulls the average up. The honest picture is the share within target: 11 of 14 cases, 78.6 %, which means that one customer in five was not served within the promise.

One day, four ways to read it (minutes)16.5Median28.4Average30Target140Slowest caseThe median ignores the 140-minute case, the average does not
Figure 3. The same day read four ways. The median (16.5), the average (28.4) and the slowest case (140) differ a lot; the target (30) sits in between. Quote the median and the share within target, and name the slowest case.

How to read the result

The three misses were received at 09:26, 12:02 and 14:20, so they are spread over the day. With only fourteen cases this proves nothing about a bad hour, but it is exactly the question you chase in the next lessons: volume by hour (lesson 4) and staffing (lesson 5) explain why a team can be fast most of the day and still miss the occasional message.

Also note what the numbers do not tell you. A fast first reply says nothing about whether the problem was solved or whether the customer felt better. A team can have a perfect first-response score and a poor resolution rate. That is why this course builds a full set of measures: response, resolution, volume, staffing, deflection, sentiment, escalation and cost.

Step by step in Excel

  1. Enter the target in a single cell (C2) and give the received and reply columns the date-time format.
  2. In E4 type =ROUND((D4-C4)*1440,0) and copy the formula down to E17.
  3. In F4 type =IF(E4<=$C$2,"Yes","No") and copy it down to F17.
  4. Under the table calculate the average, the share within target and the median.
  5. Compare average and median. If they differ by much, look for the slow case before you report.
Check your minutes against a clock before you trust the formula: pick one case, subtract the times by hand and compare. A wrong multiplier (24 instead of 1440) is the most common error and it looks plausible.

Common mistakes and how to spot them

  • Forgetting the unit. =D4-C4 returns a fraction of a day (0.038). Multiply by 1,440 for minutes and by 24 for hours.
  • Counting the automatic reply. If your inbox tool exports the time of the auto-acknowledgement, the average will look wonderful and mean nothing.
  • Using < instead of <=. A reply after exactly 30 minutes meets a 30-minute target.
  • Reporting only the average. One very slow case can hide behind a respectable mean. Add the median and the share within target.
  • Typing the target into every formula. Keep it in one locked cell, so the report updates when the promise changes.

Quick reference: functions in this lesson

ROUND(x, 0) rounds to whole numbers. IF(test, a, b) returns a or b. AVERAGE adds and divides by the count. MEDIAN returns the middle value of the sorted numbers. COUNTIF(range, "Yes") counts cells equal to "Yes", and COUNT counts numbers. Date-times are numbers: one day = 1, one hour = 1/24, one minute = 1/1440.

Real-world uses

  • A weekly care report that shows median first reply and attainment per channel.
  • A staffing review that compares slow cases with the busiest hours.
  • A contract or internal promise that says "90 % of messages answered within 30 minutes", tested with the same share formula.
  • A before-and-after check when a team introduces saved replies or a new rota.

What you will build in the rest of this course

The remaining nineteen labs use new fictional data for the same company and add one skill at a time. Together they cover the whole operating picture of social customer care.

  • Resolution Time by Topic: Hours from Open to Close, a Resolution Target, AVERAGEIFS per Topic and the Longest Case. You turn opened and closed timestamps into hours to resolve, test them against a target, average them per topic with AVERAGEIFS and find the longest case.
  • Service Levels by Channel: Target Lookup per Channel, a Met-or-Missed Flag, Attainment with COUNTIFS and a Warning Rule. You look up a response target per channel, flag every message as met or missed, calculate the attainment per channel with COUNTIFS and highlight channels below the goal.
  • Volume by Hour: Weekday Averages, Share of the Week, the Peak Hour with INDEX/MATCH, the Busiest Day and a Column Chart. You summarise a week of inbound messages by hour and weekday, calculate hourly shares, find the peak hour and the busiest day with INDEX/MATCH and chart the daily profile.
  • Staffing Requirement: Workload Hours, Agents Needed at a Target Occupancy, the Roster Gap and Shortage Highlighting. You turn contacts and handling time into workload hours and agents needed, compare them with the roster, highlight understaffed hours and add up the agent-hours short.
  • Occupancy and Idle Time: Busy Minutes, Hourly Occupancy, Overloaded and Underused Hours, a Weighted Day Figure and a Line Chart. You calculate busy minutes and hourly occupancy, label hours as overloaded, OK or underused, compute the weighted occupancy of the whole day and chart the daily profile.
  • Deflection to Help Content: Self-Served Sessions, Self-Service Rate, Contacts Avoided and the Cost Avoided. You turn help-centre sessions from social links into self-served sessions and a self-service rate, estimate the contacts avoided with a stated assumption and price them with the cost per contact.
  • Help-Content Priorities: Share of Contacts by Topic, Rank, Contacts a New Article Could Avoid and the Top-Three Concentration. You rank care topics by contacts, estimate what a new help article could avoid for topics that have none, measure the concentration of the top three topics with LARGE and chart the drivers.
  • Sentiment Before and After: Score Change per Case, a Threshold for Real Change, Outcome Labels, the Average Change and the Share Improved. You calculate the sentiment change of each care case, label it Improved, Same or Worse against a threshold, and summarise the average change and the share of cases that improved.
  • Reply Speed and Sentiment: Speed Bands with COUNTIFS, Average Sentiment Change per Band, CORREL and a Column Chart. You group cases into reply-speed bands, count them and average their sentiment change with COUNTIFS and AVERAGEIFS, measure the correlation with CORREL and chart the result, with a warning about causation.
  • Escalation Rate by Topic: Rate, Target Check, Escalations Above Target with MAX, the Weighted Overall Rate and an Above-Average Rule. You calculate escalation rates per topic, flag topics above the target, count the escalations above target, compute the weighted overall rate and highlight above-average topics.
  • What Escalations Cost: Cost per Case from Tier-1 and Tier-2 Minutes, Total Cost by Reason, Avoidable Escalations and the Weighted Average. You price escalated cases from the minutes spent at two tiers, total the cost by reason, add up the avoidable escalations and compute the weighted average cost per escalated case.
  • Cost per Contact by Channel: Labour and Tool Cost, Cost per Contact, an Index versus Phone, the Blended Cost and a Bar Chart. You price each channel from agent hours and tool costs, calculate the cost per contact, index every channel against phone, compute the blended cost per contact and chart the comparison.
  • Cost per Resolution: Contacts per Resolved Case, Cost per Resolution, Savings at a Target, a Rank and the Total Opportunity. You turn contacts and resolved cases into contacts per resolution and cost per resolution, rank topics by it and estimate the saving if each topic reached a target.
  • Agent Productivity and Quality: Cases per Hour, Team Rates, a Review-Weighted Quality Score and a Fair Coaching Flag. You calculate cases per hour, build the team rates with correct weighting, compute a review-weighted quality score and flag agents for coaching only when enough reviews exist.
  • First-Contact Resolution and Reopens: a 1/0 Flag with IF and AND, FCR Rate, Reopen Rate, Messages per Case by Topic and Long Threads. You flag cases solved at first contact using two conditions, calculate the FCR rate, the reopen rate and the share of long threads, and average the messages per case by topic.
  • Weekly Volume Forecast: SLOPE and FORECAST.LINEAR, a Campaign Uplift, Agents Needed from the Forecast and a Trend Chart. You fit a trend to twelve weeks of contacts with SLOPE and FORECAST.LINEAR, adjust the forecast for a planned campaign, convert it into agents needed and chart the history.
  • Is the Gain Real? Paired Differences, an Average with its 95 % Margin of Error (CONFIDENCE.T), a Paired T-Test and a Verdict. You compare matched pairs of cases answered with an old and a new reply template, calculate the average difference with its margin of error, run a paired t-test and turn the p-value into a verdict.
  • What-If Grid: Monthly Care Cost for Every Deflection Rate and Cost per Contact, a Heat Map, Net Saving, Break-Even and Scenario Count. You build a two-way what-if grid with mixed references, shade it as a heat map, calculate the net saving and break-even deflection rate of a plan and count the scenarios cheaper than today.
  • Final Project: Social Care Scorecard by Month with SLA Attainment, Escalation Rate, Cost per Contact, a Cap Flag and a Combo Chart. You build a ten-month scorecard from raw counts, calculate SLA attainment, escalation rate and cost per contact, compute the weighted year-to-date unit cost, flag months above a cost cap and chart volume against cost.

Summary

The first response time is =ROUND((reply-received)*1440,0): the minutes between a message and the first human reply. Compare it with a target stored in one cell using <=, then summarise with the average (28.4 minutes), the median (16.5) and the share within target (78.6 %). At Lumenpath Broadband 11 of 14 cases met the 30-minute target, and the 140-minute lunch-time case shows why an average alone is not enough.

Now scroll up to the lab and build the sheet yourself. It takes about fifteen minutes, the Coach checks each step, and you will end up with a response report you can apply to your own inbox export today.

Frequently asked questions

What is first response time in customer care?

It is the time between the arrival of a customer message and the first reply from a person that moves the case forward. Automatic acknowledgements are normally excluded, because they do not help the customer.

How do I calculate response time in minutes in Excel?

Subtract the received date-time from the reply date-time and multiply by 1,440, for example =ROUND((D4-C4)*1440,0). Excel stores a day as 1, so a minute is 1/1440.

Should I report the average or the median first response time?

Report both. The average is pulled up by a few very slow cases, the median shows the typical customer. Add the share of cases within your target, because it is the number customers actually experience.

What is a good first response time on social media?

There is no universal number. Targets depend on the channel, the industry and the roster, and customers expect public posts to be answered faster than private messages. Set a target you can meet and measure it consistently.

Does an automatic reply count as a first response?

Most care teams say no. The clock stops at the first human reply that moves the case forward. Whatever rule you choose, apply it to every case and write it next to the report.

How do I count how many cases met the target?

Mark each case with =IF(E4<=$C$2,"Yes","No") and divide =COUNTIF(F4:F17,"Yes")/COUNT(E4:E17). Use <= so that a reply exactly on the target counts as met.

Keep going: Social Customer Care: First-Response & Resolution Times, Volume & Staffing, Deflection, Sentiment, Escalation & Cost per Contact

This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.

  1. Resolution Time by Topic: Hours from Open to Close, a Resolution Target, AVERAGEIFS per Topic and the Longest Case
  2. Service Levels by Channel: Target Lookup per Channel, a Met-or-Missed Flag, Attainment with COUNTIFS and a Warning Rule
  3. Volume by Hour: Weekday Averages, Share of the Week, the Peak Hour with INDEX/MATCH, the Busiest Day and a Column Chart
  4. Staffing Requirement: Workload Hours, Agents Needed at a Target Occupancy, the Roster Gap and Shortage Highlighting
  5. Occupancy and Idle Time: Busy Minutes, Hourly Occupancy, Overloaded and Underused Hours, a Weighted Day Figure and a Line Chart
  6. Deflection to Help Content: Self-Served Sessions, Self-Service Rate, Contacts Avoided and the Cost Avoided
  7. Help-Content Priorities: Share of Contacts by Topic, Rank, Contacts a New Article Could Avoid and the Top-Three Concentration
  8. Sentiment Before and After: Score Change per Case, a Threshold for Real Change, Outcome Labels, the Average Change and the Share Improved
  9. Reply Speed and Sentiment: Speed Bands with COUNTIFS, Average Sentiment Change per Band, CORREL and a Column Chart
  10. Escalation Rate by Topic: Rate, Target Check, Escalations Above Target with MAX, the Weighted Overall Rate and an Above-Average Rule
  11. What Escalations Cost: Cost per Case from Tier-1 and Tier-2 Minutes, Total Cost by Reason, Avoidable Escalations and the Weighted Average
  12. Cost per Contact by Channel: Labour and Tool Cost, Cost per Contact, an Index versus Phone, the Blended Cost and a Bar Chart
  13. Cost per Resolution: Contacts per Resolved Case, Cost per Resolution, Savings at a Target, a Rank and the Total Opportunity
  14. Agent Productivity and Quality: Cases per Hour, Team Rates, a Review-Weighted Quality Score and a Fair Coaching Flag
  15. First-Contact Resolution and Reopens: a 1/0 Flag with IF and AND, FCR Rate, Reopen Rate, Messages per Case by Topic and Long Threads
  16. Weekly Volume Forecast: SLOPE and FORECAST.LINEAR, a Campaign Uplift, Agents Needed from the Forecast and a Trend Chart
  17. Is the Gain Real? Paired Differences, an Average with its 95 % Margin of Error (CONFIDENCE.T), a Paired T-Test and a Verdict
  18. What-If Grid: Monthly Care Cost for Every Deflection Rate and Cost per Contact, a Heat Map, Net Saving, Break-Even and Scenario Count
  19. Final Project: Social Care Scorecard by Month with SLA Attainment, Escalation Rate, Cost per Contact, a Cap Flag and a Combo Chart

Buy course — $9.90 See the full course