Time and Date Calculation

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.

Find more posts tagged with

Formulas

Accepted answers

All comments

Brent C. Wilson

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.