Jun 25 2021 03:45 AM
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
Jun 25 2021 10:34 AM - edited Apr 17 2023 05:50 AM
@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
Jun 28 2021 05:30 AM
@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?
Jun 28 2021 06:00 AM - edited Jun 28 2021 06:08 AM
@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.
Jun 28 2021 06:09 AM
Jun 28 2021 07:50 AM
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.
Sep 03 2021 02:51 AM
Sep 03 2021 04:02 AM
@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)
Sep 03 2021 06:25 AM
Sep 03 2021 08:01 AM - edited Sep 03 2021 08:22 AM
@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)
Jun 28 2021 07:50 AM
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.