Forum Discussion
Formatting Dates in SharePoint Online list
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.
- The logic in the JSON doesn't seem to match what you asked for - what it is doing is showing Effective Date green if the Effective Date is less than ExpDate+1month, red if it's after that.
You seem to want Effective Date green if the ExpDate is less than a month away, and red if it's after the ExpDate... That's more complex logic. I'm not that great in JSON, but in pseudo logic it would be something like
If EffectiveDate < ExpDate and EffectiveDate >= ExpDate-1month show green
If Effective Date > ExpDate and EffectiveDate <= ExpDate+1month show red
5 Replies
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
Robin Nilsson I tried to use the JSON provided but SharePoint gives me an error that says "Please enter valid column-formatting JSON.
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",
