Conditional formatting of column in SharePoint list using value from another column

[magnusjonasson]

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!

[#2]

@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

[#3 · Accepted solution]

@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

[#4]

Thank you very much, that worked like a charm!

[#5]

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.

[#6]

@magnusjonasson You have to use the column name in this format only [$Date]. But, instead of Date, use the correct internal name of your date column.

Follow this article: Find the internal name of SharePoint column 

---

Please consider giving a Like if my post helped you in any way. For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs

[#7]

My bad, I missed a square bracket. Thanks!