Needing help with a formula for project health

TenilleF

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?

Nick Korna

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

Holden J

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")))

TenilleF

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!

Nick Korna

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