I am trying to color records that are less the 4 days old green, records between 4 and 7 days old yellow and records older then 7 days red. Please help?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

If(

[Exception Create Date]-Today() <= Days(3),"green",

[Exception Create Date]-Today() >= Days(5), and [Exception Create Date]-Today() <= Days(6),"yellow",

[Exception Create Date]-Today() >= Days(7),"red",

"")

Photo of Troy

Troy

  • 110 Points 100 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of Brendan

Brendan

  • 40 Points
Hi Troy,

Try creating a Formula Text field with the following formula:

If(

[Exception Create Date]-Today() <= Days(3), "<div style=\"background-color:green;\">"& [Exception Create Date] &"</div>",

[Exception Create Date]-Today() >= Days(5) and [Exception Create Date]-Today() <= Days(6),"<div style=\"background-color:yellow;\">"& [Exception Create Date] &"</div>",

[Exception Create Date]-Today() >= Days(7),"<div style=\"background-color:red;\">"& [Exception Create Date] &"</div>",

"")

*Don't forget to click the "Allow some HTML tags to be inserted in the field" checkbox in the formula field's properties page.

Hope this helps!  Let me know how it goes.
Photo of Troy

Troy

  • 110 Points 100 badge 2x thumb
This seems to only color the field. I need the entire row colored based of the of the less then 4 days from Exception Create Date "green" Exception Create Date is older then 4 days but lower than 7 and anything over 7 days colored red.
Photo of Brendan

Brendan

  • 40 Points
Hi Troy,

Thanks for following up!  Sorry I had misunderstood.

If you need the entire row of the report to be highlighted then you can put a similar formula in the report's builder page.  Go to the report's builder and scroll down to the Options section.  Click the "Highlight records based on criteria" checkbox and put the following formula:

If(

[Exception Create Date]-Today() <= Days(3),"green",

[Exception Create Date]-Today() >= Days(5) and [Exception Create Date]-Today() <= Days(6),"yellow",

[Exception Create Date]- Today() >= Days(7),"red",

"")

It looks like you had an extra comma before the "and."

Test app report builder: http://screencast.com/t/SnBSMG78
Report view: http://screencast.com/t/miW1N5uYYIX
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Troy,

I think that you were close with your original formula aside from that extra comma which Brendan spotted.  But you have the date comparisons reversed.  Future dates are "larger" then past dates, so it needs to be this way.



If(

Today() - [Exception Create Date] <= Days(3),"green",

Today() - [Exception Create Date] >= Days(5), and Today() - [Exception Create Date] <= Days(6),"yellow",

Today() - [Exception Create Date] >= Days(7),"red")



I am assuming that  the [Exception Create Date] field is a Date field and not a Date / Time field type.  If its the latter, then you would need to do this.

If(

Today() - ToDate([Exception Create Date]) <= Days(3),"green",

Today() - ToDate([Exception Create Date]) >= Days(5), and Today() - ToDate([Exception Create Date]) <= Days(6),"yellow",

Today() - ToDate([Exception Create Date]) >= Days(7),"red")



Laura Hillier has a great app here to help choose colors, I find red is pretty harsh for readability. https://laurahillier.quickbase.com/db/bhy8pumuk?a=q&qid=1

You may find that a color code like #EE6363 work better.



.......    Today() - ToDate([Exception Create Date]) >= Days(7),"#EE6363")

Photo of Troy

Troy

  • 110 Points 100 badge 2x thumb
Thanks! I was going crazy trying to figure this out. Sometime it only takes a different perspective to see whats wrong.