SOLVED

Conditional Formatting Files Older Than Certain number of days or years

Iron Contributor

Hello Everyone,

 

I'm aware of the new Conditional Formatting Rule builder that just dropped to my tenant last week. However, I'm wanting to implement formatting that will mark files that have a modified date 2 years or more before today. 

 

The goal is to mark files that haven't been modified in the past 2 years (relative to today). Has anyone done this? If so can you please share the JSON? Please and Thank You!

11 Replies
best response confirmed by Michael Malloy (Iron Contributor)
Solution

Hi Michael, (@Michael Malloy), yes we do this at our company. The JSON is below and subtracts 2 years (in milliseconds) from today's date. If the Modified date is older than that then the date is red, otherwise it's green:

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "color": "=if([$Modified] <= @now-63113904000, '#c64444', '#468757')"
  }
}

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

 

@RobElliott 

 

Also...one more question...It appears that JSON applied to a library affects ALL views. Is there a way to restrict this to just the active view to which I'm adding the JSON?

@Michael Malloy  sorry for the delay in replying Michael. From the menu where your views are listed, select Format current view. In the example below I've got the All items view in standard list format, but in the view that I've named Tile I've formatted the items differently with JSON:

 

viewFormatting.gif

 

Rob
Los Gallardos

Microsoft Power Automate Community Super User

@RobElliott 

 

Thanks so much for the video. I swear I only applied the json (Red files older than 2 years) to only 1 view. However, for some reason, that formatting seemed to bleed over into all views. I will do some more testing. Thanks again.

@Michael Malloy if you apply the formatting to the column (from the column header) then it will be there in all views. If you apply formatting to the view then it won't.

 

Rob

Los Gallardos

Microsoft Power Automate Community Super User

@RobElliott  - could you show me this, but Bold if it meets the criteria, otherwise regular text.

Thx!

@RobElliott Would I be able to flag items that have a date that is older than a week before today's date? For example, I have an item with today's date (5/17/2023). In one week from today, I want this item to be flagged, and I want this to happen for all items.

At them moment, the formatting that I have is "if the variable is before a fixed date," but I don't want the date to be fixed - I want it to always be a week before today's date, if that makes sense.

jeremyELAM_0-1684351263965.png

 

@jeremyELAM you can't do that with the built-in column formatting, but if you format the column in advanced mode you can paste in the following JSON which, in this example, will make the background color green until it's older than 7 days before today when it changes to maroon.

 

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(@currentField < @now-604800000, '#830909', '#409f93')",
    "color": "white",
    "padding-left": "10px"
  }
}

 

 

This is the result.

 

0-SP.png

 

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

@jeremyELAM as an alternative to using milliseconds you can use the recently-released addDays function. It gives the same result but is perhaps slightly easier to read.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "background-color": "=if(@currentField < addDays(@now,-7), '#830909', '#409f93')",
    "color": "white",
    "padding-left": "10px"
  }
}

 

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

1 best response

Accepted Solutions
best response confirmed by Michael Malloy (Iron Contributor)
Solution

Hi Michael, (@Michael Malloy), yes we do this at our company. The JSON is below and subtracts 2 years (in milliseconds) from today's date. If the Modified date is older than that then the date is red, otherwise it's green:

 

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "color": "=if([$Modified] <= @now-63113904000, '#c64444', '#468757')"
  }
}

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

 

View solution in original post