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?

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    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! 😊

Answers

  • Holden J
    Holden J ✭✭✭
    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")))

  • TenilleF
    Options

    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
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    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!