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

Chad Dixon

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.

Thanks,

Chad

Jenn Hilber

Hi Chad,

Try re-organizing your formula:

=IF(Done@row = 1, "Green", IF([Due date]@row > TODAY(5), "Green", IF([Due date]@row < TODAY(), "Red", "Yellow")))

NOTE: @row replaces the row number and will work on any row - is says "current row" instead of "row 3".

Chad Dixon

That's perfect - thank you!

Jenn Hilber

Hi Chad,

Try re-organizing your formula:

=IF(Done@row = 1, "Green", IF([Due date]@row > TODAY(5), "Green", IF([Due date]@row < TODAY(), "Red", "Yellow")))

NOTE: @row replaces the row number and will work on any row - is says "current row" instead of "row 3".

Chad Dixon

That's perfect - thank you!