How to make the health column mark red if Actual End date> Today using the column formula
I have health column where I am managing the status of completed, in progress and planned with Green, Yellow and blue color respectively. Whereas I also nested the IF AND in same column if the Actual End Date> today and the status is On-Hold then the health column should be automictically be red. This is the formula.
=IF(Status@row = "Completed", "Green", IF(Status@row = "In Progress", "Yellow", IF(AND([Actual End Date]@row > TODAY(), Status@row = "On-Hold"), "Red", IF(Status@row = "Planned", "Blue", ""))))
But the formula isn't working for "IF(AND([Actual End Date]@row > TODAY(), Status@row = "On-Hold"), "Red"" this part. Any suggestions @Paul Newcome @Genevieve P.
Answers
-
Are you sure the greater than today is what you want to be Red. Only asking because I know I will type the incorrect greater than or less than symbol myself sometimes and it would make more sense for it to be Red if the date was less than today.
-
=IF(Status@row = "Completed", "Green", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "Planned", "Blue", IF(AND(Status@row = "On-Hold", [Actual End Date]@row > TODAY()), "Red", ""))))
However this does not assign a color for On-Hold with a date less than today.
-
Hey @Ray Lindstrom Thanks for guiding, this formula is working fine for In Progress, Planned and completed. But "if the actual date is beyond today and the task status is not completed then it should mark red". This condition i am unable to met. Kindly guide me for that segment.
-
Hey @Hollie Green yes if actual date is more than today and the status is not completed then want the health column to show red other than that all the status have defined color.
Can you guide for same.
-
@moumita77 Your formula looks correct to work for that can you provide a screenshot of your sheet with column headers and what it is doing for the "Red" part of your formula. I copied your formula exactly and it worked
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 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!