How can I track when a ticket is past its number of SLA days??

Hello, thank you in advance with helping with this question! Please see below:

Situation: My team uses a Smartsheet intake form to take work requests from other teams. This populates into a sheet with the needed information to move tickets to different status' (ex: New Request, Active, Backlog, Triage Required).

All of these status' are tied to SLAs of how long they are able to remain under that status. (ex: Backlog = 5 business days, New Request = 24 hours).

Issue: I am tracking these SLAs by manually counting from when it changed to the specific status to determine the overdue SLAs. I want this process to be more automated.

Question: How can I track/get notified when a status has gone over its SLA days since they all have different total number of days for each SLA?

Find more posts tagged with

Sheets

Formulas

Support

Automations

Project Management

Accepted answers

KPH

Hi @NBKD

You could create a column to record the date the Status changed and create a workflow to automatically populate that column.

Then you can create another column to calculate when the alert should be sent, based on the status and the SLA associated with that status. For example, IF Status is backlog then SLA is 5 working days after the date the status was last changed. That formula would be

=IF(Status@row = "Backlog", WORKDAY([Current Status Start Date]@row, 5))

You'll need to extend the formula for the other statuses.

Then you can then use some conditional formatting to highlight rows where the SLA date is in the past

And/or create another workflow to be triggered the day after that SLA date.

All comments

KPH

Hi @NBKD

You could create a column to record the date the Status changed and create a workflow to automatically populate that column.

=IF(Status@row = "Backlog", WORKDAY([Current Status Start Date]@row, 5))

You'll need to extend the formula for the other statuses.

Then you can then use some conditional formatting to highlight rows where the SLA date is in the past

And/or create another workflow to be triggered the day after that SLA date.

NBKD

@KPH thank you! This should work. how would that extended formula look if these 3 are incorporated

New Request = 24 hours

New - Triage Required = 48 hours

Backlog = 5 days

KPH

You can add the other SLAs to the IF statement by adding additional, nested IFs, like this:

=IF(Status@row = "Backlog", WORKDAY([Current Status Start Date]@row, 5), IF(Status@row = "New Request", WORKDAY([Current Status Start Date]@row, 1)))

The addition is in bold. This means that if the Status is not Backlog another IF is used to determine if the status is New Request. If it is, it returns a result 1 working day after Current Status Start Date.

You can learn about nesting IF statements here: https://help.smartsheet.com/function/if

For your third status you would add in this part in bold (to add 2 working days)

=IF(Status@row = "Backlog", WORKDAY([Current Status Start Date]@row, 5), IF(Status@row = "New Request", WORKDAY([Current Status Start Date]@row, 1), IF(Status@row = "New - Triage Required", WORKDAY([Current Status Start Date]@row, 2))))

And so on for more statuses. Be careful to include the comma before each new IF and to insert the parenthesis at the end.

NBKD

@KPH my formula keeps coming back as unparseable, might not have the parenthesis is the correct spots.

=IF(Status@row = "Backlog", WORKDAY([Helper Status Change]@row, 5), IF(Status@row = "New Request", WORKDAY([Helper Status Change]@row, 1), IF(Status@row = "New - Triage Required", WORKDAY([Helper Status Change]@row, 2))))

KPH

It looks good to me.

Can you check the following:

Column names are Helper Status Change and Status (and that there are no spaces at the start or end of the name).
The Helper column is a date format.
The quotation marks are straight not curved.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.