1

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?

2
  • I didn't understood all your conditions. But check below answer to know how you can add if condition to check if date is blank. when you want to color it red? 183 days after date field or 183 days before today's (current) date? – Ganesh Sanap Nov 5 '20 at 15:51
  • When using that JSON code the columns that doesn't have a date is blank, so that seems to work! But the columns that should be red/yellow/green i also blank.. I want the columns to change to red 183 days after date field. If I put the date 2020-01-01 in my date column, I want the column to be green and stay green until 120 days later when it instead will change to yellow. After 183 days, the column should turn red and stay red until I update the date column to today's date. – hs0n Nov 11 '20 at 16:23
0

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', ''))))"
    }
}

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

Similar question on Forum: SharePoint color formating

1
  • Hi @hs0n, Does this answers your question? If Yes, Please Upvote(^) and accept as an Answer as it will help others with same problem in future. It also removes this question from "Unanswered questions" list. If you have any doubt please ask here. – Ganesh Sanap Nov 24 '20 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.