Formula Issue - Invalid Operation

LDLValentine

=IF([Time Due]@row > [Day End]@row, [Day Start]@row + ([Time Due]@row - [Day End]@row), 0)

Getting an "Invalid Operation" with the above and cannot seem to see it. I really think I have been looking at this Ticketing solution for too long.

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.

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

Meg Y

@LDLValentine

Try this

=IF(AND([Time Due]@row > [Day End]@row, ([Day Start]@row + ([Time Due]@row - [Day End]@row), 0))

Not 100% sure as formulas are a weak area for me.

LDLValentine

Thanks Meg, wish it was that simple. The "And" is where I started, but it isn't an And statement really. What the statement is looking for is whether the [Time Due] is past the End of Day (in this case 6pm). I probably should have created greater clarity in my original post.

If [Time Due] = 8:45PM then the calculation should take the extra 2:45 and add it to 7:00AM ([Day Start]) giving me the answer of 9:45AM

I still have to figure out how to make the day roll over. This is all part of an SLA tracking strategy. I have another question that has been hanging out there for a couple of days with no answer, so I am simply trying to go at it a different way with helper columns.

JamesB

@LDLValentine

This may give you a start. Since Smartsheet does not really have a time function, I had to breakdown the time format and convert it to a value in order to display the difference between the end of day and Time Due numbers. If you wanted to append this to the start time on the next day you would have to use the same strategy to break the time into separate hours and minutes values for both the start time and the additional time from the previous day and then add your difference in hours to the hours and difference in minutes to your minutes and then rebuild it with the colon to display as a time output.

LDLValentine

Thanks James, this gets me to the calculation, but as soon as I add the necessary "IF" statement it falls apart again. I am usually not this dense, truly.

JamesB

@LDLValentine What does your IF statement look like?

LDLValentine

The original if statement was in the initial question. I thought I would go a different direction and convert the time into number (decimal) for calculation purposes, still beating my head against a brick wall honestly. Tried this with the converted numbers.

=IF([Time Overflow]@row <= 0, [Time Extract]@row, SUM([Time Overflow]@row, [Day Start]@row))

Time Overflow = 5:03 (this is the number of hours past the End of Day e.g. End of Day is 6:00PM and the based on the SLA the total hours would put the end time at 11:03PM)

Day Start = 7 (normally this would be 7:00 AM, but for calculation I used a whole number as this is a Helper Field)

Time Extract = this is the helper field that converts Time Overflow from Time to a Decimal Number

Right now, I am still not able to add (sum) the two decimal fields together for a total. Just as I was not able to add the two Time fields together for a total. What I need to be able to see, is the hour of day the SLA is due. I can convert back to time once I know the actual "Time"

JamesB

@LDLValentine

To do this with the IF statement, you still will need to breakdown the times and rebuild them. I have done this in a sheet and I believe it is what you are looking for. On Row1 is the result of a True Statement. Row2 is the result of a False statement. Row 3 is so I could capture the formula. Note all my entries are in a time format i.e. 7:00

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

LDLValentine

Hi James, thank you for this. I am sure I am missing something, but this is so close to what I am looking for. Not certain why my result doesn't convert the way yours does. I suspect it is because it is converting based on a 24 hour day vs. a 12 hour day. But I am also getting seconds when the if statement is True where you were not.

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

I just can't find the difference between the two. I plan on converting the calculated SLA field back to Time so I can actually affix it to Date and Time for SLA tracking. Your help has me so close and I appreciate you.

JamesB

@LDLValentine Are you using a Formula in the Time Overflow column and/or the Time Extract column? If so, can you share those.

LDLValentine

Yes, those are both calculated fields. Plus Time Due 1 is also a calculated field. All of these are intended to be Helper Fields.

Time Extract: =LEFT([Time Due 1]@row, 5)

Time Overflow: =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))

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

JamesB

@LDLValentine Ok, I am still trying to figure out why you are getting a result of 4:47:47. I do note that the only place this is happening is where Time Overflow is a negative. Can you share the format of your Start Column that is referenced in your Time Due 1 formula?

JamesB

@LDLValentine Also, the 12 hour vs 24 hour format is not an issue. However, the formula is working in strict numerical data and not time formats. For example.

Day End is 6pm. You have a job that goes more than 7 hours past 6pm giving it a 1:00am stop time. If we extract using the formula for overflow, it will do 1-6 for the hours. There is additional logic probably in a helper column to account for the time past 12am.

Something that says if the hours in the time due is less than 6, then first (12+hours in time due) then minus the 6 for the day end. Using the Scenario above, this should give you a result of 7 that would then become the hours to append to the start time of 7am.

LDLValentine

The format for day end is 6:00

JamesB

@LDLValentine We can work on the overflow once we get the base working. I need to understand how the data in the "Start" column is derived and displayed. This will help me understand the format being created in the "Time Due 1" column.

LDLValentine

Both the Day Start and the Day End are a manual entered field. There is no calculation involved, it is simply there to enable the rest, initially I tried picking up the information from the Summary but that didn't work. Then I tried pointing to just Row 1 for each but continued to get an error. So now what I have a Ctrl C / Ctrl V to fill each column.