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.

Find more posts tagged with

dates

Accepted answers

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

All comments

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.

=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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.