What is wrong with my automated colour column formula?
I am looking to automate the colour “status” for my rows based on the health of the action  a calculation based on the ‘% Complete’ and the ‘Due Date’. The goal is to demonstrate a contextual health status. For example, even if an action is only 10%, if the due date is still 6+ months away, the action is still Green due to the timeframe before it's due.
The formula below is based on the following rules:
 If Start Date has not commenced yet  Grey
 If due date is within 6 months and the progress is under 10%, Yellow; if 10+%, Green.
 If due date is within 3 months and progress is under 10%, Red ; if under 30%, Yellow; if 30+%, Green.
 If due date is within 1 month and progress is under 20%, Red; if under 50%, Yellow; if 50+%, Green.
 If due date is within 2 weeks and progress is under 80%, Red; if 80+%, Green.
 If due date is within 5 days and progress is under 95%, Red; if 95+% Green.
 If due date has lapsed or it is the due date and progress is not 100%, Red.
This is the formula:
=IFERROR(IF(AND([Due Date]@row < TODAY(+182), [% Complete]@row < 0.1), "Yellow", IF(AND([Due Date]@row < TODAY(+182), [% Complete]@row > 0.1), "Green", IF(AND([Due Date]@row < TODAY(+91), [% Complete]@row < 0.1), "Red", IF(AND([Due Date]@row < TODAY(+91), [% Complete]@row < 0.3), "Yellow", IF(AND([Due Date]@row < TODAY(+91), [% Complete]@row > 0.3), "Green", IF(AND([Due Date]@row < TODAY(+31), [% Complete]@row < 0.2), "Red", IF(AND([Due Date]@row < TODAY(+31), [% Complete]@row < 0.5), "Yellow", IF(AND([Due Date]@row < TODAY(+31), [% Complete]@row > 0.5), "Green", IF(AND([Due Date]@row < TODAY(+14), [% Complete]@row < 0.8), "Red", IF(AND([Due Date]@row < TODAY(+14), [% Complete]@row < 0.9), "Yellow", IF(AND([Due Date]@row < TODAY(+14), [% Complete]@row > 0.9), "Green", IF(AND([Due Date]@row < TODAY(+5), [% Complete]@row < 0.95), "Red", IF(AND([Due Date]@row < TODAY(+5), [% Complete]@row > 0.95), "Green", IF(AND([Due Date]@row < TODAY(), [% Complete]@row < 1), "Red", IF(AND([Due Date]@row = TODAY(), [% Complete]@row < 1), "Red", IF(AND([Due Date]@row = TODAY(), [% Complete]@row = 1), "Green", IF([Start Date]@row > TODAY(), "Gray"))))))))))))))))), "ERROR")
I have a few problems:
 When I try to include a rule to capture due dates that are beyond the 6 months at the beginning of the formula (e.g. IF(AND([Due Date]@row > TODAY(+182), [% Complete]@row 0, "Green") the formula reads as INCORRECT ARGUMENT SET. What am I doing wrong?
 Even without this issue above  the formula does not appear to capture all rows data. For example, a row with 15% complete, a start date of 27/10/21 and a due date of 01/07/22 has no colour status  it is blank. (Note: I also do not receive an error message for these rows). What am I missing in my dates?
 Other rows are coming up with an ERROR message. For example, a row with 30% complete, start date of 01/01/21 and a due date of 31/09/21
Answers

Try this...
=IF([Start Date]@row > TODAY(), "Gray", IF(AND([Due Date]@row<= TODAY(5), [% Complete]@row< 1), "Red", IF([Due Date]@row<= TODAY(5), IF([% Complete]@row< 0.95, "Red", "Green"), IF([Due Date]@row<= TODAY(14), IF([% Complete]@row< 0.8, "Red", "Green"), IF([Due Date]@row<= TODAY(30), IF([% Complete]@row< 0.2, "Red", IF([% Complete]@row< 0.5, "Yellow", "Green")), IF([Due Date]@row<= TODAY(90), IF([% Complete]@row< 0.1, "Red", IF([% Complete]@row< 0.3, "Yellow", "Green")), IF([Due Date]@row< TODAY(180), IF([% Complete]@row< 0.1, "Yellow", "Green"))))))))
thinkspi.com

Thanks Paul  I have had a go with your formula. Unfortunately there are still rows that do not have any status (Far RHS Column)...

Those rows do not have due dates within the next 6 months.
thinkspi.com

Thank you for flagging! But when I try to add a line regarding the deadlines beyond that period, at the end of the formula (E.g. =IF(AND([Due Date]@row > TODAY(180), "Green") I receive an error.

What is the error and how exactly are you trying to add it?
thinkspi.com

With the formula:
=IF([Start Date]@row > TODAY(), "Gray", IF(AND([Due Date]@row <= TODAY(5), [% Complete]@row < 1), "Red", IF([Due Date]@row <= TODAY(5), IF([% Complete]@row < 0.95, "Red", "Green"), IF([Due Date]@row <= TODAY(14), IF([% Complete]@row < 0.8, "Red", "Green"), IF([Due Date]@row <= TODAY(30), IF([% Complete]@row < 0.2, "Red", IF([% Complete]@row < 0.5, "Yellow", "Green")), IF([Due Date]@row <= TODAY(90), IF([% Complete]@row < 0.1, "Red", IF([% Complete]@row < 0.3, "Yellow", "Green")), IF([Due Date]@row < TODAY(180), IF([% Complete]@row < 0.1, "Yellow", "Green", IF(AND([Due Date]@row > TODAY(180), "Green"))))))))))
I do not receive an error  but not all cells have an a status colour:
What I find odd is that while the formula successfully applies to some  I do not receive the error message when there is no 'Start Date'  and some that still have start and end dates still receive an #INCORRECT ARGUMENT SET  while some do not have any colour coding at all despite the start and due dates...

More examples:


If I delete the AND I receive an #UNPARSEABLE message.

You will also need to make sure you are deleting the corresponding closing parenthesis from the end of the formula.
thinkspi.com

I believe I have:
=IF([Start Date]@row > TODAY(), "Gray", IF(AND([Due Date]@row <= TODAY(5), [% Complete]@row < 1), "Red", IF([Due Date]@row <= TODAY(5), IF([% Complete]@row < 0.95, "Red", "Green"), IF([Due Date]@row <= TODAY(14), IF([% Complete]@row < 0.8, "Red", "Green"), IF([Due Date]@row <= TODAY(30), IF([% Complete]@row < 0.2, "Red", IF([% Complete]@row < 0.5, "Yellow", "Green")), IF([Due Date]@row <= TODAY(90), IF([% Complete]@row < 0.1, "Red", IF([% Complete]@row < 0.3, "Yellow", "Green")), IF([Due Date]@row < TODAY(180), IF([% Complete]@row < 0.1, "Yellow", "Green", IF([Due Date]@row > TODAY(180), "Green")))))))))
But it is still leaving cells blank...

Can you provide another screenshot so we can compare the data generating the blanks to the formula piece by piece?
thinkspi.com
Help Article Resources
Categories
Check out the Formula Handbook template!