I have a Microsoft List (SharePoint List) and have a date field. I want to have a conditional format of the color based on today, i.e. if the date is in the past, grey, if the date is between today and a month from now, Amber and if the date is more than a month in the future, green.
I understand I will need JSON but I haven't used that before.
Has anyone done this before and has the JSON for it?
You have to use the JSON column formatting for this. Use below JSON for your date column:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"style": {
"background-color": "=if(Number(@currentField) == 0, '', if(@currentField < @now, '#808080', if(@currentField >= @now && @currentField <= addDays(@now, 30), '#FFBF00', if(@currentField > addDays(@now, 30), '#00FF00', ''))))"
},
"children": [
{
"elmType": "span",
"txtContent": "@currentField",
"style": {
"color": "black"
}
}
]
}
Output:
You can apply additional formatting as per your requirements. If you want to change color of text, use color property instead of background-color in above JSON.
For more information, follow Microsoft documentation: Use column formatting to customize SharePoint
Update from comments:
You can check if date column is empty or not using Number() operator. Use below expression for background-color for your requirements:
=if(Number(@currentField) == 0, '', if(@currentField < @now, '#808080', if(@currentField >= @now && @currentField <= addDays(@now, 30), '#FFBF00', if(@currentField > addDays(@now, 30), '#00FF00', ''))))
Source: SharePoint JSON formatting: Check if date & time column is blank/empty
