What formula can I create to get the correct SLA?

jr7922

I know I can create a column to only include the date from the ticket was submitted and another for calculating the business days. However, I'd like to auto-populate the deadlines based on the triage options.

Based on the selection of Triage options auto-populate a deadline from the time the ticket was submitted:

• Tier 1 – Resolution at 1 Business Day
• Tier 2 – Update at 3 Business Days; Resolution at 5 Business Days
• Tier 3 – Update at 3 Business Days
  SLA.png

Protonsponge

Hello @jr7922 ,

Hopefully the above is helpful to you.

I have detailed below the formulas to make this add work days to [Date & Time Ticket was Submitted]

Formula for [Deadline to Provide an Update] -

=IF([Triage Options]@row = "Tier 1", "N/A", IF([Triage Options]@row = "Tier 2", WORKDAY([Date & Time Ticket was Submitted]@row, 3), IF([Triage Options]@row = "Tier 3", WORKDAY([Date & Time Ticket was Submitted]@row, 3))))

Formula for [Resolution Deadline] (based upon 10 days for Tier 3 resolution deadline (change 10) as you prefer, see previous comment above) -

=IF([Triage Options]@row = "Tier 1", WORKDAY([Date & Time Ticket was Submitted]@row, 1), IF([Triage Options]@row = "Tier 2", WORKDAY([Date & Time Ticket was Submitted]@row, 5), IF([Triage Options]@row = "Tier 3", WORKDAY([Date & Time Ticket was Submitted]@row, 10))))

Also, just as an FYI, make sure your column types are set to Date. You may know this already but just to be sure 😀

Protonsponge

Hello @jr7922,

Would the following be an option for you?

In the [Deadline to Provide an Update] you could have a set of IF statements to add days to [Date & Time Ticket was Submitted] based upon [Triage Options].

1. IF Tier 1 = N/A (as you said no update timeline)
2. IF Tier 2 = add 3 days to [Date & Time Ticket was Submitted]
3. IF Tier 3 = add 3 days to [Date & Time Ticket was Submitted]

Formula for [Deadline to Provide an Update] -

=IF([Triage Options]@row = "Tier 1", "N/A", IF([Triage Options]@row = "Tier 2", [Date & Time Ticket was Submitted]@row + 3, IF([Triage Options]@row = "tier 3", [Date & Time Ticket was Submitted]@row + 3, "")))

In the [Resolution Deadline] you should do something similar using IF statements to add days to [Date & Time Ticket was Submitted] based upon [Triage Options].

1. IF Tier 1 = add 1 date to [Date & Time Ticket was Submitted,
2. IF Tier 2 = add 5 days to add [Date & Time Ticket was Submitted]
3. IF Tier 3 I have it as "TBC" as you didn't have a resolution date for this category. (I think it might work better for you long terms to have a date criteria for your tier 3 resolution date rather than TBC, example formula at bottom)

Formula for [Resolution Deadline] -

=IF([Triage Options]@row = "Tier 1", [Date & Time Ticket was Submitted]@row + 1, IF([Triage Options]@row = "Tier 2", [Date & Time Ticket was Submitted]@row + 5, IF([Triage Options]@row = "tier 3", "TBC")))

In case its helpful - here is a formula with +10 days for your tier 3 resolution deadline, just edit the 10 in bold to the number of days you prefer.

=IF([Triage Options]@row = "Tier 1", [Date & Time Ticket was Submitted]@row + 1, IF([Triage Options]@row = "Tier 2", [Date & Time Ticket was Submitted]@row + 5, IF([Triage Options]@row = "tier 3", [Date & Time Ticket was Submitted]@row + 10)))

jr7922

Thank you! I have one more question. Would I simply use a workday formula to make sure that the date is three business days after the submission date?

Protonsponge

Hello @jr7922 ,

Hopefully the above is helpful to you.

I have detailed below the formulas to make this add work days to [Date & Time Ticket was Submitted]

Formula for [Deadline to Provide an Update] -

=IF([Triage Options]@row = "Tier 1", "N/A", IF([Triage Options]@row = "Tier 2", WORKDAY([Date & Time Ticket was Submitted]@row, 3), IF([Triage Options]@row = "Tier 3", WORKDAY([Date & Time Ticket was Submitted]@row, 3))))

Formula for [Resolution Deadline] (based upon 10 days for Tier 3 resolution deadline (change 10) as you prefer, see previous comment above) -

=IF([Triage Options]@row = "Tier 1", WORKDAY([Date & Time Ticket was Submitted]@row, 1), IF([Triage Options]@row = "Tier 2", WORKDAY([Date & Time Ticket was Submitted]@row, 5), IF([Triage Options]@row = "Tier 3", WORKDAY([Date & Time Ticket was Submitted]@row, 10))))

Also, just as an FYI, make sure your column types are set to Date. You may know this already but just to be sure 😀

jr7922

THANK YOU!! This formula works great. I really appreciate the help.

Protonsponge

Great, really pleased you got something that works and grateful for the opportunity to help you out 😁