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

  • Paul Newcome
    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"))))))))

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


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You do not need the AND function.

    thinkspi.com

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!