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
