Strange Formula Behavior

maraore

Greetings,

I am developing a solution that automatically captures a 24-hour time (as an integer value) from the generated system date/time. The goal is to allow users to use a form on a tablet to enter a few fields of information into three sheets. My solution converts the times in each sheet correctly:

Above is a portion of my test data. The first column highlighted in yellow is a conversion of the automatic system date/time (in gray). The second and third yellow columns are references from other sheets that use the same formula to calculate the 24-hour conversion.

My issue comes from trying to update the sheet with a form. When a new line is submitted, the formula behaves strangely.

Above is the same data as before, but with a new record submitted. Now, the formula calculation will work for some, but add a comma, or return an error for others.

What's even stranger is that when I open up the sheet and save, the data goes back to the intended values.

I will add that the formula for my conversion is complex (about 518 characters) with multiple chained functions. Any thoughts on why I need to do this "manual refresh" for the formulas to work?

Thank you

Mike TV

What happens when you create another sheet with a cell reference to the #INVALID cells before you fix it with the save method? Is it really a broken formula or just a display issue?

James Keuning

I have one like this too. A pretty complicated budget sheet that shows errors while editing, and after saving, but which resolves when I refresh. It makes me nervous every time I encounter it.

So, no answer for you, but more just saying that it's not you, it's Smartsheet.

That said, there might be some refinement that can clean it up, or maybe I can chase down which part of the super complex formula is causing the issue, but I just haven't had the motivation.

My system is made up of a bunch of helper columns that I have not collapsed into one cell, and I still can't figure it out.

Mine often say #BLOCKED

I think it's connected to another problem that I have seen - really complex IF statements which have one really simple T/F test in them, like, if something is in this cell, then MESSAGE TO CLEAR THE CELL. And there is no other condition that creates that message. I clear the cell, message is still there. Save. Message still there. Refresh. Message goes away.

maraore

https://community.smartsheet.com/discussion/comment/327816#Comment_327816

Matt,

The main table in my first screenshot does make a cell reference and pulls exactly the value in its referenced sheet:

https://community.smartsheet.com/discussion/comment/327818#Comment_327818

James,

To your point, my formula is also complex nested if statements building off of a helper column (the system date/time with the date stripped out). Below I've separated it into a more readable format:

= SUBSTITUTE(
(IF(FIND("AM", TIMESTRIP@row) > 0, 
   IF(FIND("12", TIMESTRIP@row) <> 0, 
      SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TIMESTRIP@row, "AM", ""), "PM", ""), "12", "00"),
      SUBSTITUTE(SUBSTITUTE(TIMESTRIP@row, "AM", ""), "PM", "")), 
   IF(FIND("12", TIMESTRIP@row) <> 0, 
      SUBSTITUTE(SUBSTITUTE(TIMESTRIP@row, "AM", ""), "PM", ""), 
      SUBSTITUTE(
                 SUBSTITUTE(SUBSTITUTE(TIMESTRIP@row, "AM", ""),"PM", ""),
                 LEFT(TIMESTRIP@row, FIND(":", TIMESTRIP@row) - 1),
                 VALUE(LEFT(TIMESTRIP@row, FIND(":", TIMESTRIP@row) - 1)) + 12, 1)
                 )
      )
),
":", "")

Other, simpler formulas transfer and adjust easily with new entries - it's what I rely on in other projects to trigger events as users enter data. This is the first formula I've made of this complexity and length.

Not sure how I can optimize this logic - I thought of leaving out the "AM/PM" characters to make the formula smaller, but I need them for the 24-hour logic (unless I use a conditional helper column?).

maraore

My apologies, I just noticed that my screenshot in the first and previous post present incorrect results.

This is the actual end result with correct values. No changes to the time formula or its referenced sheets, only refreshing and saving to recalculate the values on all sheets.

Sameer Karkhanis

Not sure what your formula in other sheet is but just a general observation and possible hint from your screenshot:

If you notice all the errors are for those rows where the time in your date/time value is a single digit hour. For example, 04/28/22 2:28 PM instead of 04/28/22 02:28 PM. Check if your conversion formula errors if the hour portion is a single digit.