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:

  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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!