I'm totally new to JSON formatting and need your help.
The SP list requires the following:
List formatting to show colors (green, yellow, red) if the Request Completed Date is empty as well as if the Request Date exceeds 1 day, 7 days, and 8 days.
Essentially, green = 1 day, 7 days = yellow, and more than 8 days = red.
Is this possible?
You can use JSON column formatting for this requirements. Use JSON like (considering today's date is 8/24/2022):
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"attributes": {
"class": "=if(Number(@currentField) == 0 , '', if(@currentField + 691200000 < @now , 'ms-bgColor-red ms-fontColor-white', if(@currentField + 604800000 < @now , 'ms-bgColor-yellow ms-fontColor-white', if(@currentField + 86400000 < @now , 'ms-bgColor-green ms-fontColor-white', ''))))"
}
}
Output:
OR:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"attributes": {
"class": "=if(Number(@currentField) == 0 , '', if(@currentField + 604800000 < @now , 'ms-bgColor-red ms-fontColor-white', if(@currentField + 518400000 < @now , 'ms-bgColor-yellow ms-fontColor-white', if(@currentField < @now , 'ms-bgColor-green ms-fontColor-white', ''))))"
}
}
Output:
References:
You can enhance the JSON as per your further requirements, you can check if date column is empty or not using expression Number(@currentField) == 0.
And you have to convert number of days to milliseconds to compare with current date (@now).
Example: 1 day = 1 x 24 x 60 x 60 x 1000 = 86400000
Update from comments:
Try using this JSON:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"attributes": {
"class": "=if(Number(@currentField) == 0 , if([$RequestDate] + 604800000 < @now , 'ms-bgColor-red ms-fontColor-white', if([$RequestDate] + 518400000 < @now , 'ms-bgColor-yellow ms-fontColor-white', if([$RequestDate] < @now , 'ms-bgColor-green ms-fontColor-white', ''))), '')"
}
}
Use correct internal name of Request Date column in place of [$RequestDate] in JSON expression in format: [$InternalNameOfColumn]. You can get the internal name of your column by following this article: How to find the Internal name of columns in SharePoint Online?
You have to apply this JSON formatting for "Request Completed Date" column.
=if(Number(@currentField) == 0 , if([$RequestDate] + 604800000 < @now , 'ms-bgColor-red ms-fontColor-white', if([$RequestDate] + 518400000 < @now , 'ms-bgColor-yellow ms-fontColor-white', if([$RequestDate] < @now , 'ms-bgColor-green ms-fontColor-white', ''))), ''). Use correct internal name of Request Date column in place of [$RequestDate] in JSON expression.
As a supplement, you could use the addDays() function to add days directly without counting seconds. It can also be compared to other columns, not just the current time. For example below:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"color": "=if(@currentField > addDays(Date([$A]), 1), '#00ff00', '')"
}
}
