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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!