Apr 24 2020 11:19 AM
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!
Apr 25 2020 03:18 AM
SolutionHi 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
Apr 27 2020 09:18 AM
Apr 28 2020 07:49 AM
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?
Apr 30 2020 08:35 AM - edited Apr 30 2020 09:01 AM
@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:
Rob
Los Gallardos
Microsoft Power Automate Community Super User
Apr 30 2020 09:58 AM
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.
Apr 30 2020 10:01 AM
@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
Apr 30 2020 10:03 AM
Ah I see! I will try that!
Nov 12 2021 07:36 AM
@RobElliott - could you show me this, but Bold if it meets the criteria, otherwise regular text.
Thx!
May 17 2023 12:21 PM
@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.
May 18 2023 01:54 AM - edited May 18 2023 01:55 AM
@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.
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
May 19 2023 02:29 AM
@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)
Apr 25 2020 03:18 AM
SolutionHi 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