Challenge Scenario
In customer support operations, Service Level Agreements (SLAs) guarantee that customer inquiries must be fully resolved within a strict turnaround time (e.g. 24 hours). Tickets taking 24 hours or less pass SLA, while longer resolutions are flagged as breached.
As a Support Operations Manager, you are reviewing five escalated tickets:
- Resolution in Hours (Column D): Subtract Open Date from Resolved Date and multiply by 24 hours per day:
=(C2-B2)*24.
- SLA Status (Column E): If resolution is 24 hours or fewer (
D2 <= 24), assign "PASSED SLA"; otherwise assign "BREACHED".
- Average Resolution Time (Cell D8): Compute average resolution hours using
AVERAGE(D2:D6).
- Total Breached Tickets (Cell E8): Count tickets marked
"BREACHED" using COUNTIF(E2:E6, "BREACHED").
Your Sheet Layout
Understanding the support SLA monitoring table:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C6 |
Ticket, Open Date, Resolved Date |
Helpdesk Queue |
Ticket identifiers and resolution timestamps |
Read-only support ticket records. |
D2:D6 |
Resolution (Hrs) |
Elapsed Time |
=(C2-B2)*24 |
Calculates elapsed turnaround time in hours. |
E2:E6 |
SLA Status |
SLA Compliance |
=IF(D2<=24, "PASSED SLA", "BREACHED") |
Flags PASSED SLA vs BREACHED ticket status. |
D8 |
Average Resolution Time |
Summary |
=AVERAGE(D2:D6) |
Average ticket handling time (19.8 hours). |
E8 |
Breached SLA Count |
Summary |
=COUNTIF(E2:E6, "BREACHED") |
Total tickets violating 24-hour agreement (2). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells D2:D6, calculate resolution hours (= (Resolved Date - Open Date) * 24).
2
In cells E2:E6, assign status ("PASSED SLA" if <= 24, otherwise "BREACHED").
3
In cell D8, calculate average resolution hours using AVERAGE.
4
In cell E8, count total tickets marked as "BREACHED".
Solution & Formula Breakdown
Let's review the timestamp math and SLA compliance checks:
1. Elapsed Resolution Hours Calculation (D2:D6)
=(C2-B2)*24
Spreadsheets store dates as days. Subtracting gives fractional days, so multiplying by 24 gives exact hours:
- TCK-801 (Billing): 0.416667 days × 24 = 10.0 hours.
- TCK-802 (Bug Report): 1.250000 days × 24 = 30.0 hours.
- TCK-803 (Feature Req): 0.750000 days × 24 = 18.0 hours.
- TCK-804 (API Outage): 1.208333 days × 24 = 29.0 hours.
- TCK-805 (Account Lock): 0.500000 days × 24 = 12.0 hours.
2. SLA Threshold Classification with IF (E2:E6)
=IF(D2<=24, "PASSED SLA", "BREACHED")
- TCK-801 (10.0 hrs): <= 24 → PASSED SLA.
- TCK-802 (30.0 hrs): > 24 → BREACHED.
- TCK-803 (18.0 hrs): <= 24 → PASSED SLA.
- TCK-804 (29.0 hrs): > 24 → BREACHED.
- TCK-805 (12.0 hrs): <= 24 → PASSED SLA.
3. Summary Queue Metrics (D8 & E8)
- Cell D8 (Average Time):
=AVERAGE(D2:D6) → 19.8 hours.
- Cell E8 (Breached Count):
=COUNTIF(E2:E6, "BREACHED") → 2 tickets.
If you ever need resolution time in minutes instead of hours, multiply the date difference by 1,440 (24 hours × 60 minutes = 1440)!