SharePoint List-Custom Conditional Formatting for "Modified" Column

Question

SharePoint List-Custom Conditional Formatting for "Modified" Column

Harry N Nomikos 1,336

Hi Team

I was wondering if you could please help with how I can apply Conditional Formatting for the "Modified" column on a SharePoint list under the Modern view.

I'd like it to reflect the below conditions;

If a list entry hasn't been modified in the last 2 weeks from today>highlight orange
If a list entry hasn't been modified in the last month from today> highlight red

I'm very limited with the type of access I have, so JSON formula would be best or if it can be done with the current conditional format fields in some way, that'd be great :)

Cheers,
Harry

1 answer

Your answer

Answer 1

RaytheonXie_MSFT 40,471 Microsoft External Staff

Hi @Harry N Nomikos ,
Since we need to use JSON formula, column formatting is our best choice.
Please refer to the following code and link:

{  
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",  
  "elmType": "div",  
  "debugMode": true,  
  "txtContent": "@currentField",  
  "style": {  
    "background-color": "=if(floor((Number(@now)-Number([$Modified]))/(1000*60*60*24)) >= -1 && floor((Number(@now)-Number([$Modified]))/(1000*60*60*24)) < 30, '#ffbf00', if(floor((Number(@now)-Number([$Modified]))/(1000*60*60*24)) >= 30, '#ff0000', '')"  
 }  
}

https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Harry N Nomikos 1,336 Reputation points

2021-09-06T10:04:36.953+00:00

Hi @RaytheonXie_MSFT

Thanks for the response.

I've copied the JSON formula you provided, yet it has returned all entries to highlight as Orange under "Modified"

Could you please provide the JSON coding for the below under the "Modified column;
If a list entry hasn't been modified in the last 2 weeks from today>highlight orange
If a list entry hasn't been modified in the last month from today> highlight red

Cheers,
Harry

RaytheonXie_MSFT 40,471 Microsoft External Staff

Hi @Harry N Nomikos ,
I mistook the query conditions and any items under 30 days will be marked with orange.Do you have the items over 30 days marked with red?I have edited the code,try following please

     {  
       "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",  
       "elmType": "div",  
       "debugMode": true,  
       "txtContent": "@currentField",  
       "style": {  
         "background-color": "=if(floor((Number(@now)-Number([$Modified]))/(1000*60*60*24)) >= 14 && floor((Number(@now)-Number([$Modified]))/(1000*60*60*24)) < 30, '#ffbf00', if(floor((Number(@now)-Number([$Modified]))/(1000*60*60*24)) >= 30, '#ff0000', '')"  
       }  
 }

The result should be like this pic

Harry N Nomikos 1,336 Reputation points

2021-09-07T06:14:29.95+00:00

Hi @RaytheonXie_MSFT

The updated JSON formula seems to be working. I don't have any items that haven't been amended in over 30 days yet, however this will change from next week and I'll monitor if the conditional formatting picks it up.

Thanks so much for your help on this-it's greatly appreciated :)

Cheers,
Harry
RaytheonXie_MSFT 40,471 Reputation points Microsoft External Staff

2021-09-13T01:31:43.293+00:00

Hi @Harry N Nomikos ,
would you please provide us with an update on the status of your issue?
RaytheonXie_MSFT 40,471 Reputation points Microsoft External Staff

2021-09-23T01:29:09.1+00:00

Hi @Harry N Nomikos ,
I am checking to see if the problem has been resolved.
RaytheonXie_MSFT 40,471 Reputation points Microsoft External Staff

2021-10-04T08:39:53.007+00:00

Hi @Harry N Nomikos ,
If there's anything you'd like to know, don't hesitate to ask.

Share via

SharePoint List-Custom Conditional Formatting for "Modified" Column

1 answer

Your answer