Formula to link End date and Health status

Samad07

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

Nick Korna

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.

Samad07

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.

Nick Korna

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! 🙂

Samad07

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 :

Nick Korna

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! 🙂

Samad07

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!!!!

Nick Korna

No problem at all, happy to have helped! 😊