Formulas are displaying #INVALID VALUE after form submission

Ryan Nighswander

Good morning! I have developed a system in Smartsheet that tracks production efficiency for 40 different workcenters. This system has been working as intended since Sept 2021, but beginning on the afternoon of Tuesday the 12th something broke on some of the sheets that drive the system. I have a formula to turn the "Created" timestamp into a number that can be used in formulas, and that formula isn't running until I open and save the sheet. I have several seemingly identical sheets, and some work and some don't. My current worries:

1. Was there a recent Smartsheet update that might break my formulas?
2. Are there sheet limits that, when approached, will stop some formulas from running?

I have some formulas in the sheet that are running properly. Here is the formula for my timestamp number:

=(IF(SUM(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row) - 9), FIND(":", RIGHT(Created@row, LEN(Created@row) - 9)) - 1))) = 12, 0, SUM(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row) - 9), FIND(":", RIGHT(Created@row, LEN(Created@row) - 9)) - 1)) * 60)) + SUM(VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2))) + IF(RIGHT(Created@row, 2) = "PM", 720, 0)) / 1440

Sameer Karkhanis

2 things to check:

1. Make sure the Created column type is correct in your sheets
2. The values used in the formula are not outside the range that you are comparing with

Ryan Nighswander

Thank you Sameer for your advice! I believe I found my actual issue. When I submit the form, the Created column has a comma between the date code and the time code (IE 04/15/22, 10:21 AM). After opening and saving the sheet, the comma is removed (IE 04/15/22 10:21 AM). The formula is hard coded to expect the lack of a comma, so it grabs the wrong thing and it's not a number and everything is messed up. I have a new formula that works so far,

=((IF(((VALUE(IF(LEFT(RIGHT(Created@row, 8), 1) = " ", MID(RIGHT(Created@row, 8), 2, 1), LEFT(RIGHT(Created@row, 8), 2))))) = 12, 0, ((VALUE(IF(LEFT(RIGHT(Created@row, 8), 1) = " ", MID(RIGHT(Created@row, 8), 2, 1), LEFT(RIGHT(Created@row, 8), 2))))))) * 60 + VALUE(LEFT(RIGHT(Created@row, 5), 2)) + IF(RIGHT(Created@row, 2) = "PM", 720, 0)) / 1440

I'm avoiding the area where the comma shows up.