Formula Checked Box
Could you please help me to edit a formula?
There are 3 columns, Start Date, End Date and Completed (this is a check box)
I have a health column where the symbols change:
if a date is between today and the due date = Green
if the date is 3 days before the due date from today, then turn Yellow
if the date is on or past due date from today = Red
This is the formula:
=IF(ISBLANK([Start Date]@row), "", IF(AND([DUE DATE]@row > TODAY(), [DUE DATE]@row <= TODAY(3)), "Yellow", IF([DUE DATE]@row > TODAY(), "Green", IF([DUE DATE]@row <= TODAY(), "Red", ""))))
But my question is how can I edit the formula to add the Completed column making the symbol change to blue if the column Completed is checked?
Thank you so much and any help would be greatly appreciated!
Best Answer
-
If your intent is to have the Completed checkbox override the other values, then you want to put it before all the other colors. First, though, make sure your column format is set for RYGB:
=IF(ISBLANK([Start Date]@row), "", IF(Completed@row = 1, "Blue", IF(AND([DUE DATE]@row > TODAY(), [DUE DATE]@row <= TODAY(3)), "Yellow", IF([DUE DATE]@row > TODAY(), "Green", IF([DUE DATE]@row <= TODAY(), "Red", "")))))
Don't forget to add one more end parentheses at the end of the formula.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
If your intent is to have the Completed checkbox override the other values, then you want to put it before all the other colors. First, though, make sure your column format is set for RYGB:
=IF(ISBLANK([Start Date]@row), "", IF(Completed@row = 1, "Blue", IF(AND([DUE DATE]@row > TODAY(), [DUE DATE]@row <= TODAY(3)), "Yellow", IF([DUE DATE]@row > TODAY(), "Green", IF([DUE DATE]@row <= TODAY(), "Red", "")))))
Don't forget to add one more end parentheses at the end of the formula.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!