RYG - AHHHHH!!!!

Brian@anderson_Solutions

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

Paul Newcome

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

Paul Newcome

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

hollyconradsmith

HI @Brian@anderson_Solutions

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.

Brian@anderson_Solutions

THANK YOU BOTH!!!!!

SUCESS!!!!!!

Paul Newcome

@hollyconradsmith Just some quick pointers...

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.

Paul Newcome

@Brian@anderson_Solutions Happy to help. 👍️