Help with Health Formula

cvilla

When I put this formula: =IF([% Complete]@row = "100%", "Green", IF(AND([% Complete]@row <> "100%", [Due Date]@row >= TODAY()), "Yellow", IF(AND([% Complete]@row <> "100%", [Due Date]@row < TODAY()), "Red", "")))

The status shows Red when the % complete is 100% and the Due date is past. How can I correct that?

Thanks

Mark.poole

@cvilla

The issue comes into it finding everything false except the due date being less then today(). What exactly are you wanting it to show. Know that I can fix it for you. Keep in mind the way nested IF functions work is they will continue until it finds the first true statement.

Ray Lindstrom

@cvilla

Try this:

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

Hope that helps!

All the best,
-Ray

Mark.poole

@cvilla

The issue comes into it finding everything false except the due date being less then today(). What exactly are you wanting it to show. Know that I can fix it for you. Keep in mind the way nested IF functions work is they will continue until it finds the first true statement.

Ray Lindstrom

@cvilla

Try this:

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

Hope that helps!

All the best,
-Ray

cvilla

Thanks, the formula above helped.😀

Ray Lindstrom

@cvilla,

Great news, and no problem, happy to help!

All the best,
-Ray