Outside SLA time notifications/calculations

Deanna Copello

Hi, I'm trying to establish various kpi interval tracking and notification if out of sla. I'm having a VERY hard time coming up with how this should work.

Example:

Received date is 12/1, sla time frame is 12/1 + 2 days = 12/3, however, if received doesn't change to next step, order submitted, i need a notification sent.

Another example:

Site requires construction, notification date is 12/1, construction sla time is 120 business days, i need that date calculated, then if construction doesn't complete by that date, i need another notification.

basically setting up sla intervals based on my milestone date changes (which currently has an automation to insert the date).

I hope that makes sense.

Thank you!

Mike TV

@Deanna Croach

For example #1, if your received date is in a column called Received Date your formula would be (in a date type column):

=[Received Date]@row+2

You put that formula into a new column and it will add 2 days to the received date which you can use to trigger an Automation if it's needed. In example #1, I'm not sure what you mean by "if received doesn't change to next step". However that next step is indicated on your sheet, you'd set up an automation that if date is reached (using your formula above to calculate the Received Date + 2 days and have it set up an alert if the "received doesn't change to next step" by the time that date is reached.

For example #2, again using a Received Date named column your formula would be (in a date type column):

=[Received Date]@row+120

Then however your indicate on your sheet that construction is completed, you'll setup an Automation for the date being reached from the formula above that it sends an alert notification.

Deanna Copello

invalid column value:

Mike TV

@Deanna Copello

Did you make sure to set the new column type to Date as I indicated?

Deanna Copello

ah, nope. that is what i was missing!

next tricky part, can we calculate the number of days that are past the sla?

Mike TV

@Deanna Copello

That's easy. Something simple like this:

=NETDAYS([Received Out of SLA]@row, TODAY())

Deanna Copello

ok, got that, but didn't think of this.

received date = 6/1/22

order submitted sla = 6/3/22

actual ordered date = 6/4/22

days outside of sla should show 1 OR if blank, calculate to today.

would i modify to something like this:

=NETDAYS([Received Out of SLA]@row, if[order submitted]=blank, then TODAY())

Mike TV

@Deanna Copello

I'm not sure if I fully understand what your saying but you could use a formula like this for what it sounds like you may be trying to say:

=IF(ISBLANK([Order Submitted]@row), TODAY(), NETDAYS([Received Out of SLA]@row, TODAY()))

Deanna Copello

sorry, maybe this is more clear:

if order submitted = 11/21, order submitted sla date is 11/23, then order submitted outside sla would be blank:

but if order submitted is 11/24, order submitted sla date is 11/23, then outside sla would = 1

Mike TV

@Deanna Copello

Sorry, no I don't follow.

Deanna Copello

trying to calculate the number of days that we are outside of our order submitted sla date.

if the sla date is 11/23, but order submitted is showing either blank or beyond 11/23, that is the number of days i'm trying to calculate. (outside sla)

but if the order submitted date is equal or less than 11/23, then outside sla would be blank.

Mike TV

@Deanna Copello

I guess to be able to help I need to understand the relationship between [Received Date] and [Order Date]. I thought we were dealing with just [Received Date] and adding 2 or 120 to that. So now it sounds like we're dealing with the following columns:

[Received Date]

[Received Outside of SLA]

[Order Submitted]

[Order Submitted SLA Date]

[Order Submitted Outside SLA]

...and I'm just confused as to how all of these tie together now. Also, I could understand an example of 11/29 Received Date. We add 2 days to 11/29 makes it 12/01 which is today, so the outside SLA should be blank if the deadline date is today being not yet outside the SLA...but you're using dates in the past and I'm sooooo confused.

Deanna Copello

Sooooo sorry. i guess i didn't share my workflow. here is the diagram with sla timeframes in-between job steps. i need to report on the steps that are out of sla: