1

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:

SharePoint library screen capture

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.

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"
  }
}

enter image description here

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

enter image description here

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.

4
  • Hello, Thank you. For any reason my Sharepoint Document Library, I was working on, got corrupted. Whatever formatting I was trying to apply wouldn't work, including yours. I created a new library from scratch and now it works this time, all the JSON, including mine (which does contain some errors though). Your input helped me a lot.
    – Luc
    Oct 26 '20 at 14:59
  • JSON formatting shouldn't corrupt the library as you can remove it anytime. Is your library not accessible even after removing the JSON format? This might be a different issue here. You can post the issue to the forum as a separate issue. Oct 26 '20 at 15:04
  • Well it's now too late as I discarded the Document Library. There wasn't any error apart that the formatting would not show anything. The column was remaining white.
    – Luc
    Oct 26 '20 at 16:55
  • What I'm trying to achieve is a full status indicator by merging 2 JSON codes. I must admit my knowledge and experience with JSON is weak. I thought I could go around with Excel knowledge but, no. I will post a new question.
    – Luc
    Oct 26 '20 at 16:59

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.