Return a value that's RED in color if it's greater then a certain number

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • In Progress
I'm using a formula text field where I want to see if the number of days between 2 fields is greater than 7. If it is true, I want the number to be in RED, else just remain the same color. I used this one below but the return value was either <color=red> or color=black>. Should I be using formula numeric or formula rich text instead? Please also correct me with the If formula I'm using. Thanks for any help!

If([Week2 Date]-[Week1 Date]>Days(7),"<color=red>","<color=black>")
Photo of Ma. Isabel Gumanid

Ma. Isabel Gumanid

  • 256 Points 250 badge 2x thumb

Posted 1 month ago

  • 0
  • 1
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 6,396 Points 5k badge 2x thumb
Because you cannot color a checkbox output value (as a checkbox; it outputs as a value of 0 or 1) and because you are probably looking for something is less-complicated to implement; you would be better using a text-output value.

Formula-Rich-Text field:

If( [Week2 Date] - [Week1 Date] > Days(7), "<div style=\"color:red;\">> 7 Days<div>","")

This displays in red text > 7 Days and displays no value when the criteria is false.

To display colored-check marks you would need to download and store some custom images to be used instead of text as an output.  Quick Base does not have a red or black check image in their Icons list which would have been ideal.  If any of the other icons Quick Base has, work for your needs; then your fomula could be:

If( [Week2 Date] - [Week1 Date] > Days(7),
"<div><img src=\"https://images.quickbase.com/si/24/221-point_red.png \" alt=\"\" title=\"> 7 Days\" width=\"16\" height=\"16\" /></div>",
"<div><img src=\"https://images.quickbase.com/si/24/222-point_green.png \" alt=\"\" title=\"< 7 Days\" width=\"16\" height=\"16\" /></div>")

If you wanted to see the number of days as your output then you could do this:


var text daysBetween=ToText(ToDays([Week2 Date] - [Week1 Date]));

If([Week2 Date] - [Week1 Date] > Days(7),
"<div style=\"color:red;\">"&$daysBetween&"<div>",
"<div style=\"color:black;\">"&$daysBetween&"<div>")


(Edited)
Photo of Ma. Isabel Gumanid

Ma. Isabel Gumanid

  • 256 Points 250 badge 2x thumb
Got it. I appreciate your help so much. I hope there is a way to get this fixed.
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 6,396 Points 5k badge 2x thumb
If(Round(ToNumber(ToText(ToDate([Week 2 Date]) - ToDate([Week 1 Date]))),2) > 7,
Photo of Ma. Isabel Gumanid

Ma. Isabel Gumanid

  • 256 Points 250 badge 2x thumb
No error but it still returns the number in decimal i.e. 8.0022154
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 6,396 Points 5k badge 2x thumb
Can you give me the two date/time field values that you have so I can try to replicate on my end with the same data.

I think you need to round the variable as well; because that is what is being "displayed".
(Edited)
Photo of Ma. Isabel Gumanid

Ma. Isabel Gumanid

  • 256 Points 250 badge 2x thumb
You're right. Rounding the variable is what fixed it. Thank you!