1

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”))))))
Improve this question
1
  • Remember: Today calculations in a Formula do not update! No matter how many [Today] workarounds you read Todays calculations are not possible.
    – Danny '365CSI' Engelman
    Commented Oct 19, 2015 at 15:29

1 Answer 1

Reset to default
1

This is what I have done a couple days ago and it works perfectly well for me:

=(IF(AND(INT(Projects_CRiskScore)>0,INT(Projects_CRiskScore)<5),"Green",IF(AND(INT(Projects_CRiskScore)>4,INT(Projects_CRiskScore)<10),"Amber",IF(AND(INT(Projects_CRiskScore)>10,INT(Projects_CRiskScore)<25),"Red",IF(INT(Projects_CRiskScore)=25,"Purple","")))))

If you are doing that through the UI, change &lt; to < and &gt; to >

Hope this helps.

Improve this answer
3
  • Will give it a try substituting the columns options. Without wishing to appear total dense what is the INT element of the formula?
    – Janella
    Commented Oct 5, 2015 at 15:04
  • You can skip the INT part, it's bascically useless in my case. That funcion converts the value to a number. =(IF(AND(Projects_CRiskScore&gt;0,Projects_CRiskScore&lt;5),"Green",IF(AND(Projects_CRiskScore&gt;4,Projects_CRiskScore&lt;10),"Amber",IF(AND(Projects_CRiskScore&gt;10,Projects_CRiskScore&lt;25),"Red",IF(Projects_CRiskScore=25,"Purple","")))))
    – Paul Strupeikis
    Commented Oct 5, 2015 at 15:19
  • I don't think this formula works for me in the scenario describe originally as it doesn't deal with the date element of the argument. Could you "write2 it out how it should work?
    – Janella
    Commented Oct 5, 2015 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.