SOLVED

Date Format to show out of date records

Copper Contributor

Hi guys, 

 

I have a list of supplier details where I want to highlight records  that have not been updated in the last year. I have tried (unsuccessfully as usual) to create a schema in the advanced column format page and can't seem to nail it it.

 

Any help as always is of great help :smile:

 

2 Replies
best response confirmed by nshaw75 (Copper Contributor)
Solution

@nshaw75 Do you want to highlight whole record/item or just column/cell?

 

As you mentioned "column format", try using SharePoint JSON column formatting based on date column like: 

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "[$DateColumn.displayValue]",
  "style": {
    "background-color": "=if(Number([$DateColumn])==0,'',if([$DateColumn] < addDays(@now, -365),'#ff0000','#0b6623'))",
    "color": "=if(Number([$DateColumn])==0,'','#FFFFFF')",
    "padding-left": "10px"
  }
}

 

Where [$DateColumn] is an internal name of your date column in SharePoint list in this format: [$InternalNameOfColumn]. You can get the internal name of your SharePoint list columns by following this article: How to find the Internal name of columns in SharePoint Online?

 

Output

ganeshsanap_0-1712316535991.png

 


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 Fantastic, thanks very much :smile:

1 best response

Accepted Solutions
best response confirmed by nshaw75 (Copper Contributor)
Solution

@nshaw75 Do you want to highlight whole record/item or just column/cell?

 

As you mentioned "column format", try using SharePoint JSON column formatting based on date column like: 

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "[$DateColumn.displayValue]",
  "style": {
    "background-color": "=if(Number([$DateColumn])==0,'',if([$DateColumn] < addDays(@now, -365),'#ff0000','#0b6623'))",
    "color": "=if(Number([$DateColumn])==0,'','#FFFFFF')",
    "padding-left": "10px"
  }
}

 

Where [$DateColumn] is an internal name of your date column in SharePoint list in this format: [$InternalNameOfColumn]. You can get the internal name of your SharePoint list columns by following this article: How to find the Internal name of columns in SharePoint Online?

 

Output

ganeshsanap_0-1712316535991.png

 


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.

View solution in original post