0

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.

0

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

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. – Matiur Rahman 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.