Calculating SLA based on time/hour formula
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.
---------------------------------------------------------------------------------------------------
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
Best Answer
-
Closing this thread off.
Managed to figure out the solution using one of Paul's publish sheet.
Thanks
Syed
Answers
-
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 :(
-
-
Closing this thread off.
Managed to figure out the solution using one of Paul's publish sheet.
Thanks
Syed
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!