What is wrong with my automated colour column formula?

Bottomley

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:

1. If Start Date has not commenced yet - Grey
2. If due date is within 6 months and the progress is under 10%, Yellow; if 10+%, Green.
3. If due date is within 3 months and progress is under 10%, Red ; if under 30%, Yellow; if 30+%, Green.
4. If due date is within 1 month and progress is under 20%, Red; if under 50%, Yellow; if 50+%, Green.
5. If due date is within 2 weeks and progress is under 80%, Red; if 80+%, Green.
6. If due date is within 5 days and progress is under 95%, Red; if 95+% Green.
7. 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:

1. 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?
2. 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?
3. 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

Paul Newcome

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"))))))))

Bottomley

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

Paul Newcome

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

Bottomley

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.

Paul Newcome

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

Bottomley

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...

Bottomley

More examples:

Paul Newcome

You do not need the AND function.

Bottomley

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

Paul Newcome

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

Bottomley

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...

Paul Newcome

Can you provide another screenshot so we can compare the data generating the blanks to the formula piece by piece?