Formula Issue - Invalid Operation

JamesB

@LDLValentine Ok, I hope this is what you are needing.

In your TIME DUE 1 column, we need a qualifier to formulate against. In my test sheet, I put the time as 1:47 AM. Using the AM in my logic, I am able to create an overflow that can calculate the time over flow from 6pm to cover when the time overflow goes past 12am. See the formula below.

=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)))

a 1:47 AM in your TIME DUE 1 column will give you an Overflow of 7:47.

For your Day Start and Day End Column, you can use ="7:00" and ="6:00" and then make those column formulas so any time you add a row, the start and end columns will auto-populate.

LDLValentine

You are my hero! That worked perfectly, but it is still translating in the conversion to the SLA time to the 00:00:00 where the original time in the Overflow was a negative. I have taken apart your original formula, but it works perfectly for everything where the Day Start and Time Overflow are added. I truly am nearly tempted to simply manually adjust the others. this was your original formula:

=IF([Time Overflow]@row <= 0, [Time Extract]@row, 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)))

With this formula and the other, everything is working with that small exception. The SLA structure will function properly and the calendar will roll over making the entire HelpDesk almost fully automated.

JamesB

@LDLValentine I am glad to hear this is working. Curiosity question. If the Time Overflow is a negative value, would it be safe to assume that a formula to look for a negative amount sets the overflow to 0 for that entry? If so, It would not be hard to add this to formulation to make this happen.

LDLValentine

Hi James, yes that is actually where I started and what made the most sense to me. I had jumped through so many hoops trying to get this to work, with your assistance mostly it does now. Just to put it in perspective for you and why it such a beast. This is actually a SLA strategy for helpdesk ticket tracking.

Critical = 4 hour turn around: High = 8 hour turn around: Medium = 24 hour turn around: Low = 48 hour turn around.

From the time a ticket is received there is a 30 minute grace period (e.g. received 8:00 AM clock starts 8:30 AM)

Working time 7:00 AM - 6:00 PM. thus the overflow requirement to allow a High or Medium to complete the following day.

Does that make sense?

Your assistance has been immense and has taught me so much. I appreciate you so very much. If converting the negative to a zero makes sense, I think that would be fabulous, especially if it would solve the final issue.

JamesB

@LDLValentine

This should fix the - to 0. Always backup your original formulas in case these do not do what you want.

Time Overflow Column formula:

=IF(CONTAINS("-", 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))), "0:00", =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)))

Overflow Column Formula

=IF(VALUE(MID([Time Overflow]@row, 1, FIND(":", [Time Overflow]@row, 1) - 1)) = 0, 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))))

LDLValentine

Hi James. Converting to zero put us back a step, trying this morning to take apart the steps but the outcome was the SLA found the zero and returned the Day Start as the result.

Still have the issue of 24 hour day, but I think I can resolve this one.

Again, I so very much appreciate all of your assistance. This has been a real learning experience for me and has taught me so much.

LDLValentine

Good Morning James, with your brave and invaluable assistance nearly all the pieces have come together. Over the weekend I tweaked and played, and finally have a 99% working model that makes sense. There is only piece that is still giving grief. I know what it is I just don't know why it is. If you see in the Time Due column the Invalid Value, all of those are "Medium" priority with a 24-hour SLA requirement. I can override manually the 15:3 to 15:30 and that fixes the #Invalid Value error, I am wondering though why this is only happening sometimes and only with this particular set of data. Any ideas? In researching this weekend I couldn't find anything, and honestly, I will happily fix this error manually for the time being.

JamesB

@LDLValentine Just for ease, can you share your formula from the Calculated SLA column?

LDLValentine

Sure, it is the one that you provided. Ultimately it gave the best and most accurate result.

=IF([Time Overflow]@row <= 0, [Time Extract]@row, 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)))

Then all I did for the Time Due is this:

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

Now I am searching through all we have talked about and hoping to extract something simple to calculate the actual time between start and finish of the ticket and I have a fully automated helpdesk.

I cannot begin to tell you how much I appreciate what you have done for this joyful little project.

JamesB

@LDLValentine

This should fix the Invalid's

=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))))

LDLValentine

you are my hero for the month James. Thank you.