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

Jason Serin

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

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

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

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.