Needing help with a formula for project health
hi, i am trying to figure out a formula, but can't seem to work it out.
if a task is in progress, the project health column should change to green
if a task is 15+ days from start date, the project health column should change to yellow
if a task is within 15 days of the end date, the project health column should change to red
is this possible?
Best Answer
-
If the status does matter (i.e. should be "In Progress" plus the date criteria), then you will need to make the first two conditions have AND:
=IF(AND([End Date]@row - TODAY() <= 15, Status@row = "In Progress", [End Date]@row <> ""), "Red", IF(AND((TODAY() - [Start Date]@row >= 15), Status@row = "In Progress"), "Yellow", IF(Status@row = "In Progress", "Green", "Default")))
This could be further modified - for example, if you have the dates missing an In Progress status will show as Green, but this fulfils the criteria given.
Sample data/output:
If you've any problems/questions or other things that need adding in, just post! 😊
Answers
-
Hi @TenilleF - I think this should work:
=IF([Status]@row = "In Progress", "Green", IF(TODAY() - [Start Date]@row >= 15, "Yellow", IF([End Date]@row - TODAY() <= 15, "Red", "Default")))
-
Hi Holden,
Thanks for your reply.
That doesn't seem to be working for the 'yellow' or 'red' options, with the couple of date changes I have made for testing. Any other suggestions?
Thanks in advance!
-
If the status does matter (i.e. should be "In Progress" plus the date criteria), then you will need to make the first two conditions have AND:
=IF(AND([End Date]@row - TODAY() <= 15, Status@row = "In Progress", [End Date]@row <> ""), "Red", IF(AND((TODAY() - [Start Date]@row >= 15), Status@row = "In Progress"), "Yellow", IF(Status@row = "In Progress", "Green", "Default")))
This could be further modified - for example, if you have the dates missing an In Progress status will show as Green, but this fulfils the criteria given.
Sample data/output:
If you've any problems/questions or other things that need adding in, just post! 😊
Help Article Resources
Categories
Check out the Formula Handbook template!