Conditional formatting based on number of days to due date

Question

In SharePoint 0365, in the modern experience, I have a list of contracts that have an expiration date.

I would like to use conditional formatting to create a status column that shows me the contract is one of these:

1. Active, Green background - due date is more than 4 month from today.
2. Expiring, Orange background - due date is between 4 month from today and today.
3. Expired, Red background - due date is past one day.

I'm trying to get this for while now without success. I matched the formulas in Excell where they're working.

This is the code I'm trying:

{
  "elmType": "div",
  "style": {
    "background-color": "=if([$DueDate] > @now + 1036800000 ,'green', (if([$DueDate] - @now >= 0, '#ffa59b','red'))"
  },
  "children": [
    {
      "elmType": "span",
      "txtContent": "=if([$DueDate] > @now + 1036800000 ,'Current', (if([$DueDate] >= @now - 10368000000, 'Expiring','Expired'))",
      "style": {
        "color": "white"
      }
    }
  ]
}

And this is the result I get:

Here is the working formula tested in Excel, I'm trying to convert in JSON : =IF(B16>TODAY()+120,"green",IF(B16-TODAY()>=0,"orange","red")) where B16 is the cell containing the contract due date.

Answer 1

Please try the below and let me know

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if([$DueDate]>(@now +10368000000), 'Green', if([$DueDate]>@now && [$DueDate]< (@now +10368000000), 'Expiring', 'Expired'))",
  "style": {
    "background-color": "=if([$DueDate]>(@now +10368000000), 'Green', if([$DueDate]>@now && [$DueDate]< (@now +10368000000), 'Orange', 'Red'))",
    "color": "black",
    "font-size": "1.5em",
    "justify-content": "center"
  }
}

The explanation below may help others understand the if conditional formula better.

The formula is an Excel-style conditional expression (source) which is comprised of:

1. logical_test: the condition that you want to check

2. value_if_true: the value to return if the condition is True

3. value_if_false: the value to return if the condition is False

In regards to the usage of @now and the large numbers, @now is a special value which resolves to the current date/time and is evaluated when the user loads the list view (source).

You can add milliseconds to any date and the result will be a new date.

For example, to add a day to a date, you'd add (24*60*60*1000 = 86,400,000) milliseconds (source).

10,368,000,000 milliseconds is equal to 120 days or ~ 4 months.

Condition 1

if([$DueDate]>(@now +10368000000), 'Green'...

If DueDate is more than 120 days in the future, set the background color to Green.

Condition 2

if([$DueDate]>@now && [$DueDate]< (@now +10368000000), 'Orange', 'Red'

If DueDate is between tomorrow and 120 days in the future, set the background color to Orange.

Otherwise (ie if DueDate is today or in the past), set the background color to Red.