Hi I'd like to ask a question as I've been struggling with this for some time now.
The scenario is a I have a task list. and I want to add a simple BRAG status, I’m not looking for icons at this moment in time just the words would be good.
This is what I'm trying to get:
- If the action is complete the BRAG should be Blue.
- If the action is Not Completed and is Overdue OR the due date is today – BRAG should be Red
- If the action in Not Completed and today is Due Date minus -2 days return Amber
- If the action is Not Completed and today is Due Date minus -3 days return Green.
The Not Completed or other status being used as opposed to Complete are:
- Not Started, In Progress, Deferred or Waiting on someone else.
I've tried and various variants, I've also tried OR for AND what ever I try I get a syntax error. Eventually I'd like to add colour icons...... BTW have a column created for the Today part of the equation:
=IF(Status=”Completed”,”BLUE”,(IF(AND(Status=”Not Started" "In Progress" "Deferred" "Waiting on someone else",Today>=[Due Date]),”RED”,(IF(AND(Status>=”Not Started""In Progress""Deferred""Waiting on someone else",Today>[Due Date]-2),”YELLOW”,(IF(AND(Status>=”Not Started""In Progress""Deferred""Waiting on someone else",Today>[Due Date]-3)”GREEN”))))))