Discussions

Expand all | Collapse all

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

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

    Posted 07-18-2019 16:06
    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>")


  • 2.  RE: Return a value that's RED in color if it's greater then a certain number

    Posted 07-18-2019 16:41
    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>")




  • 3.  RE: Return a value that's RED in color if it's greater then a certain number

    Posted 07-18-2019 19:15
    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?


  • 4.  RE: Return a value that's RED in color if it's greater then a certain number

    Posted 07-18-2019 19:16
    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.



  • 5.  RE: Return a value that's RED in color if it's greater then a certain number

    Posted 07-18-2019 19:27
    Followed exactly the formula but it prompts the same error. Am I missing something?


  • 6.  RE: Return a value that's RED in color if it's greater then a certain number

    Posted 07-18-2019 19:43
    Try this:

    If(Round(ToNumber(ToText(ToDate([Week 2 Date]) - ToDate([Week 1 Date]))),2) > 7,


  • 7.  RE: Return a value that's RED in color if it's greater then a certain number

    Posted 07-18-2019 19:50
    Got it. I appreciate your help so much. I hope there is a way to get this fixed.


  • 8.  RE: Return a value that's RED in color if it's greater then a certain number

    Posted 07-18-2019 20:09
    If(Round(ToNumber(ToText(ToDate([Week 2 Date]) - ToDate([Week 1 Date]))),2) > 7,


  • 9.  RE: Return a value that's RED in color if it's greater then a certain number

    Posted 07-18-2019 20:18
    No error but it still returns the number in decimal i.e. 8.0022154


  • 10.  RE: Return a value that's RED in color if it's greater then a certain number

    Posted 07-18-2019 20:24
    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".


  • 11.  RE: Return a value that's RED in color if it's greater then a certain number

    Posted 07-19-2019 15:41
    You're right. Rounding the variable is what fixed it. Thank you!