Formula to link End date and Health status
Hi,
I've just managed to be able to get my health symbols to correspond with the progress of my project using this formula :
=IF(Status@row ="Started", "Yellow", IF(Status@row ="Blocked", "Red", IF(Status@row = "Not Started","Blue", IF(Status@row ="Completed", "Green"))))
Would it be possible to now add another formula or add to this formula so that it links the Health to the Dates, With the status changing if the dates are past due?
Thank you in advance
Answers

Hi @Samad07,
It is absolutely possible  you can either use IF & AND for the health symbols, or if your status is derived by a formula (looking at a percentage, for example), then again, using IF & AND to look at the dates and any other criteria as required.
If you have any examples of what you would be looking to do, then happy to help you get the formula sorted.

Hi@Nick Korna ,
Thank you for the reply,
The status is currently manually controlled by the user and is linked with health using said formula but I would like to create a formula that works by first checking the status of the task. If the task is complete, then the formula will return a health symbol of "Green". Otherwise, the formula will check to see if the task is in progress. If the task is in progress and the end date of the task is in the future, then the formula will return a symbol of "Orange". If the task is in progress and the end date of the task is in the past, then the formula will return a value of "Red". Otherwise, the formula will check to see if the task is blocked. If the task is blocked, then the formula will return a value of "Red". Otherwise, the formula will assume that the task has not yet started and the formula will return a value of "Blue".
I've attempted using this formula but it returns a formula syntax error message
=IF(Status@row = "Completed", "Green", IF(Status@row = "In Progress" AND [Finish]@row >= TODAY(), "Orange", IF(Status@row = "In Progress" AND [Finish]@row < TODAY(), "Red", IF(Status@row = "Blocked", "Red", IF(Status@row = "Not Started", "Blue")))))
Thanks again and looking forward to a reply.

You need to rearrange the AND portions to use the correct syntax, along the lines of :
=IF(Status@row = "Completed", "Green", IF(AND(Status@row = "In Progress", Finish@row >= TODAY()), "Orange", IF(AND(Status@row = "In Progress", Finish@row < TODAY()), "Red", IF(Status@row = "Blocked", "Red", IF(Status@row = "Not Started", "Blue")))))
Simple output sample:
Hope this helps  if you have any questions/problems then just post! 🙂

Hi @Nick Korna ,
Thank you for that, so far it's working except for when the status is completed the health symbol completely disappears. I will attach an image below :

Change the "Completed" in the formula to "Complete"  the extra letter is why it can't find the result to give the green symbol.
Or change the status to completed as an option instead  whichever works for you! 🙂

Hi @Nick Korna ,
Don't know how I didn't notice that but the code is all good now. I appreciate the help.
Thank You!!!!

No problem at all, happy to have helped! 😊
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!