Risk Formula (Red, Yellow, Green)

Options

I am using the formula below to automatically calculate risk. I am using percent complete of a task against time complete off the start and end dates.

=IF([%CMPLT]@row >= (NETDAYS(TODAY(), [End Date]@row) / NETDAYS([Start Date]@row, [End Date]@row)), "Green", IF([%CMPLT]@row > 0, "Yellow", "Red"))

So if my start and end date calculates 50% of the time complete and we only have 30% of that task done we will get a "Yellow" orb.

This formula works great if we have current start dates. But if the start date is in the future the orb turns red. I don't know how to make a condition saying if start date is in the future default the orb to "green."

Any help would be much appreciated.

Tags:

Best Answer

Answers

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Options

    @ChristineB

    =IF([Start Date]@row > TODAY(), "Green", IF[%CMPLT]@row >= (NETDAYS(TODAY(), [End Date]@row) / NETDAYS([Start Date]@row, [End Date]@row)), "Green", IF([%CMPLT]@row > 0, "Yellow", "Red"))


    This should fix the problem, as it will first look for any that are starting in the future and make those green, before looking at any of the other values.

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • ChristineB
    ChristineB ✭✭
    Answer ✓
    Options

    @Colleen Patterson

    YES!

    =IF([Start Date]@row > TODAY(), "Green", IF([%CMPLT]@row >= (NETDAYS(TODAY(), [End Date]@row) / NETDAYS([Start Date]@row, [End Date]@row)), "Green", IF([%CMPLT]@row > 0, "Yellow", "Red"))

    This worked, thank you so much!

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Options

    Glad I was able to help. @ChristineB

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!