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...
Formulas and functions
Tables and fields
Laura_Thacker's avatar
Laura_Thacker
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?
  • Laura_Thacker's avatar
    Laura_Thacker
    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?
  • Laura_Thacker's avatar
    Laura_Thacker
    Qrew Captain
    6 years ago
    Try this:

    If(Round(ToNumber(ToText(ToDate([Week 2 Date]) - ToDate([Week 1 Date]))),2) > 7,
  • Lightning0114's avatar
    Lightning0114
    Qrew Cadet
    6 years ago
    Got it. I appreciate your help so much. I hope there is a way to get this fixed.
  • Laura_Thacker's avatar
    Laura_Thacker
    Qrew Captain
    6 years ago
    If(Round(ToNumber(ToText(ToDate([Week 2 Date]) - ToDate([Week 1 Date]))),2) > 7,
  • Lightning0114's avatar
    Lightning0114
    Qrew Cadet
    6 years ago
    No error but it still returns the number in decimal i.e. 8.0022154
  • Laura_Thacker's avatar
    Laura_Thacker
    Qrew Captain
    6 years ago
    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".
  • Lightning0114's avatar
    Lightning0114
    Qrew Cadet
    6 years ago
    You're right. Rounding the variable is what fixed it. Thank you!