Conditional Formatting Column using JSON

Question

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.

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!

Answer 1

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

Answer 2

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