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.
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.
| Case | Channel | Received | First human reply | Minutes | Within 30 min? |
|---|---|---|---|---|---|
| S-101 | 08:12 | 08:16 | 4 | Yes | |
| S-102 | 08:35 | 08:47 | 12 | Yes | |
| S-103 | X | 09:00 | 09:07 | 7 | Yes |
| S-104 | 09:26 | 10:21 | 55 | No | |
| S-105 | 09:50 | 10:08 | 18 | Yes | |
| S-106 | X | 10:28 | 10:58 | 30 | Yes |
| S-107 | 10:55 | 11:04 | 9 | Yes | |
| S-108 | X | 11:40 | 12:02 | 22 | Yes |
| S-109 | 12:02 | 14:22 | 140 | No | |
| S-110 | 12:50 | 13:05 | 15 | Yes | |
| S-111 | X | 13:35 | 13:41 | 6 | Yes |
| S-112 | 14:20 | 15:01 | 41 | No | |
| S-113 | 15:05 | 15:16 | 11 | Yes | |
| S-114 | X | 16:00 | 16:27 | 27 | Yes |
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.
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.
| Measure | Formula | Result |
|---|---|---|
| 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.
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
- Enter the target in a single cell (C2) and give the received and reply columns the date-time format.
- In E4 type
=ROUND((D4-C4)*1440,0)and copy the formula down to E17. - In F4 type
=IF(E4<=$C$2,"Yes","No")and copy it down to F17. - Under the table calculate the average, the share within target and the median.
- Compare average and median. If they differ by much, look for the slow case before you report.
Common mistakes and how to spot them
- Forgetting the unit.
=D4-C4returns 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.
- Resolution Time by Topic: Hours from Open to Close, a Resolution Target, AVERAGEIFS per Topic and the Longest Case
- Service Levels by Channel: Target Lookup per Channel, a Met-or-Missed Flag, Attainment with COUNTIFS and a Warning Rule
- Volume by Hour: Weekday Averages, Share of the Week, the Peak Hour with INDEX/MATCH, the Busiest Day and a Column Chart
- Staffing Requirement: Workload Hours, Agents Needed at a Target Occupancy, the Roster Gap and Shortage Highlighting
- Occupancy and Idle Time: Busy Minutes, Hourly Occupancy, Overloaded and Underused Hours, a Weighted Day Figure and a Line Chart
- Deflection to Help Content: Self-Served Sessions, Self-Service Rate, Contacts Avoided and the Cost Avoided
- Help-Content Priorities: Share of Contacts by Topic, Rank, Contacts a New Article Could Avoid and the Top-Three Concentration
- Sentiment Before and After: Score Change per Case, a Threshold for Real Change, Outcome Labels, the Average Change and the Share Improved
- Reply Speed and Sentiment: Speed Bands with COUNTIFS, Average Sentiment Change per Band, CORREL and a Column Chart
- Escalation Rate by Topic: Rate, Target Check, Escalations Above Target with MAX, the Weighted Overall Rate and an Above-Average Rule
- What Escalations Cost: Cost per Case from Tier-1 and Tier-2 Minutes, Total Cost by Reason, Avoidable Escalations and the Weighted Average
- Cost per Contact by Channel: Labour and Tool Cost, Cost per Contact, an Index versus Phone, the Blended Cost and a Bar Chart
- Cost per Resolution: Contacts per Resolved Case, Cost per Resolution, Savings at a Target, a Rank and the Total Opportunity
- Agent Productivity and Quality: Cases per Hour, Team Rates, a Review-Weighted Quality Score and a Fair Coaching Flag
- 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
- Weekly Volume Forecast: SLOPE and FORECAST.LINEAR, a Campaign Uplift, Agents Needed from the Forecast and a Trend Chart
- Is the Gain Real? Paired Differences, an Average with its 95 % Margin of Error (CONFIDENCE.T), a Paired T-Test and 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
- Final Project: Social Care Scorecard by Month with SLA Attainment, Escalation Rate, Cost per Contact, a Cap Flag and a Combo Chart