Adding to a Complex Formula
Good morning All:
I am trying to add RYG to my Formula. Here are my conditions:
If 15+ Days Behind Schedule = Red
If Between 5-15 Days Behind Schedule = Yellow
If 0 or Ahead of Schedule = Green
Current Formula without RYG:
=IF(ISBLANK([Actual Date]@row), "", IF(AND(ISBLANK([Planned Date]@row), ISDATE([Actual Date]@row)), "Start Date Missing", IF([Planned Date]@row - [Actual Date]@row < 0, ABS([Planned Date]@row - [Actual Date]@row) + " Day(s) Behind Schedule", IF([Planned Date]@row - [Actual Date]@row > 0, ABS([Planned Date]@row - [Actual Date]@row) + " Day(s) Early", IF([Planned Date]@row - [Actual Date]@row = 0, "0 Variance, On Schedule")))))
Thanks
Best Answers
-
You could just wrap it in an IFERROR statement.
=IFERROR(DATEONLY([Actual Date]6) - [Planned Date]6), "")
-
Happy to help! 👍️
Answers
-
How exactly are you trying to incorporate RYG? You will not be able to show the colored ball AND additional data in the same cell at the same time.
-
I want to add the Color, not the ball.
-
Ok. And do you want to add it before or after the text that is currently being generated?
-
After
-
In that case, all you should have to do is add a nested IF to the end of your existing formula.
=ORIGINAL FORMULA + IF([Planned Date]@row - [Actual Date]@row <= -15, " Red", IF([Planned Date]@row - [Actual Date]@row <= -5, " Yellow", IF([Planned Date]@row - [Actual Date]@row >= 0, " Green")))
-
I get the same results but with no color.
-
Disregard! I was expected the cell to turn Green. not the actual word Green
-
Thanks! I may be able to use this type of formula for something else.
-
Ah. If you were wanting the cell to turn RYG, you would need to set up a column that tracks the variance then use conditional formatting based on that to change the cell color.
-
How do I remove an error from the formula when I do not have a date in the Plan or Actual Column? I treid ISBlank but unsuccessful.
Current Formula:
=DATEONLY([Actual Date]6) - [Planned Date]6)
-
You could just wrap it in an IFERROR statement.
=IFERROR(DATEONLY([Actual Date]6) - [Planned Date]6), "")
-
Excellent, Thanks!
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!