How to Build an SLA Tracker in Excel With Working Hours

An SLA tracker in Excel shows, for every ticket or request, when it was due and whether you met the deadline. An SLA (service level agreement) is a promise such as "we resolve tickets within 2 business days" or "we reply within 4 working hours". This guide builds the tracker step by step: WORKDAY for due dates, IF for the status, a working-hours formula for hour-based SLAs, and colour rules that flag breaches.

What an SLA tracker needs#

An SLA tracker needs one row per ticket and a few columns that formulas can work from. Keep the inputs on the left and the calculated columns on the right:

Turn the range into an Excel Table (Ctrl+T, or ⌘T on a Mac) so new rows pick up the formulas automatically. If the SLA depends on priority, keep a small table of priorities and days and fill column C with XLOOKUP. The same layout works for a general task tracker, with deadlines in place of SLAs.

Here is the sample data. 2 November 2026 is a Monday.

Calculate the due date with WORKDAY#

WORKDAY adds a number of business days to a date and skips Saturdays and Sundays. In E2, type:

=WORKDAY(B2,C2)

Fill it down and format column E as a date. T-102 was opened on a Thursday with 2 days, so it is due on Monday 9 November: Friday is day 1, the weekend is skipped, Monday is day 2. T-103 (Friday plus 3) is due on Wednesday 11 November, and T-105 on Tuesday 17 November.

WORKDAY starts counting the day after the opened date. To skip public holidays, list them in a range such as H2:H10 and add it as the third argument: =WORKDAY(B2,C2,$H$2:$H$10). The WORKDAY function guide lists every argument, and the article on NETWORKDAYS and WORKDAY compares the two.

Show Met or Breached with IF#

IF compares the resolved date with the due date and returns a label. For closed tickets, the core formula is:

=IF(D2<=E2,"Met","Breached")

T-101 and T-103 were resolved on their due dates, so they show Met. T-102 (resolved Tuesday 10, due Monday 9) and T-104 (resolved Wednesday 11, due Tuesday 10) show Breached.

A real tracker also has open tickets, where D is blank. Nest a second IF so they don't show as Met by accident:

=IF(D2="",IF(TODAY()>E2,"Overdue","Open"),IF(D2<=E2,"Met","Breached"))

Read it from the left: if there is no resolved date, the ticket is Overdue once today is past the due date, otherwise Open. If there is a resolved date, it is Met or Breached. See the IF function guide for how nested IFs work.

To report how many business days a ticket took, use NETWORKDAYS and subtract 1, because NETWORKDAYS counts both the start and the end day:

=NETWORKDAYS(B2,D2)-1

T-102 gives 4 − 1 = 3 business days, one more than its SLA. The NETWORKDAYS function guide has more examples.

SLAs in working hours#

When the SLA is in hours, such as "respond within 4 working hours", you need the working hours between two date-times. Put the opened date-time in B2 and the response date-time in D2, and the office hours in J1 (9:00) and J2 (17:00). This formula returns the working hours between them:

=((NETWORKDAYS(B2,D2)-1)*($J$2-$J$1)
  +IF(NETWORKDAYS(D2,D2),MEDIAN(MOD(D2,1),$J$2,$J$1),$J$2)
  -MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),$J$2,$J$1))*24

MOD(D2,1) takes the time part of a date-time, MEDIAN clips it into office hours, and NETWORKDAYS counts the full working days in between. The guide on working hours between two dates explains each part.

For example, a ticket opened on Monday 2 November 2026 at 15:00 and answered on Tuesday 3 November at 11:00 took 2 hours on Monday and 2 on Tuesday: 4 working hours. If the elapsed hours are in G2 and the SLA hours in C2, the status is the same IF as before:

=IF(G2<=C2,"Met","Breached")

Overnight hours and weekends don't count against you, which is how most hour-based SLAs are written. If your team works a different week, such as Sunday to Thursday, use NETWORKDAYS.INTL and WORKDAY.INTL with a weekend code instead.

Highlight breaches with conditional formatting#

Conditional formatting colours rows so problems stand out without reading every cell. To colour every breached row red:

1. Select the data rows, for example A2:F100.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Type =$F2="Breached", pick a light red fill and click OK.

The $ before F locks the column, so every cell in the row checks column F. Add a second rule, =$F2="Overdue", in orange for open tickets past their deadline. For a warning before the deadline, =AND($D2="",$E2-TODAY()<=1) highlights open tickets due within one day. The guide to highlighting overdue dates has more rules like these.

Summarise SLA performance#

A tracker is most useful with a few summary numbers at the top. With statuses in F2:F100:

The met rate divides by closed tickets only, so open tickets don't drag it down before they are due. Add COUNTIFS by priority or by agent to see where breaches happen. These are the everyday numbers behind Excel skills for customer service roles.

Practise it#

The tracker rests on three skills. Practise NETWORKDAYS and WORKDAY in a live spreadsheet to get due dates and business days right, then the IF function lesson for Met and Breached labels, and the conditional formatting lesson for colour rules. Each one checks your formula instantly. Start with the exercise on this page: one ticket, one due date, one status.