How to calculate SLA compliance in Excel

Hours per ticket and the target from a priority table

Date-times are days in Excel, so resolved minus opened is a fraction of a day. Multiply by 24 for hours: T-2201 took 6.58 hours. Column E wraps it in ROUND(...,2) so the column reads cleanly, and IF(D2="","",...) leaves open tickets blank instead of showing a big negative number.

Don't type the target on every row. Keep a small table of priority and target hours (I1:J4) and look it up with XLOOKUP. When the P2 target changes from 8 hours to 6, you change one cell and every ticket is rechecked. In Excel 2019 or earlier, use =VLOOKUP(B2,$I$2:$J$4,2,FALSE).

Met, Missed, or breaching while still open

Column G gives each ticket a status. Resolved tickets are Met when the hours are within the target and Missed when they aren't. Open tickets are compared with an "as of" time in M2: if they've already been open longer than their target, they're Breaching, otherwise Open.

Use a typed "as of" date-time rather than NOW(). NOW() changes every time the sheet recalculates, so the report you send at 9:00 doesn't match the one your manager opens at 11:00. A fixed time also makes last week's report reproducible.

Use <= so a ticket that lands exactly on its target counts as Met. Check how your contract words it. "Within 4 hours" usually includes 4:00, but some teams treat it as "under".

The compliance rate

Count the Met tickets and divide by the tickets that were resolved. COUNT(D2:D11) counts the cells in the Resolved column that have a date-time, so open tickets aren't in the bottom of the fraction.

That's the usual definition, but it means a ticket that sits open forever never counts against you. Show the open breaches next to the rate, =COUNTIF(G2:G11,"Breaching") gives 2 here, or report a stricter rate that adds them to the bottom: 5 of 10 = 50%. Say which one you report.

To compare with a goal, put your target rate in M4 and test it: =IF(COUNTIF(G2:G11,"Met")/COUNT(D2:D11)>=M4,"On target","Below goal") shows Below goal against 90%.

Compliance by priority and by month with COUNTIFS

By priority: count Met tickets for that priority, divided by resolved tickets for that priority. "<>" in COUNTIFS means "not blank", so open tickets are left out. P1 and P2 are both at 66.7% and P3 at 50%.

By month, decide whether a ticket belongs to the month it was opened or the month it was resolved, and stick with it. Resolved-in-month is common for reporting. Put the month start in M3 and use EDATE for the end: =COUNTIFS(G2:G11,"Met",D2:D11,">="&M3,D2:D11,"<"&EDATE(M3,1))/COUNTIFS(D2:D11,">="&M3,D2:D11,"<"&EDATE(M3,1)) gives 4 of 7 for October, 57.1%.

With this few tickets, one miss moves a priority by 33 points. Show the count next to each rate so a bad week on three P1s isn't read as a trend.

Business-hours SLAs

Many teams run P1 on the clock, 24/7, and P2 and P3 on business hours only. Clock hours make T-2207 look late: opened Wednesday at 13:10, resolved Thursday at 10:00, 20.83 hours against 8. In 9:00-17:00 business hours it took 4.83 hours (3h50 on Wednesday, 1 hour on Thursday) and met its target.

The formula below counts working hours with NETWORKDAYS for the whole days and MEDIAN to clamp the first and last day into 9:00-17:00. It assumes the ticket opens and closes on a working day, so a ticket resolved on a Sunday gives a strange result: check those by hand. The guide on hours between two date-times explains each part and how to add holidays.