Sharepoint List - JSON Conditional Formatting

[acidrain]

Hoping someone can help.

I've setup a list of certificate expirations for all our employees but am having an issue setting up the conditional format to visually indicate the following:

Expiration date < today = Highlight Red
Expiration date <= today + 60 days = Highlight Orange
Expiration date > today + 60days = Highlight Green

The default formatting does not allow for setting custom rules on the dates. You can either set today or a specific date but not something like "within 60 days from today".

The only way to do this is via Advanced formatting using JSON. After doing some google searching, I found an example but when using it, sharepoint throws an error "Enter valid column-formatting JSON".

Current code is

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if([$Cert] <= @now + 7776000000, '#ff8000',if([$Cert] < @now, '#FF0000',if([$Cert] > @now + 7776000000, '#008000','')))"
    "padding-left": "10px"
  }
}

If someone is able to point out the problem, or perhaps point me to a better example, that would be greatly appreciated.

Thanks

 

[swift412]

Try adding a comma at the end of the line with background-color, like so:

{
	"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
	"elmType": "div",
	"txtContent": "@currentField",
	"style": {
		"background-color": "=if([$Cert] <= @now + 7776000000, '#ff8000',if([$Cert] < @now, '#FF0000',if([$Cert] > @now + 7776000000, '#008000','')))",
		"padding-left": "10px"
	}
}

 

[acidrain]

Ok, so found I was missing a comma, so the code is partially working.

Problem now is:
Those that aren't relevant (i.e. don't have that particular cert are being flagged by being highlighted, and then those who are expiring within the next 60 days are not hihglighted orange.

 

[swift412]

Looks like you need to check if the column is empty first, but for some reason [$Cert] == '' doesn't work with date columns, but checking [$Cert] < 1 works.

{
	"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
	"elmType": "div",
	"txtContent": "@currentField",
	"style": {
		"background-color": "=if(@currentField < 1, '', if(@currentField < @now, '#FF0000', if(@currentField < @now + (60*24*60*60*1000), '#FF8000', '#00FF00')))",
		"padding-left": "10px"
	}
}

It's Friday afternoon and my brain is tired, but it should work as:
if empty -> don't format background
if in the past -> red
if in the next 60 days -> orange
otherwise it has to be in the future, so -> green

 

[acidrain]

Somehow it's formatting empty cells but I got it all working now.

It's a dirty method for excluding empty cells. I just set expiry as today - 365 days. That way those without certs will obviously return true but the period is long enough that those that expire will never fall true as a year is enough time to renew the license.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if([$Cert] < @now - 31104000000, '#ffffff',if([$Cert] < @now, '#FF0000',if([$Cert] > @now + 7776000000, '#9BE094',if([$Cert] < @now + 7776000000, '#ff8000','')))",
    "padding-left": "10px"
  }
}

 

[swift412]

Yeah it's weird, I'd have thought a simple == '' or == 0 would have worked but it didn't.

You've probably stumbled across it while googling, but https://pnp.github.io/sp-dev-list-formatting/ ( https://github.com/pnp/sp-dev-list-formatting/tree/master ) have a lot of great examples for SharePoint formatting stuff.