Calculating SLA based on time/hour formula

Syed Muhafzal

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.

Thanks in advance

Syed

Syed Muhafzal

Closing this thread off.

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

Thanks

Syed

Syed Muhafzal

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 :(

Syed Muhafzal

Syed Muhafzal

Closing this thread off.

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

Thanks

Syed