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
0 
Thanks Paul  I have had a go with your formula. Unfortunately there are still rows that do not have any status (Far RHS Column)...
0 
Those rows do not have due dates within the next 6 months.
thinkspi.com
0 
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.
0 
What is the error and how exactly are you trying to add it?
thinkspi.com
0 
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...
0 
More examples:
0 
You do not need the AND function.
thinkspi.com
0 
If I delete the AND I receive an #UNPARSEABLE message.
0 
You will also need to make sure you are deleting the corresponding closing parenthesis from the end of the formula.
thinkspi.com
0 
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...
0 
Can you provide another screenshot so we can compare the data generating the blanks to the formula piece by piece?
thinkspi.com
0