Dec 16 2019 11:14 AM - edited Dec 16 2019 11:15 AM
I have a list with two dates one labeled "Effective Date" and one labeled "Exp Date". Both columns are using the Date & Time column type but set to Date Only. What I'm trying to figure out is how to make the "Exp Date" Green if a date is entered and it is not within 30 days of expiring. If the "Exp Date" is 30 days or less of expiring make it Red.
Dec 16 2019 11:55 AM
@spinman There's an answer from @Matti Paukkonen to your previously asked question https://techcommunity.microsoft.com/t5/SharePoint/JSON-Formula-Help/m-p/977223#M35868 - did that not work for you?
There's great documentation here https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting
Dec 17 2019 06:36 AM
@Robin Nilsson I tried to use the JSON provided but SharePoint gives me an error that says "Please enter valid column-formatting JSON.
Dec 17 2019 08:15 AM
@spinman There's an error in the $schema code. Change this:
"$schema": "<a href="<a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a>" target="_blank"><a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a</a>>",
to this:
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
Dec 17 2019 08:49 AM
@Robin Nilsson Thanks. So I made that change and it let me save it. However, the Exp Date doesn't show up unless you go into the record and the field is not colored red or green. Here is what I put in the JSON format field of the Exp Date field. Am I still missing something?
{
"$schema": "<a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a>",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": {
"operator": "?",
"operands": [
"=length([$EffectiveDate]) > 0",
{
"operator": "?",
"operands": [
{
"operator": "<",
"operands": [
"[$EffectiveDate]",
"[$ExpDate]"
]
},
"",
{
"operator": "?",
"operands": [
{
"operator": "<",
"operands": [
"[$EffectiveDate]",
{
"operator": "+",
"operands": [
"[$ExpDate]",
2592000000
]
}
]
},
"green",
"red"
]
},
""
]
},
""
]
}
}
}
Dec 17 2019 09:10 AM
SolutionDec 17 2019 09:10 AM
Solution