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
Best Answers
-
Try something like this...
=IF([Completed Date]@row <> "", "Green", IF([Scheduled Date]@row < TODAY(), "Red"))
-
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
Answers
-
Try something like this...
=IF([Completed Date]@row <> "", "Green", IF([Scheduled Date]@row < TODAY(), "Red"))
-
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
-
Yes. That should work as well.
-
Thanks!
-
Happy to help! 👍️
-
Hi Paul. Can I contact you directly for additional help?
-
Are you able to explain the problem you are encountering here in the Community?
-
Hi Paul, I did here:
-
@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.
-
Thanks Paul.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!