Project Health (Risk) Symbols Formula

Question

Hello,

I am using the project status and end date to determine my project health, but I cant seem to figure out what I need to clear up three issues. I would like to return a yellow in the health column when there is no end date and the project status is either not started or in progress.

I would like to return a blank value in the health column when there is no project status and end date.

I would like to return a red value in the health column if the current date is past the end date and there is no project status.

My current formula is:

=IF(AND(Status@row = "In Progress", TODAY() > [End Date]@row), "Red", IF(AND(Status@row = "Not Started", TODAY() > [End Date]@row), "Red", IF(Status@row = "Complete", "Green", "Yellow")))

Thanks in advance!

Sheri

Nick Korna · Accepted Answer

Hi @Sheri.C

I believe this will accomplish what you're after:

=IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), ISBLANK([End Date]@row)), "Yellow", IF(Status@row = "Complete", "Green", IF((TODAY() - [End Date]@row > 0), "Red", "")))

If the end date is only filled in when the project is completed, this should be fine. If it is a projected end date then the formula may need an addition to account for projects being "In Progress" with a date in the End Date field - currently they will come back blank.

Hopefully this helps, let us know if you need anything extra adding on!