# 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:

Options

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!

• ✭✭✭✭✭✭
Options

=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.

Options

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!

• ✭✭✭✭✭✭
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!