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.

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

@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

Tags:

Best Answer

Answers

  • Syed Muhafzal
    Syed Muhafzal Overachievers
    edited 08/02/21

    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 Overachievers
    Answer ✓

    Closing this thread off.

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


    Thanks

    Syed

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!