# Needing help with a formula for project health

Options
✭✭

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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

• ✭✭
Options

Hi Holden,

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?

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!