Aug 11 2022 04:42 AM
Hello,
I wish to format a date column named Last_x0020_review_x0020_date with conditional formatting to display either the column or ideally the entire row in "Red" with white text.
For example if Last Review Date = 10/08/2021 (UK date format), it should format as above if today is equal to or more than 365 days (1 year).
However if the date is 11/08/2022 then it should just display normally (white background and black text).
Any help would be really appreciated!
Thanks 🙂
Aug 11 2022 06:10 AM - edited Aug 11 2022 06:20 AM
@Chris Cundy You can color entire row using view formatting. Use below JSON in "Format current view" option:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
"additionalRowClass": "=if([$Last_x0020_review_x0020_date] + 31536000000 <= @now , 'ms-bgColor-red ms-fontColor-white', '')"
}
Output:
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
Aug 11 2022 06:18 AM
This works great.
One thing, can we highlight rows where the Last Review Date is empty? in a different colour such as yellow?
Aug 11 2022 06:21 AM
For coloring only last review date column, you can use column formatting. use this JSON in "Format this column" option for date column:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"attributes": {
"class": "=if(@currentField + 31536000000 <= @now , 'ms-bgColor-red ms-fontColor-white', '')"
}
}
Output:
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
Aug 11 2022 06:26 AM
Both the above options for row and column formatting work well - are you able to help with my second query?
Can we highlight rows where the Last Review Date is empty? in a different colour such as yellow?
and can this be incorporated to the first solution?
Aug 11 2022 06:32 AM
Solution@Chris Cundy Yes, you have to add one more condition in expression like:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
"additionalRowClass": "=if(if(Number([$LastReviewDate]) == 0, 'ms-bgColor-yellowLight', [$LastReviewDate] + 31536000000 <= @now , 'ms-bgColor-red ms-fontColor-white', ''))"
}
Reference: SharePoint JSON formatting: Check if date & time column is blank/empty
Output:
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
Aug 11 2022 06:44 AM
Aug 11 2022 06:49 AM
@Chris Cundy You're welcome, glad it worked for you!
Please consider giving Likes if my posts helped you in any way.
Aug 11 2022 06:32 AM
Solution@Chris Cundy Yes, you have to add one more condition in expression like:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
"additionalRowClass": "=if(if(Number([$LastReviewDate]) == 0, 'ms-bgColor-yellowLight', [$LastReviewDate] + 31536000000 <= @now , 'ms-bgColor-red ms-fontColor-white', ''))"
}
Reference: SharePoint JSON formatting: Check if date & time column is blank/empty
Output:
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.