# RYG - AHHHHH!!!!

I CANT FIGURE THIS OUT!!! - SEND HELP!

I have a "At Risk" Column for this formula

I have a start date / finish date / % complete column

this is what I am trying to automate

Past the due date and not 100% complete (RED).

Less than 60% complete for tasks coming due within 5 business days (YELLOW).

Above 60% complete for tasks coming due within 5 business days (GREEN).

• ✭✭✭✭✭✭

Try something like this...

=IF(AND([% Complete]@row <> 1, [Due Date]@row< TODAY()), "Red", IF([Due Date]@row< WORKDAY(TODAY(), 5), IF([% Complete]@row< .6, "Yellow", "Green")))

• ✭✭✭✭✭✭

Try something like this...

=IF(AND([% Complete]@row <> 1, [Due Date]@row< TODAY()), "Red", IF([Due Date]@row< WORKDAY(TODAY(), 5), IF([% Complete]@row< .6, "Yellow", "Green")))

• ✭✭✭✭
edited 09/09/21

Try starting here?

=if(and(today()>[Due Date]@row, [% complete]<100), "Red", if(and([due date]@row=<today()-5, [% complete]<60), "Yellow", "Green")

I didn't type this out in Smartsheet so it probably needs some massaging.

Director of Technology & Innovation 💡 at Streamline

CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony

• THANK YOU BOTH!!!!!

SUCESS!!!!!!

• ✭✭✭✭✭✭

Percentages are read in Smartsheet as part of a whole or part of one. So 100% actually equals 1, 50% = 0.50, so on and so forth.

This portion here:

"[due date]@row=<today()-5"

is basically saying that if the due date is more than 5 days in the past. If today is the 9th, the today - 5 is the 4th. If it is coming up within the next 5 days meaning less than the 14th, then we instead should be adding 5 days to today's date instead of subtracting.

That also leads me to the WORKDAY function since the original post indicated "business days".

Finally, the original post indicated that it should only be green if within 5 business days and greater than 60%. The way your formula is written, it will be green even if greater than 5 days in the future because of how you used "Green" as the final argument. To get it how the original post had it specified, you would either need to do another IF/AND similar to your yellow, or an IF/IF similar to my formula above.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!