Formula Help: RGB & due date, returning "Invalid Column Value"

This is driving me crazy. I want to display a "Green" if an item is completed, "Red" if it has not been completed and the due date is past, and nothing otherwise. I am using the following formula:

=IF(ISBLANK([Scheduled Date]@row), "", IF(NOT(ISBLANK([Completed Date]@row)), "Green", IF(AND([Scheduled Date]@row > TODAY()), ([Completed Date]@row = "blank"), "Red")))

It works unless the due date is in the future, in which case I get "#INVALID COLUMN VALUE." What am I doing wrong? If I put anything at the end to return a "yellow," I get other errors.

Thanks for your help. Jason

Find more posts tagged with

Accepted answers

Paul Newcome

Try something like this...

=IF([Completed Date]@row <> "", "Green", IF([Scheduled Date]@row < TODAY(), "Red"))

Jason Serin

Thanks Paul! I will give it a try. I could not let this go after I posted the question. After a few more hours of trail and error, I got the following to work and it seems to work without returning errors:

=IF(ISBLANK([Scheduled Date]@row), "", IF(NOT(ISBLANK([Completed Date]@row)), "Green", IF(TODAY() > [Scheduled Date]@row, "Red")))

Jason

All comments

Paul Newcome

Try something like this...

=IF([Completed Date]@row <> "", "Green", IF([Scheduled Date]@row < TODAY(), "Red"))

Jason Serin

=IF(ISBLANK([Scheduled Date]@row), "", IF(NOT(ISBLANK([Completed Date]@row)), "Green", IF(TODAY() > [Scheduled Date]@row, "Red")))

Jason

Paul Newcome

Yes. That should work as well.

Jason Serin

Thanks!

Paul Newcome

Happy to help! 👍️

Jason Serin

Hi Paul. Can I contact you directly for additional help?

Paul Newcome

Are you able to explain the problem you are encountering here in the Community?

Jason Serin

Hi Paul, I did here:

https://community.smartsheet.com/discussion/68973/check-if-values-in-two-columns-match-values-in-two-columns-in-another-sheet

Paul Newcome

@Jason Serin It looks like @Andrée Starå has replied. He definitely knows what he is doing in Smartsheet, so I would imagine he should be able to provide a working solution.

One thing that will certainly help is if you could share some screenshots. Sensitive/confidential data can be removed, blocked, and/or replaced with "dummy data" as needed. You could even use manually entered data to show a "mocked up" version of what you are trying to accomplish.

Jason Serin

Thanks Paul.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.