Forum Discussion

DevHuws's avatar
DevHuws
Copper Contributor
Jun 25, 2021
Solved

Sharepoint List Column Formatting

Hello everyone. I have a Sharepoint Online list with various different fields holding contract information. Each row contains several date fields formatted dd/mm/yyyy. I want every row that has an ex...
Lists
SharePoint Online
  • ganeshsanap's avatar
    ganeshsanap
    Jun 28, 2021

    DevHuws Not sure if I understood it correctly. But, either of below JSON codes should work for you: 

     

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
   "additionalRowClass": "=if(([$DueDate] && [$DueDate] <= @now) || ([$EndDate] && [$EndDate] <= @now) || ([$AnyOtherDateColumn] && [$AnyOtherDateColumn] <= @now), 'sp-field-severity--severeWarning', '')"
}

     

    OR 

     

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
   "additionalRowClass": "=if((Number([$DueDate]) !=0 && [$DueDate] <= @now) || (Number([$EndDate]) !=0 && [$EndDate] <= @now) || (Number([$AnyOtherDateColumn]) !=0 && [$AnyOtherDateColumn] <= @now), 'sp-field-severity--severeWarning', '')"
}

     

    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
Jun 25, 2021

DevHuws You need to use JSON view formatting for your requirements. Use JSON in below format: 

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
   "additionalRowClass": "=if([$DueDate] <= @now || [$EndDate] <= @now || [$AnyOtherDateColumn] <= @now, 'sp-field-severity--severeWarning', '')"
}

 

Where DueDate, EndDate, AnyOtherDateColumn are internal names of SharePoint date columns. As JSON formatting supports internal names only, you have to find out the internal name of your "ISO 45001 (OHAS 18001)" column using above link. Follow this article to get the internal name of your SharePoint columns: How to find the Internal name of columns in SharePoint Online? 

 

Also, you can add multiple date conditions using OR ( || ) operator in single if expressions as shown above.

Microsoft documentation: Use view formatting to customize SharePoint 

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

DevHuws's avatar
DevHuws
Copper Contributor
Jun 28, 2021

ganeshsanap Hi thanks for the reply, this works well with formatting based on multiple columns, however I have date fields which do not contain dates and these are being formatted as well. Is there anyway to include if the date field = 0, it will ignore it? While still formatting all that have late dates?

  • ganeshsanap's avatar
    ganeshsanap
    MVP
    Jun 28, 2021

    DevHuws Great, glad it worked for you.

     

    For your another question about blank date field:

    This is not possible using the view formatting code I provided as it colors the whole row. However, you can override the view formatting by applying column formatting for specific date columns. You can use the JSON similar to this:

     

    {
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "@currentField",
    "style": {
        "background-color": "=if(Number(@currentField) == 0, 'white', '#FDE7E9')"
    }
}

     

    You can change the background-color in above JSON as per your requirements. The output of above JSON will look something like below: 

    More ways to check if date field is blank: SharePoint JSON formatting: Check if date & time column is blank/empty 

    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.

    • DevHuws's avatar
      DevHuws
      Copper Contributor
      Jun 28, 2021
      Hi thanks for the quick reply. Sorry not sure if I explained properly, I don't mind the whole row being coloured I just dont want the row to be coloured if for example start date was empty, and only colour the row if it includes a date which has expired. If this is not possible I will use the example you have mentioned with column formatting. Thank you for your help!
      • ganeshsanap's avatar
        ganeshsanap
        MVP
        Jun 28, 2021

        DevHuws Not sure if I understood it correctly. But, either of below JSON codes should work for you: 

         

        {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
   "additionalRowClass": "=if(([$DueDate] && [$DueDate] <= @now) || ([$EndDate] && [$EndDate] <= @now) || ([$AnyOtherDateColumn] && [$AnyOtherDateColumn] <= @now), 'sp-field-severity--severeWarning', '')"
}

         

        OR 

         

        {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
   "additionalRowClass": "=if((Number([$DueDate]) !=0 && [$DueDate] <= @now) || (Number([$EndDate]) !=0 && [$EndDate] <= @now) || (Number([$AnyOtherDateColumn]) !=0 && [$AnyOtherDateColumn] <= @now), 'sp-field-severity--severeWarning', '')"
}

         

        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.