# Time and Date Calculation

✭✭✭✭
edited 09/05/24

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:

• 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

• ✭✭✭✭

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!