Forum Discussion
Solved
Conditional formatting of column in SharePoint list using value from another column
I'm trying to color the background of a date column in SharePoint using conditional formatting and json. I want the column background to turn red if the date in the column is before today minus X number of days. The number of days I want to subtract from today can be different for each row, and is therefor specified in its own column:
So far I've managed to find out how to write the json using a constant value of number of days, but now I need it to be dynamic, using data from the column 'Mål antal bankdagar' above.
My current json code looks like this:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=if(@currentField <= @now - 432000000, 'red', if(@currentField >= @now - 432000000, 'green', ''))"
}
}
Also, it would be neat if I could swap out the red and green color to SharePoint's default color scheme, with both red (coral?) background color and darker red text and the same for green.
Any help would be much appreciated, thanks!
ganeshsanap For example, I have this JSON and list setup in our SharePoint site:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "style": { "padding-left": "10px" }, "attributes": { "class": "=if(@currentField <= addDays(@now, -1 * [$NumberCol]), 'sp-css-backgroundColor-BgCoral sp-css-color-DarkRedText', if(@currentField >= addDays(@now, -1 * [$NumberCol]), 'ms-bgColor-greenLight ms-fontColor-greenDark', ''))" } }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.
For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs
magnusjonasson You can use JSON like:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "style": { "background-color": "=if(@currentField <= addDays(@now, -1 * [$Målantalbankdagar]), 'red', if(@currentField >= addDays(@now, -1 * [$Målantalbankdagar]), 'green', ''))" } }Use correct internal name of your column in place of Målantalbankdagar in [$Målantalbankdagar].
Refer this article: Find the internal name of SharePoint column
Also, you can use classes as per your requirements. Check this list of CSS classes by Denis Molodtsov: SharePoint Online CSS Classes
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.
For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs
ganeshsanap For example, I have this JSON and list setup in our SharePoint site:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "style": { "padding-left": "10px" }, "attributes": { "class": "=if(@currentField <= addDays(@now, -1 * [$NumberCol]), 'sp-css-backgroundColor-BgCoral sp-css-color-DarkRedText', if(@currentField >= addDays(@now, -1 * [$NumberCol]), 'ms-bgColor-greenLight ms-fontColor-greenDark', ''))" } }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.
For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs
- I seem to have run into another issue with this.
If I want to reference another column called 'Date' instead of 'now' in the JSON above, to make it look at a given date in that column when it decides on what color to use on currentField, how do I write that? I tried swapping out '@now' against [$Date] but then nothing is colored at all.
Any help would be appreciated.
