Invalid Value in Date / Time Calculation

LDLValentine

First some background, this is not a consistent outcome, and there is more than a single calculation involved. The Hypercare Ticketing system calculates SLA times and was a bear to build as it is based on Ticket Priority and time. We don't run 24 hours, so had to calculate over 'next day'. That is just the background. What is happening as best as I can tell, when a ticket has a calculated SLA with a time of 8:01 (example) it is being converted to 8:1, thus dropping the zero. There are several fields used for the conversions:

Due Date 1: =IF(Priority@row = "Critical", [Created On]@row + (4 / 24), IF(Priority@row = "High", [Created On]@row + (8 / 24), IF(Priority@row = "Medium", [Created On]@row + 1, IF(Priority@row = "Low", [Created On]@row + 2, "N/A"))))

Time Due 1: =IF(Priority@row = "Critical", TIME(Start@row) + 240, IF(Priority@row = "High", TIME(Start@row) + 480, IF(Priority@row = "Medium", TIME(Start@row) + 1440, IF(Priority@row = "Low", TIME(Start@row) + 2880, "N/A"))))

Overflow: =IF(AND(CONTAINS("AM", [Time Due 1]@row), VALUE(MID([Time Due 1]@row, 1, FIND(":", [Time Due 1]@row, 1) - 1)) <> 12), VALUE(MID([Time Due 1]@row, 1, FIND(":", [Time Due 1]@row, 1) - 1)) + 12 - VALUE(MID([Day End]@row, 1, FIND(":", [Day End]@row, 1) - 1)) + ":" + VALUE(MID([Time Due 1]@row, FIND(":", [Time Due 1]@row, 1) + 1, 2)), VALUE(MID([Time Due 1]@row, 1, FIND(":", [Time Due 1]@row, 1) - 1)) - VALUE(MID([Day End]@row, 1, FIND(":", [Day End]@row, 1) - 1)) + ":" + VALUE(MID([Time Due 1]@row, FIND(":", [Time Due 1]@row, 1) + 1, 2)))

Calculated SLA: =IF(LEN(SUM(VALUE(MID([Time Overflow]@row, 1, FIND(":", [Time Overflow]@row, 1) - 1)), VALUE(MID([Day Start]@row, 1, FIND(":", [Day Start]@row, 1) - 1))) + ":" + (VALUE(MID([Time Overflow]@row, FIND(":", [Time Overflow]@row, 1) + 1, 2)))) = 3, SUM(VALUE(MID([Time Overflow]@row, 1, FIND(":", [Time Overflow]@row, 1) - 1)), VALUE(MID([Day Start]@row, 1, FIND(":", [Day Start]@row, 1) - 1))) + ":" + (VALUE(MID([Time Overflow]@row, FIND(":", [Time Overflow]@row, 1) + 1, 2))) + 0, SUM(VALUE(MID([Time Overflow]@row, 1, FIND(":", [Time Overflow]@row, 1) - 1)), VALUE(MID([Day Start]@row, 1, FIND(":", [Day Start]@row, 1) - 1))) + ":" + (VALUE(MID([Time Overflow]@row, FIND(":", [Time Overflow]@row, 1) + 1, 2))))

Time Due: =IF([Time Overflow]@row <= 0, [Time Due 1]@row, TIME([Calculated SLA]@row, 0, 2))

Date Due: =IF([Time Overflow]@row < 0, [Due Date 1]@row, [Due Date 1]@row + 1)

SLA Deadline: =[Due Date 1]@row + " " + [Time Due]@row

I know this a lot. But somewhere in here there is something I am missing. The system has been working until yesterday and it is still working except when a ticket has the leading zero in the minutes. "Time due", "Date Due", and "SLA Deadline" is where the error is occurring and I do not know why or how to fix.

Paul Newcome

Are you able to provide some screenshots as well?

LDLValentine

Good morning Paul. I will when the error happens again. The tickets with the error I have fixed individually as this is an active environment with primarily short SLA turn arounds. I have been unable to replicate the error in my test environment.

LDLValentine

As requested the screen shots of an error that happened earlier today

The only common theme I have been able to see in all the errors thus far is the decimal place change when there is a leading zero in the time extract.

Paul Newcome

Exactly what is the formula in [Calculated SLA] doing?