SharePoint list View Formatting: Highlighting items older than a specific number of days

The out-of-the-box rule configurations for SharePoint lists/Microsoft lists give us several options to format views without touching the view JSON, but we still don’t have the option of using calculations using relative dates:

Options to visually set up view formatting

For this, we still need to create a custom JSON view, but it is pretty straightforward anyway.

The logic behind

To manage date calculations we need to calculate anything based on milliseconds.

For example, to compare a date to 30 days ago:

1000 milliseconds * 60 seconds * 60 minutes * 24 hours * 30 days = 2592000000 milliseconds

So if we want to compare if a date is older than 30 days ago, we would need to compare if the date is older than Now – 2592000000 milliseconds. There is no operator for ‘today‘ but for date only fields, basing the calculations on ‘now‘ value will do the same job.

The JSON example below does the trick by using a formula to compare a date field (internal name: DateReported) to the current date minus 2592000000 milliseconds.

Also validates if no date was provided, if no date was provided in the field, nothing happens.

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
    "additionalRowClass": "=if(toString([$DateReported]) == '', '', if([$DateReported] <= (@now - 2592000000), 'sp-css-backgroundColor-blockingBackground40',''))"
  }

Results

On the example above we are highlighting the rows using the CSS class sp-css-backgroundColor-blockingBackground40 to highlight rows in red as in the image below:

In case you want a different days range, just use the above explained logic and change the value 2592000000 above to the result of your calculations. Also in this example we are using our field Date reported which has an internal name of DateReported, replace this value to your field internal name for the view formatting to work properly.

For a list of available SharePoint reusable CSS classes that can be used with list formatting, check a post by Denis Molodtsov: https://zerg00s.github.io/sp-modern-classes/

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *