1

I'm trying to format a column to conditionally color based on expiration date of a license, similar to the question here: Conditional formatting based on date to expire

I've tried to edit the JSON code from this answer for my case, where if expiration date is more than 30 days away from now, status would be current and the background color would be green. If expiration date is between 0 and 30 days from now, status would be expiring and color would be yellow. And lastly, if expiration date has passed, status is expired and color would be red.

I've gotten the code to this point:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "background-color": "=if(@$Expiration_x0020_Date <= @now, 'red', (if(@$Expiration_x0020_Date <= @now + 2592000000, '#FFFF00','green'))"
  },
  "children": [
    {
      "elmType": "span",
      "txtContent": "=if(@$Expiration_x0020_Date >= @now + 2592000000,'Current', (if(@$Expiration_x0020_Date >= @now, 'Expiring','Expired'))",
      "style": {
        "color": "black"
      }
    }
  ]
}  

But the formatting is going to the else case at the end, so everything is green and says expired, even though that's not correct.

2 rows of sample data

Can anyone tell me what I'm doing wrong and how I'd get it to correctly display the first row as current/green, and the 2nd row as expired/red?

Thank you!

2 Answers 2

0

Use below JSON in "Expiration Status" column:

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "style": {
        "background-color": "=if([$Expiration_x0020_Date] < @now, 'red', if(@now <= [$Expiration_x0020_Date] && @now >= addDays([$Expiration_x0020_Date], -30), 'yellow', if(@now < addDays([$Expiration_x0020_Date], -30), 'green', '')))"
    },
    "children": [
        {
            "elmType": "span",
            "txtContent": "=if([$Expiration_x0020_Date] < @now, 'Expired', if(@now <= [$Expiration_x0020_Date] && @now >= addDays([$Expiration_x0020_Date], -30), 'Expiring', if(@now < addDays([$Expiration_x0020_Date], -30), 'Current', '')))",
            "style": {
                "color": "black"
            }
        }
    ]
} 

Documentation: JSON column formatting


Update from comments:

You have to use the correct "internal name" of columns in JSON formatting. Follow this article for same: Find the internal name of SharePoint column

4
  • This ends with the Expiration Status being blank for me. I'm starting to think maybe I mis-formatted Expiration Date and that is what is causing me issues. But thank you! I'll keep trying to figure out what's wrong with my list.
    – Jasmine B
    Oct 4, 2022 at 14:06
  • You have to use the correct internal name of columns in JSON formatting. check updated answer. Oct 4, 2022 at 14:11
  • 1
    Thank you so much! I started from a template and never would have figured out that internally it was the original name. It's working now!
    – Jasmine B
    Oct 4, 2022 at 14:34
  • Great, glad it worked for you! Oct 4, 2022 at 15:15
0

You could use following codes in the column.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "background-color": "=if([$ExpirationDate] < @now,'red', if(([$ExpirationDate] >= @now) && ([$ExpirationDate] < @now + 2592000000), 'yellow','green'))"
  },
  "children": [
    {
      "elmType": "span",
      "txtContent": "=if([$ExpirationDate] < @now,'Expired', if(([$ExpirationDate] >= @now) && ([$ExpirationDate] <@now + 2592000000), 'Expiring','Current'))",
      "style": {
        "color": "black"
      }
    }
  ]
} 

enter image description here

1
  • When I copy paste this code in, I'm still getting both rows showing expired, but I can see from your screenshot that this should work. So strange, I'm not sure what's wrong with my Sharepoint. But thank you!
    – Jasmine B
    Oct 4, 2022 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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