Three-Color Coding based on Date Ranges in SharePoint Online

Question

Follow-up to 594140

ORIGINAL CODE:

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if(@currentField <= @now + 864000000, 'red', if(@currentField <= @now + 7776000000, 'yellow', ''))"
}
}

My boss is, of course, never satisfied with a solution that works. Now she wants three colors for three conditions:

• Next Review <90 days from 'now' = yellow

• Next Review <10 days from 'now' = orange

• expired (Next Review is past 'now') = red

You'd think this would be simple. You'd think it would be a matter of copy/pasting a third condition into the "background color" section and changing the time and color values, right? No such luck.

Here's what I tried:

NEW CODE:

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if(@currentField <= @now, 'red', =if(@currentField <= @now + 864000000, 'orange', if(@currentField <= @now + 7776000000, 'yellow', ''))"
}
}

Everything looks right. I even ran it through an online JSON code validator to make sure no formatting got messed up. But now the column is utterly blank--no color, and no values either!

This makes no sense. There's no conceivable way that background-color instructions, even bad ones, could erase the contents of the cells! What's going on here?

Answer 1

Looks like there is syntax error in your conditions against background-color. Try using below JSON code, it should work for you:

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "@currentField",
    "style": {
       "background-color": "=if(@currentField <= @now, 'red', if(@currentField <= @now + 864000000, 'orange', if(@currentField <= @now + 7776000000, 'yellow', '')))"
    }
}

official documentation: Use column formatting to customize SharePoint