Formula through API

RedSPINE

Hello,

I'd like to make a kinda tricky formula write with the API. I don't know if it is possible but I guess it is. I just have no idea how to properly do it. Could someone land a hand on this one ? Here's the case:

I'm going to add rows in the sheet, each of them has two date type fields I'm interested in. I want the first column (which is the flag for 'At Risk') to be True if the first date (Column 5) is higher than the second (Column6). As the second date isn't filled every time, I don't want the condition to trigger if the second date field is empty.

Do you see a way to do this ?

 

Thank you !!

dAVE Inden

You can do this with a formula. First you would need to test if the cell in Column6 is a Date value first. The function for that is ISDATE(). If that returns true you could have it then test if the date in Column5 is greater than it.

I made a formula to place in the flag column like this:

=IF(ISDATE([Column6]1), [Column5]1 > [Column6]1)

That first checks for if the value in Column6 (in this case row 1) is a date or not. If it is then it evaluates if the Column5 is greater than Column6. That evaluates to true or false and will display a flag as needed.

Side note, this is more of a Smartsheet Formulas question than an API question. Providing a formula via the API is just a string. But, the syntax of a formula is the same as what one would write working in the UI. For other questions about creating formulas I would use the Formulas tag on your posts here.