Forum Discussion

Chris Cundy's avatar
Chris Cundy
Brass Contributor
Aug 11, 2022

Column formatting - Last Review Date + 365 days

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 examp...
SharePoint Online
  • ganeshsanap's avatar
    ganeshsanap
    Aug 11, 2022

    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.

ganeshsanap's avatar
ganeshsanap
MVP
Aug 11, 2022

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.

  • Chris Cundy's avatar
    Chris Cundy
    Brass Contributor
    Aug 11, 2022

    ganeshsanap 

     

    This works great.

     

    One thing, can we highlight rows where the Last Review Date is empty? in a different colour such as yellow?

    • ganeshsanap's avatar
      ganeshsanap
      MVP
      Aug 11, 2022

      Chris Cundy 

      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.

      • ganeshsanap's avatar
        ganeshsanap
        MVP
        Aug 11, 2022

        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.

Resources