color formatting a date column

Question

I'm very new at list formatting. I've created a new list and are trying to get the columns to turn red/green/yellow depending on the date that you put in to the date-column.

I want the column to turn:

• red after 183 days
• yellow after 120 days
• green up to 119 days
• No color if no date is selected at all.

So far I've been successful in getting columns to turn green when they should. The rest of the columns is automatically turning red. I guess that I've been successful in getting columns to turn red too, but the issue is that empty column is automatically turning red too. The yellow color doesn't work at all.

I've read that if(Number) == 0,'' can be used for blank values, but I can't get it to work.

I've been using this JSON:

{
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if(@currentField >= @now - 10281600000 ,'lightgreen', if(@currentField >= @now - 10368000000 ,'yellow', (if(@currentField >= @now - 15811200000, '#ffa59b','pink'))"
}
}

Anyone that can help me get this to work as I want?

Answer 1

Try using below JSON code:

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "@currentField",
    "style": {
        "background-color": "=if(Number(@currentField) == 0, '', if(@currentField >= @now - 10281600000, 'lightgreen', if(@currentField >= @now - 10368000000, 'yellow', if(@currentField >= @now - 15811200000, 'red', ''))))"
    }
}

You can find other ways to check if date & time column is empty or not at: SharePoint JSON formatting: Check if date & time column is blank/empty

Microsoft documentation: Formatting items based on arbitrary dates (advanced)

Similar question on Forum: SharePoint color formating