Forum Discussion
Conditional Formatting Files Older Than Certain number of days or years
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!
Hi Michael, (pnthrzrule), 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
11 Replies
Hi Michael, (pnthrzrule), 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 UserRobElliott 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 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)
RobElliott - could you show me this, but Bold if it meets the criteria, otherwise regular text.
Thx!
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?
pnthrzrule 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:
Rob
Los GallardosMicrosoft Power Automate Community Super User
