Using Case function to determine the color of a field

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I can't seem to get this formula working, I want if the the [Days Till Due] is greater then zero there is no change. If it is -1 or -2 then it is yellow and if it is -3 then it is red

Case([Days Till Due], [Days Till Due] <= -3, "<div style=\"color:black; background-color:Red;\">&[Days Till Due]&</div>", 
[Days Till Due] >= -2 and [Days Till Due] < 0,"<div style=\"background-color:Yellow;\">&[Days Till Due]&</div>", "")
Photo of Jordan

Jordan

  • 1,574 Points 1k badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Dan

Dan

  • 1,176 Points 1k badge 2x thumb
What type of field is "Days Till Due"?
Photo of Jordan

Jordan

  • 1,574 Points 1k badge 2x thumb
It is a numeric calculation 
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Just a little syntax error.  
Case([Field],
evaluation, answer is true,
evaluation, answer if true, 
answer if non-else)

Try this:

Case([Days Till Due],
-3, "<div style=\"color:black; background-color:Red;\">&[Days Till Due]&</div>",
-2, "<div style=\"background-color:Yellow;\">&[Days Till Due]&</div>",
-1, "<div style=\"background-color:Yellow;\">&[Days Till Due]&</div>", "")
Photo of Jordan

Jordan

  • 1,574 Points 1k badge 2x thumb
It partially worked in that the color did appear correctly when the due date was -1, I would like it so anything -3 or less would appear in red. So the -5 seen in the picture would appear in red, also with the numbers showing

Photo of Dan

Dan

  • 1,176 Points 1k badge 2x thumb
Try this:
if(
[Days Till Due] <= -3, "<div style=\"color:black; background-color:Red;\">"&[Days Till Due]&"</div>", 
[Days Till Due] >= -2 and [Days Till Due] < 0,"<div style=\"background-color:Yellow;\">"&[Days Till Due]&"</div>",
"")
Photo of Jordan

Jordan

  • 1,574 Points 1k badge 2x thumb
This worked! Thank you!