Troubleshooting formula and or row

cpo1wh

I created a formula, but have problems. The formula is ........ =IF([Status Date]@row >= Today@row - 14, "Red", "Green") ....... The formula work fine in some rows, but not all. I'm puzzled as to why this is the case. Does the master sheet need to be purged for underlying errors of some kind. I can't duplicate the error message in a test sheet. The error message is ..... #INVALID OPERATION ......

Also, when a blank [Status Date] cell is encountered and the (Today) cell is populated, the RGB ball always goes to "Green". Same is true if (Today) cell is blank and [Status Date] is populated. If both reference cells are blank, the RGB ball goes to "Red". I would like the blank cell to remain blank if possible and work accordingly with the formula for populated cells. I guess I need to learn more about how a blank cell is treated. But I'm still puzzled about the error message on certain cells that appear to be benign. Thanks for any help.

=IF([Status Date]@row >= Today@row - 14, "Red", "Green")

Paul Newcome

To take care of the empty cells issue to only run if both cells have a date in them...

 

=IF(AND(ISDATE([Stateus Date]@row), ISDATE(Today@row)), IF([Status Date]@row >= Today@row - 14, "Red", "Green"), "")

.

To help with the other issue, screenshots would make things much easier.

cpo1wh

Thanks for your reply Paul. I am uploading a couple snippets of the problematic Smartsheet. I used your formula and it works great with my testing Smartsheet. No issues at all that I can find. But I still have issues with the master live file. The formula (yours) works for some cells, but not all. I no longer get the #INVALID OPERATION error, but when the formula is applied to many cells, they remain blank even though there is data in the involved cells. Is there a "scrub" program that can be used to check a file for underlying corruption errors? FYI,the [Status Date] cell is populated by formula .... =IF([Wil Notes]@row = "Resubmitted", Today@row)..., but it does not seem to be a problem. As you can see, many of the cells remain blank, even though the formula exists in each cell that you see. I appreciate your help with this and wonder if you can point me to learning resources where I can begin to learn more about the intricacies of Smartsheet formulas. I do use the formula example sheet. The 2nd snippet shows the formula that has been applied to all of the visible [14 Past] cells. Thanks again. 

 

 

cpo1wh

Thanks Paul. I added a comment and likely should have sent it as a reply. Sorry. Have a great day and 4th.

Paul Newcome

For the lower rows only... Remove the IF(AND(.........) portion from the beginning. Since both columns have dates in them, you shouldn't need that portion to generate a color. 

 

Let me know the results, and we can go from there. Either way it is looking like you will probably need to reach out to support, but if we can narrow down the issue, they are more likely to be able to help you out a little faster.

Paul Newcome

No worries. Either way works as I get the email notifications. At a certain point replies get very hard to deal with too as they can only go so far over to the right.

cpo1wh

Thanks for your speedy reply. I deleted the IF(AND(.... at the beginning. Now, those same cells that were always blank and should have been populated by the first formula have a #INVALID OPERATION error message. My guess is, this file may be corrupted in some way. It has 2500 records in it and is used by 4 or 5 other people simultaneously at times. I was just recently invited to help with their project, so I am unfamiliar with all of the Smartsheet changes that have been made along the way. Even so, I want to get to the bottom of the issue.Thanks again.

Paul Newcome

So the formula that reads:

 

=IF([Status Date]@row >= Today@row - 14, "Green", "Red")

 

is triggering an #INVALID OPERATION error?

 

I am not sure if you did or not, but I forgot to specify taking the corresponding portion of the IF/AND off of the end of the formula as well. It should look like the one here in this post.

cpo1wh

Paul, I found the problem. There was a section of the database that had been hard coded with "mm/dd/yy" in a formula in the [Status Date] column. Once I changed all of those records using the embedded pop-up calendar date, (mostly using copy and paste) presto, the records were fine. So, in this case, the date field accepted dates in "mm/dd/yy" format, but it did not like it when the cell was tinkered with again. My error was assuming a date field that populated without error was okay. As I discovered, such is not the case. So, I have a question for you if you don't mind. What is the difference between a hard coded date and one that is embedded in the Smartsheet calendar. There must be a difference. Is this something that Smartsheet should be aware of, or is this just my inexperience with such matters? Thanks again for your help. Your first solution was spot on and all the null fields stayed unpopulated as they were supposed to. Lesson learned, I hope.

Paul Newcome

It depends on how the formula was written. If the result of the formula is populated by a DATE function, it SHOULD work as a date. 

 

If the formula is written to populate as

 

## + "/" + ## + "/" + ##

 

then it will actually be looked at as a text string.

 

If that doesn't answer your question and if you still have the formula or can remember what it was (or at least how the result was populated), I'd be happy to take a look and let you know what I think.

cpo1

Here is an example of what I was doing, except that I wanted the date to be a static fixed date, so I used "07/06/19" in lieu of TODAY(). This formula worked and populated the cell with 07/06/19 and I assumed that the date was a date, not a text date since the quotation marks were not present. I came to realize that the date column will only work as a date column with valid dates entered. For example, if you manually enter "07/06/19" into a date cell, it will read "07/06/19", including the quotation marks. But if used in a formula, the quotation marks are dropped; hence my assumption that a valid date was being entered.

My method with "07/06/19" populates and works fine provided you are not going to compare that cell later as a valid date, which I did and had to go back and change all of the dates with manual entries. 

 

=IF(NOT([Wil Notes]@row = ""), TODAY(), "") ..........if date could be locked and not change tomorrow this could work great

 

=IF(NOT([Wil Notes]@row = ""), "07/06/19", "")...........this is what I used initially before I discovered my problem

Hopefully, I'm explaining it ok. Thanks again for helping. If there is a way to lock the date, I would appreciate knowing about it. I can't seem to find a date function that would work, but then I'm not well versed with all of the functions and logic. Still plugging away though.

Paul Newcome

As of right now there is no way to timestamp other than the system columns of Created and Modified. From what I understand though, this feature is likely to be "coming soon". The date would have to be manually entered or...

 

If the trigger you are looking to timestamp happens to be the very last edit to the row (and no other edits are necessary), you can use the Modified (date) system column and then use an Automation to lock the row once xyz trigger has occurred. 

.

In regards to using a formula to enter dates that are usable...

 

=IF(NOT([Wil Notes]@row = ""), "07/06/19", "")

 

You would need to use the DATE function to populate it as an actual date.

 

=IF(NOT([Wil Notes]@row = ""), DATE(2019, 07, 06), "")