I'm trying to create a nested IF formula and am stumped. Please help!
My columns are: Finish ... which is a date field, Status ... which can be Complete, Not Started or In Progress, and a column where I want to insert a Green, Yellow, or Red symbol based on logic.
My needs: If the finish date is in the past and the status is not 'complete' I want to insert a RED symbol. If the finish date with within 5 days of today's date and the status is not 'complete' I want to insert a Yellow symbol. Otherwise symbol should be inserted as Green.
My stab at a formula:
=IF(AND(TODAY() > [Finish]@row, [Status]@row <> "Complete", "Red"), IF[Finish]@row < TODAY(+5), "Yellow"), "Green")
Any advice?
Best Answer
-
As Julie said, yes, just a couple of parentheses -- so close! You may also want to add in the check if it's complete before coloring yellow?
=IF(AND(TODAY() > Finish@row, Status@row <> "Complete"), "Red", IF(AND(Finish@row < TODAY(+5), Status@row <> "Complete"), "Yellow", "Green"))
Answers
-
Hi @Parker103
Try this:
=IF(AND(TODAY() > Finish@row, Status@row <> "Complete"), "Red", IF(Finish@row < TODAY(+5), "Yellow", "Green"))
All I did was change around some parentheses. You were close!
www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt
-
Here's what I would recommend.
=IF([Status]@row = "Complete", "Green", IF(TODAY()>Finish]@row, "Red" , IF([Finish]@row<TODAY(5) , "Yellow" , "Green"))
Hope it helps!
Best,
Zach Hall
Training Delivery Manager / Charter Communications
-
As Julie said, yes, just a couple of parentheses -- so close! You may also want to add in the check if it's complete before coloring yellow?
=IF(AND(TODAY() > Finish@row, Status@row <> "Complete"), "Red", IF(AND(Finish@row < TODAY(+5), Status@row <> "Complete"), "Yellow", "Green"))
-
Yes @Katie G , that is a great point!
-
Thanks VERY much for the help. Formula is working great :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 283 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!