Forum Discussion

Missile64's avatar
Missile64
Copper Contributor
Nov 21, 2022

Change border colour of Text field based on Date Field - Calculated Field

I have a SharePoint list that contains a list of documents that require periodic reviews. I would like to have a Calculated field that adds a red border on the Title field when the Next Review Date field is < Today, and Amber if the Review Date field is within 1 Month from Today.

JS is not allowed hence the need for a Calculated field.

Title - Text FieldNext Review Date - Date FieldTodays Date 22-11-2022
Document 125-10-2022Red border because it is past Review Date
Document 212-12-2022Amber border because it is within 1 month of Review Date
Document 320-02-2023Nothing as it is over 1 month from Review Date
Document 421-11-2022Red border because it is past Review Date

 

Thanks in advance
Pete

2010
sharepoint
  • ganeshsanap's avatar
    ganeshsanap
    MVP
    Nov 22, 2022

    Missile64 If you are using modern experience in SharePoint online, you can use JSON column formatting for your title column like: 

     

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "padding-left": "5px",
    "border": "2px solid",
    "border-color": "=if([$NextReviewDate] < @now, 'red', if([$NextReviewDate] >= @now && [$NextReviewDate] <= addDays(@now, 30), 'orange', ''))"
  },
  "children": [
    {
      "elmType": "span",
      "txtContent": "@currentField",
      "style": {
        "color": "black"
      }
    }
  ]
}

     

    Output: 

    Where NextReviewDate is the internal name of your date column. Follow this article to get the internal name of your SharePoint column: How to find the Internal name of columns in SharePoint Online? 

    Documentation: SharePoint column-formatting 

    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

Resources