Getting #Invalid Operation

DeeDee_Thompson

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.

jmyzk_cloudsmart_jp

Hi @DeeDee_Thompson

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.

https://app.smartsheet.com/b/publish?EQBCT=f7cf42a464c941f585504c64c55573fb

Jason P

Hi @DeeDee_Thompson

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.

jmyzk_cloudsmart_jp

Hi @DeeDee_Thompson

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.

https://app.smartsheet.com/b/publish?EQBCT=f7cf42a464c941f585504c64c55573fb