Forum Discussion

BrandonKyzer's avatar
BrandonKyzer
Qrew Member
6 years ago

Formula to Color A Variable Cell

Hello All,

I am trying to figure out how to color a cell based on a date range.
I currently have 

If(

[DATE]<[DATE <7days], "FF81A0",
[DATE]>[DATE <7days]and [DATE]<[DATE 8-14 Days], "FFFD81", "")

This functions just fine for coloring the entire row but I would like to color only the DATE cell based on the 0-7 days and 8-14 days criteria.

Thanks in advance

------------------------------
Brandon Kyzer
------------------------------
Formulas and functions
  • BrandonDrake's avatar
    BrandonDrake
    Qrew Cadet
    6 years ago
    Hello Brandon, 

    For this, you would need to create a new Formula - Rich Text Field.  This is the only way that you can color an individual cell, natively anyway.  Here is an example of one of mine.  I have a normal Date field call NOC Received and this formula checks the date and color codes it accordingly. The style tag colors the cell and the information displayed is between the <div> open and closed </div> tag, if you are not familiar with HTML markup.  

    If(IsNull([NOC Received]) and [NOF/NOC Not Required] = false,
    "<div style='background-color:rgb(255,255,0)'>" & "Still Needed" & "</div>",
    "<div style='background-color:lightgreen'>" & [NOC Received] & "</div>"
    )

    In addition, if you do not want to create a new field say, for example, this color coded field will only be used in one report, you can create the Formula- Rich text field in the report itself, like the following. The Report Formulas option is directly below Columns to Display when customizing the report. Also, just an FYI, you are limited to 3 formulas, if you need more than that you will need to create Rich Text formula fields



    ------------------------------
    Brandon Drake
    JHI Group
    Monroeville OH
    ------------------------------