Help | IF/AND Formula for Automating RYG Based on Due Date and Whether Task is Complete

Hello -

I have a project plan that I am trying to populate R/Y/G status that uses two criteria: 1) is the task marked completed, and, 2) due date.

If the task is due by one day or more AND is not complete, = red

If the task is due today and is within 5 business days AND not complete, = yellow

If the task is marked complete OR is at least 6 business days or more in the future, =green

The formula I am using does everything above, BUT for tasks that are due today, are appears as green. I think I need to adjust the logic used for the yellow task by using greater than or equal to today + 5 days, but cannot seem to get it to work. Here's the formula I am using.

=IF((AND([Due Date]3 < TODAY(), Done3 = 0)), "Red", IF((AND([Due Date]3 > TODAY(), [Due Date]3 < TODAY(5), Done3 = 0)), "Yellow", "Green"))

Please help.



Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!