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”))))))
  • 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 Oct 19 '15 at 15:29
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.

  • Will give it a try substituting the columns options. Without wishing to appear total dense what is the INT element of the formula? – Janella Oct 5 '15 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 Oct 5 '15 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 Oct 5 '15 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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