Forum Discussion

Lightning0114's avatar
Lightning0114
Qrew Cadet
6 years ago

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

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>")
Formulas and functions
Tables and fields
  • LauraThacker's avatar
    LauraThacker
    Qrew Captain
    6 years ago
    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>")


    • Lightning0114's avatar
      Lightning0114
      Qrew Cadet
      6 years ago
      Awesome! The last one is what I needed. It works well. However, it returns a decimal value and I would want to have a number rounded off so I used this: 

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

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

      Can you please enlighten me why this has an error saying it's expecting comma after Round function?
    • LauraThacker's avatar
      LauraThacker
      Qrew Captain
      6 years ago
      If(Round([Week2 Date] - [Week1 Date]),2)>Days(7),
      "<div style=\"color:red;\">"&$daysBetween&"<div>",
      "<div style=\"color:black;\">"&$daysBetween&"<div>")

      Sorry I accidentally submitted the first time with a 2-decimal place solution.
      When you Round() something; you have to tell Quick Base how to round.  You do that by inserting a value after the values you are rounding.  If you read the Function Help on this; it shows you 3 types of how Round() can be used to force the calculation to 0 or 2 decimal places.

    • Lightning0114's avatar
      Lightning0114
      Qrew Cadet
      6 years ago
      Followed exactly the formula but it prompts the same error. Am I missing something?