Getting #Invalid Operation
I have the following formula and am getting an #Invalid Operation message. I only get the error though when it returns "Red". The column is defined as a Date and is restricted to dates only. I think it has to do with the TODAY() part of the function.
=IF(AND([Due Date]@row < TODAY(), [Due Date]@row > " "), "Red", "Green")
In addition I did a test and created a new column, defined it as a Date and restricted it to dates only. I added =TODAY() in the field and got a message that 'This column is restricted to dates". So I am now even more convinced that the problem is with the TODAY() function.
Best Answer
-
What do you want to do with the condition [Due Date]@row > " "? Do you want to make sure that the Due Date is a date?
The issue with the formula lies in the fact that Smartsheet is treating the second condition ([Due Date]@row > " ") as comparing a date to a string, which is not valid.
Correct Approach:
If the goal is to check whether [Due Date]@row has a valid date (i.e., it is not blank), they should use the ISDATE() function to validate that it's a date. Here’s how the corrected formula would look:=IF(AND([Due Date]@row < TODAY(), ISDATE([Due Date]@row)), "Red", "Green")
In this case, the Due Date column does need to be a Date column, but it doesn't have to be explicitly "restricted to dates only" for the formula to work.
- If the column is already restricted to dates, there’s less need for ISDATE(), but it’s still a good practice to include it.
- If the column is not restricted, ISDATE() is crucial for preventing errors.
Answers
-
Might be the space > " " Try. =IF([Due Date]@row < TODAY(), "Red", "Green") this should return Red if date in the past and Green if today or in the future.
Cheers.
-
What do you want to do with the condition [Due Date]@row > " "? Do you want to make sure that the Due Date is a date?
The issue with the formula lies in the fact that Smartsheet is treating the second condition ([Due Date]@row > " ") as comparing a date to a string, which is not valid.
Correct Approach:
If the goal is to check whether [Due Date]@row has a valid date (i.e., it is not blank), they should use the ISDATE() function to validate that it's a date. Here’s how the corrected formula would look:=IF(AND([Due Date]@row < TODAY(), ISDATE([Due Date]@row)), "Red", "Green")
In this case, the Due Date column does need to be a Date column, but it doesn't have to be explicitly "restricted to dates only" for the formula to work.
- If the column is already restricted to dates, there’s less need for ISDATE(), but it’s still a good practice to include it.
- If the column is not restricted, ISDATE() is crucial for preventing errors.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!