Sharepoint List Column Formatting

[DevHuws]

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 expired date to be formatted in light red, not just the date field. So if either one of the date fields have expired the whole row will be formatted. There are several date fields and some do not contain any information therefore I need something to make sure nothing happens if the field does not contain a date. One of my date fields are called "ISO 45001 (OHAS 18001) so I am also unsure how to reference this, will it understand the spacing?

I have looked at many formuns but wasn't making any progress. Thank you to anyone who can help me out here.

Thanks,

Sion

[#2]

@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

[#3]

@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?

[#4]

@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.

[#5]

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!

[#6 · Accepted solution]

@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.

[#7]

@ganeshsanap,
Hi Ganesh,

I have a same scenario in Sharepoint where if column value is >0, then it need to be in red color. It's a calculated column like below
if([DueDate]-[ActivationDate]>28, "[Other column] * 1000", 0$). I don't want to do anything with this formula. Only color formatting need to be applied if column value >0, then red color.

Do you have any suggestions, please.


Regards
Janaa

[#8]

@J27 you just need to format the column as below, it doesn't matter that it's a calculated column.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=[$Percent]*100 + '%'",
  "style": {
    "color": "=if([$Percent] > 0 , '#80000d', '#3b3b3b'"
  }
}

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

[#9]

@RobElliott

Could you please let me know, do I need to change the $Percent to my column. Above code doesn't works for me when I pasted this in JSON Column Formatting area.

Thanks for your time.

Regards
Janaa

[#10]

@J27  yes Percent is the column in my list, you need to change it to whatever your column is called.

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)