Time and Date Calculation

LDLValentine
LDLValentine ✭✭✭✭✭
edited 09/05/24 in Formulas and Functions

Back to SLA calculations, this calculation worked in the previous wave of Hypercare, but for some reason isn't doing so now. Background: We calculate SLA based on Priority Critical=4, High=8, Medium=24, Low=48. Actual SLA tracks how long a Ticket remains open. I know I am missing something.

=IF([Tracking Status]@row = "Open", NETWORKDAYS([Created Date]@row, TODAY()), IF([Tracking Status]@row = "Closed", VALUE(MID(Start@row, 1, FIND(":", Start@row, 1) - 1)) - VALUE(MID(Finished@row, 1, FIND(":", Finished@row, 1) - 1)) + ":" + VALUE(MID(Start@row, FIND(":", Start@row, 1) + 1, 2))))

The reason this is so important is the RAG feeds off the SLA Actual result during Hypercare.

Tags:

Answers

  • why would you not do something like

    IF([Tracking Status]@row = "Open", NETWORKDAYS([Created Date]@row, TODAY()),
    IF([Tracking Status]@row = "Closed", HOUR(Finished@row) - HOUR(Start@row) + ":" + (MINUTE(Finished@row) - MINUTE(Start@row))))


    if it is a time then you should be able to use minute and hour and not have to use the mid function.

    I think you are trying to do to many Time → Text → Time

    Also your column rows need to be correct as date time and text are not the same



  • LDLValentine
    LDLValentine ✭✭✭✭✭

    I tested your suggestion your suggested alternative and got an unparsable error, I found my original error was in the networkday calculation which was missing the multiplier of 24.

    Thank you for looking and offering up an easier strategy.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!