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.
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 → TimeAlso your column rows need to be correct as date time and text are not the same
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!