# Calculating SLA based on time/hour formula

Options
✭✭✭✭✭

Hi @Paul Newcome I thought I would write a new thread and @ your name on this post. Hope thats ok. Taking this from Formulas for Calculating Time thread.

---------------------------------------------------------------------------------------------------

@Paul Newcome

What a thread! Came across this when I was looking for a time formula! This is truly a godsend!

Thanks so much, Paul.

I do have a question though, I am actually building a ticketing system for our IT team on Smartsheet. Our process is a ticket receives via a form and will trigger an alert etc. The issue is, we do have an SLA column that will be triaged by the IT manager based on the issue. Priority 1 will be 2H response time etc. This means, when a ticket is received at 10 am on Priority 1, we will have to respond by 12noon.

My initial thought on this will be

Ticket Start time: Auto Created Date (when a form is received)

Priority: Manual Entry (P1, P2, P3, etc)

SLA: Auto Cell Change Value based on Priority entered for example P1 = 2Hours, P2(8H), P3(24H)

Suggested Response Time: formula based on SLA from Ticket Start time.

Completed Date&Time: Manual Entry (when an IT person resolves the issue)

Achieved SLA: formula duration based on Suggested Response time to End time.

Notification will be based on time instead of date - for example, if P1 ticket receives at 10am, a notification will be sent out to the technical team by 11:30 if the status is still Open.

Of course, all SLAs have to be within office hours 8 am to 5pm. That means if I have a ticket that comes at 4:30 pm on a Monday for P1, it has to be responded to by Tuesday (the next day) at 9:30 am.....

I have seen some of your published works, and I think the Need to create a "shift" column, is probably the closest. However, I am wondering if you have other examples that may be almost to the challenge I am facing above. Even if you can point me to another thread that has a similar issue, I would really appreciate it.

Syed

Tags:

• ✭✭✭✭✭
Options

Managed to figure out the solution using one of Paul's publish sheet.

Thanks

Syed

• ✭✭✭✭✭
edited 08/02/21
Options

Continue from the above

Using @Paul Newcome formula in his various published sheets, I am stuck at the below.

I want to calculate the start time within business hours (8am-5pm) using the columns

Start Date: manual entry

StartTime - Hour : manual entry (drop down) from 1 to 12

StartTime - Minute : manual entry (drop down) from 00 to 59 (with colon at the front)

StartTime - AM/PM : Manual Entry (drop down) AM or PM

StartDateCalc : Managed to calculate formula based on Workday and Public Holidays list

=WORKDAY([Start Date]@row, IF(OR(WEEKDAY([Start Date]@row) = 7, WEEKDAY([Start Date]@row) = 1), 1) + IF([StartTime-Hour]@row + (VALUE(RIGHT([StartTime-Minute]@row, 2)) / 60) >= 17, 1, 0), {Holidays List Date})

I want to calculate Start Time based on 8am to 5pm, when anything created before 8am will start at calculating at 8 and anything after 5pm will start at 8.

I put together a formula below but receives incorrect argument :(

=IF(OR(WEEKDAY([Start Date]@row) = 1, WEEKDAY([Start Date]@row) = 7, 8, IF(OR(VALUE(LEFT(StartCombine@row, 2)) + (VALUE(RIGHT(StartCombine@row, 2)) / 60) < 8, VALUE(LEFT(StartCombine@row, 2)) + (VALUE(RIGHT(StartCombine@row, 2)) / 60) >= 5, CONTAINS("PM", [StartTime - AM/PM]@row), 8, VALUE(LEFT(StartCombine@row, 2)) + (VALUE(RIGHT(StartCombine@row, 2)) / 60)))))

*I put a column StartCombine to combine Hour and Minute together I thought it would be easier to calculate but no :(

• ✭✭✭✭✭
Options
• ✭✭✭✭✭
Options