Expand all | Collapse all

Formula to Color A Variable Cell

  • 1.  Formula to Color A Variable Cell

    Posted 29 days ago
    Hello All,

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


    [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

  • 2.  RE: Formula to Color A Variable Cell

    Posted 28 days ago
    Edited by Brandon Drake 28 days 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