IF 30 days past Planned end date show red
I am trying to create a formula that looks at Planned End Date and determines the following:
If <= Today then Green
IF 1 day past planned end date then Yellow
IF 30 days past end date then Red
=IF([PLANNED END DATE]@row(30), "Red", IF([PLANNED END DATE]@row(1), "Yellow", IF([PLANNED END DATE]@row=TODAY)
Can I do this without having to create a helper column?
Answers
-
=IF([Planned End Date]@row<=TODAY(-30), "Red", IF([Planned End Date]@row=TODAY(-1), "Yellow", IF([Planned End Date]@row<=TODAY(), "Green"
The above is the way you'd do it. However, you're going to have some problems I think with this exact formula. Your criteria I don't think are planned out properly. For example, this formula will mark things 30 days or more passed the Planned End Date as Red. Things will only be in Yellow if it's only 1 day passed the Planned End Date. When it's 2 days passed it'll be Green I think. You'd want to change the = to <= for the yellow logic. Then for the green logic instead of <= I believe you'd want to change that to greater than TODAY() or else I don't think Green would ever show up.
-
@Mike TV do you put the <= outside the parenthesis for example the below
=IF([PLANNED END DATE]@row >= (30), "Red", IF([PLANNED END DATE]@row <= (1), "Yellow", IF([PLANNED END DATE]@row > TODAY())))
-
They go outside the parenthesis but you're missing the TODAY function.
=IF([PLANNED END DATE]@row >= TODAY(30), "Red", IF([PLANNED END DATE]@row <= TODAY(-1), "Yellow", IF([PLANNED END DATE]@row > TODAY())))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!